How often should I schedule a reboot of SQL Server 2005?

How many times per year should we schedule time to reboot a SQL Server 2005 Cluster?We want to do this to defrag the memory.What I am looking for is a document from Microsoft or a consulting firm that has a good reputation that I can show my manager.

Hi

I don't think you need to scdedule a restart for SQL 2005 cluster. SQL Server 2005 is very efficient at managing the memory dynamically.

Jag

|||

There is no need to restart SQL Server on a regular basis. As SQL doesn't need memory it will release it. SQL Server 2005 is very good at managing it's memory.

If you want to clear it's cache you can use DBCC FREESYSTEMCACHE to free all the cache pools.

|||Unless you do not want to loose cached data and query plans, you should not do this :-)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||You don't need to do it -- this product is designed to stay up ad infinitum (well apart from if you patch it or the OS requiring a reboot).|||

If I am running SQL Server 2005 in a clustered environment, what is the safest way to restart the SQL server agent? It is currently running but I need to restart it for maintenance purposes.

Is the safeway way to restart is to login to the SQL Server Management Studio as the system administrator, select the SQL Server Agent object, right-mouse click and select "Restart?"

|||That should work fine.

How often should I schedule a reboot of SQL Server 2005?

How many times per year should we schedule time to reboot a SQL Server 2005 Cluster?We want to do this to defrag the memory.What I am looking for is a document from Microsoft or a consulting firm that has a good reputation that I can show my manager.

Hi

I don't think you need to scdedule a restart for SQL 2005 cluster. SQL Server 2005 is very efficient at managing the memory dynamically.

Jag

|||

There is no need to restart SQL Server on a regular basis. As SQL doesn't need memory it will release it. SQL Server 2005 is very good at managing it's memory.

If you want to clear it's cache you can use DBCC FREESYSTEMCACHE to free all the cache pools.

|||Unless you do not want to loose cached data and query plans, you should not do this :-)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||You don't need to do it -- this product is designed to stay up ad infinitum (well apart from if you patch it or the OS requiring a reboot).|||

If I am running SQL Server 2005 in a clustered environment, what is the safest way to restart the SQL server agent? It is currently running but I need to restart it for maintenance purposes.

Is the safeway way to restart is to login to the SQL Server Management Studio as the system administrator, select the SQL Server Agent object, right-mouse click and select "Restart?"

|||That should work fine.

How often should I schedule a reboot of SQL Server 2005?

How many times per year should we schedule time to reboot a SQL Server 2005 Cluster?We want to do this to defrag the memory.What I am looking for is a document from Microsoft or a consulting firm that has a good reputation that I can show my manager.

Hi

I don't think you need to scdedule a restart for SQL 2005 cluster. SQL Server 2005 is very efficient at managing the memory dynamically.

Jag

|||

There is no need to restart SQL Server on a regular basis. As SQL doesn't need memory it will release it. SQL Server 2005 is very good at managing it's memory.

If you want to clear it's cache you can use DBCC FREESYSTEMCACHE to free all the cache pools.

|||Unless you do not want to loose cached data and query plans, you should not do this :-)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||You don't need to do it -- this product is designed to stay up ad infinitum (well apart from if you patch it or the OS requiring a reboot).|||

If I am running SQL Server 2005 in a clustered environment, what is the safest way to restart the SQL server agent? It is currently running but I need to restart it for maintenance purposes.

Is the safeway way to restart is to login to the SQL Server Management Studio as the system administrator, select the SQL Server Agent object, right-mouse click and select "Restart?"

|||That should work fine.

how much size SQL SERVER each connection memory ?

recently ,I got about15M size to each db connection memory with
windows performance monitor,
I want to know is this normal?
I heard that each normal connection is about 2M size,is this right?
best regards!If you check this on BOL, it will give you a general idea of how much
memory Sql server uses for a object.
"Memory Used by SQL Server Objects Specifications"
Mel|||thanks ,but what 's BOL,can you tell me?|||SQL Server Books Online, the documentation that comes with SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<lovexueer@.gmail.com> wrote in message news:1144320352.630105.194670@.z34g2000cwc.googlegroup
s.com...
> thanks ,but what 's BOL,can you tell me?
>

how much size SQL SERVER each connection memory ?

recently ,I got about15M size to each db connection memory with
windows performance monitor,
I want to know is this normal?
I heard that each normal connection is about 2M size,is this right?
best regards!
If you check this on BOL, it will give you a general idea of how much
memory Sql server uses for a object.
"Memory Used by SQL Server Objects Specifications"
Mel
|||thanks ,but what 's BOL,can you tell me?
|||SQL Server Books Online, the documentation that comes with SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<lovexueer@.gmail.com> wrote in message news:1144320352.630105.194670@.z34g2000cwc.googlegr oups.com...
> thanks ,but what 's BOL,can you tell me?
>
sql

how much size SQL SERVER each connection memory ?

recently ,I got about15M size to each db connection memory with
windows performance monitor,
I want to know is this normal?
I heard that each normal connection is about 2M size,is this right?
best regards!If you check this on BOL, it will give you a general idea of how much
memory Sql server uses for a object.
"Memory Used by SQL Server Objects Specifications"
Mel|||thanks ,but what 's BOL,can you tell me?|||SQL Server Books Online, the documentation that comes with SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<lovexueer@.gmail.com> wrote in message news:1144320352.630105.194670@.z34g2000cwc.googlegroups.com...
> thanks ,but what 's BOL,can you tell me?
>

How much of memory is too much for SQL on 64 bit

We want to move to a 64 bit platform .
First of all I would like to know if there are any cons w/ 64 bit. I would
consider moving to SQL 2005
And second, how much of RAM do you use on such systems ? I dont want to buy
a lot. Would 32 GB or 64 GB be a good start ?Hi
As far as performance you can only really tell if you have enough memory if
you benchmark the application. As this is an existing platform you may want
to take the current amount as a guide if you are not having problems with the
current level of memory, but make sure that you allow for expansion and don't
use up all the available slots (you may want to use a few slots as you can!)
Make sure that you buy as system that satisfies the 64 bit requirements in
http://www.microsoft.com/sql/editions/enterprise/sysreqs.mspx
You should analyse you current design and make sure that you are not limited
by other factors such as hard disc capacity or performance. Make sure that
you buy a sufficiently flexible system so that you can place your database
files in such a way that it will maximise I/O as well have sufficient
capacity for future expansion. Also make sure that you have sufficient
spindles to separate tempdb, the system databases, OS and pagefile etc.
John
"Hassan" wrote:
> We want to move to a 64 bit platform .
> First of all I would like to know if there are any cons w/ 64 bit. I would
> consider moving to SQL 2005
> And second, how much of RAM do you use on such systems ? I dont want to buy
> a lot. Would 32 GB or 64 GB be a good start ?
>
>
>

How much of memory is too much for SQL on 64 bit

We want to move to a 64 bit platform .
First of all I would like to know if there are any cons w/ 64 bit. I would
consider moving to SQL 2005
And second, how much of RAM do you use on such systems ? I dont want to buy
a lot. Would 32 GB or 64 GB be a good start ?Hi
As far as performance you can only really tell if you have enough memory if
you benchmark the application. As this is an existing platform you may want
to take the current amount as a guide if you are not having problems with th
e
current level of memory, but make sure that you allow for expansion and don'
t
use up all the available slots (you may want to use a few slots as you can!)
Make sure that you buy as system that satisfies the 64 bit requirements in
http://www.microsoft.com/sql/editio...se/sysreqs.mspx
You should analyse you current design and make sure that you are not limited
by other factors such as hard disc capacity or performance. Make sure that
you buy a sufficiently flexible system so that you can place your database
files in such a way that it will maximise I/O as well have sufficient
capacity for future expansion. Also make sure that you have sufficient
spindles to separate tempdb, the system databases, OS and pagefile etc.
John
"Hassan" wrote:

> We want to move to a 64 bit platform .
> First of all I would like to know if there are any cons w/ 64 bit. I would
> consider moving to SQL 2005
> And second, how much of RAM do you use on such systems ? I dont want to bu
y
> a lot. Would 32 GB or 64 GB be a good start ?
>
>
>

How much memory should I reserve for SQL Server 6.5 when upgrading to MS SQL 2K?

Hi, all.
I wonder how much memory I should reserve for MS SQL Server 6.5 when I am
upgrading it to MS SQL Server 2000?
As you know, MS SQL Server 2000 uses dynamic configuration as default. So I
don't have to reserve any space for it.
But MS SQL Server 6.5 uses static configuration. I wonder how much memery I
should reserve for it.
I suppose that I reserver 50% of memory for MS SQL Server 6.5 and remainder
for MS SQL Server 2000.
Am I right?
Any advice would be appreciated.
Thank you.It should be ok, although this is not the most important consideration.
Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_upgrade_4khg.asp.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Kim Keuk Tae" <zyuuzika@.korea.com> wrote in message
news:e03tdMsaDHA.2328@.TK2MSFTNGP09.phx.gbl...
> Hi, all.
> I wonder how much memory I should reserve for MS SQL Server 6.5 when I am
> upgrading it to MS SQL Server 2000?
> As you know, MS SQL Server 2000 uses dynamic configuration as default. So
I
> don't have to reserve any space for it.
> But MS SQL Server 6.5 uses static configuration. I wonder how much memery
I
> should reserve for it.
> I suppose that I reserver 50% of memory for MS SQL Server 6.5 and
remainder
> for MS SQL Server 2000.
> Am I right?
> Any advice would be appreciated.
> Thank you.
>

How much memory should I assign?

Hi.
I wonder how much memory I should assign for MS SQL Server MEMORY
configuration variable.
If I have 100GB of data, what percentage of the data size is fit for memory?
20%? Or, 2~3% is adequate?
Any advice or hints would be appreciated.I would look at what percentage of system memory you want to allocate to =SQl server rather than comparing with size of DB. At 7 or 2k SQl server =will try and take almost all the available memory, and release back if =another process requires it. If you are running other apps on the same =system it can sometimes be beneficial to limit the upper end of this via =sp_configure.
Mike John,
"Kim Keuk Tae" <seiyanotenshi@.hotmail.com> wrote in message =news:eJYaPs7uDHA.640@.TK2MSFTNGP11.phx.gbl...
> Hi.
> > I wonder how much memory I should assign for MS SQL Server MEMORY
> configuration variable.
> > If I have 100GB of data, what percentage of the data size is fit for =memory?
> > 20%? Or, 2~3% is adequate?
> > Any advice or hints would be appreciated.
> > > >|||Thanks Mike.
I think I missed to explain my intend.
If I have to construct a sysem which has 100GB of data, and I don't have any
H/W, I suggest my customes some H/W specification, CPU, MEMORY, and DISKS...
This is a sizing, or capacity planning. If I calculated our application
would have 100GB of data, What memory should I
suggest to my customers? 20%, or 20GB of memory(that's too much, of
course)...
I wonder if there is a rule to choose which size of memory in proportion to
the data size.
I am very poor at sizing.
"Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
news:OLKFje9uDHA.2456@.TK2MSFTNGP12.phx.gbl...
I would look at what percentage of system memory you want to allocate to SQl
server rather than comparing with size of DB. At 7 or 2k SQl server will try
and take almost all the available memory, and release back if another
process requires it. If you are running other apps on the same system it can
sometimes be beneficial to limit the upper end of this via sp_configure.
Mike John,
"Kim Keuk Tae" <seiyanotenshi@.hotmail.com> wrote in message
news:eJYaPs7uDHA.640@.TK2MSFTNGP11.phx.gbl...
> Hi.
> I wonder how much memory I should assign for MS SQL Server MEMORY
> configuration variable.
> If I have 100GB of data, what percentage of the data size is fit for
memory?
> 20%? Or, 2~3% is adequate?
> Any advice or hints would be appreciated.
>
>|||Kim,
You cannot really size memory based purely on data size - it may be huge =but hardly ever be used, an hence a very small machine would be fine. =You really need a lot more informatioj - number of users, number of =transactions per day etc. Even with that you will still be making some =educated guesses. If I remember right there was a document on =Microsoft's site regarding sizing - have you looked there?
Mike John
"Kim Keuk Tae" <seiyanotenshi@.hotmail.com> wrote in message =news:O0jB1$$uDHA.3436@.tk2msftngp13.phx.gbl...
> Thanks Mike.
> > I think I missed to explain my intend.
> > If I have to construct a sysem which has 100GB of data, and I don't =have any
> H/W, I suggest my customes some H/W specification, CPU, MEMORY, and =DISKS...
> > This is a sizing, or capacity planning. If I calculated our =application
> would have 100GB of data, What memory should I
> suggest to my customers? 20%, or 20GB of memory(that's too much, of
> course)...
> > I wonder if there is a rule to choose which size of memory in =proportion to
> the data size.
> > I am very poor at sizing.
> > > "Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
> news:OLKFje9uDHA.2456@.TK2MSFTNGP12.phx.gbl...
> I would look at what percentage of system memory you want to allocate =to SQl
> server rather than comparing with size of DB. At 7 or 2k SQl server =will try
> and take almost all the available memory, and release back if another
> process requires it. If you are running other apps on the same system =it can
> sometimes be beneficial to limit the upper end of this via =sp_configure.
> > Mike John,
> > > "Kim Keuk Tae" <seiyanotenshi@.hotmail.com> wrote in message
> news:eJYaPs7uDHA.640@.TK2MSFTNGP11.phx.gbl...
> > Hi.
> >
> > I wonder how much memory I should assign for MS SQL Server MEMORY
> > configuration variable.
> >
> > If I have 100GB of data, what percentage of the data size is fit for
> memory?
> >
> > 20%? Or, 2~3% is adequate?
> >
> > Any advice or hints would be appreciated.
> >
> >
> >
> >
> >sql

How much memory should be used ?

I am new to SQL Server 2005. When using SQL Server 2000, we usually give
3GB in which 2GB will be used for SQL Server 2000.
How much memory is recommended for running SQL Server 2005 on a Windows 2003
Server ? From MS web site, I learn that SQL Server 2005 Server can use as
much as memory that the OS supports. Can I still use 2GB memory for it or
it requires more memory ?
ThanksHi Peter
Memory usage will depend on many things, in general the more the better upto
the point where you are memory rich. The only real way to know is to start of
at a set amount and then if necessary increase it if monitoring shows that
your system can do with more. Something that will limit the amount of memory
you can dedicate to SQL Server is if your system is used for other things,
ideally a SQL server would be the only application that the server is used
for in which case you can usually let SQL Server look after it's self i.e.
don't specify a maximum, but if it is shared with other applications you will
need to be more careful.
John
"Peter" wrote:
> I am new to SQL Server 2005. When using SQL Server 2000, we usually give
> 3GB in which 2GB will be used for SQL Server 2000.
> How much memory is recommended for running SQL Server 2005 on a Windows 2003
> Server ? From MS web site, I learn that SQL Server 2005 Server can use as
> much as memory that the OS supports. Can I still use 2GB memory for it or
> it requires more memory ?
> Thanks
>
>|||Dear John,
Thank you for your advice. That Windows Server will run SQL Server 2005
only and the loading will not be heavy. I will follow your advice and don't
set any maximum for it.
Does 3GB is sufficient for my case ?
Thanks
Peter
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:14E800BF-B9B0-4D2E-B95A-FC22F71C072C@.microsoft.com...
> Hi Peter
> Memory usage will depend on many things, in general the more the better
> upto
> the point where you are memory rich. The only real way to know is to start
> of
> at a set amount and then if necessary increase it if monitoring shows that
> your system can do with more. Something that will limit the amount of
> memory
> you can dedicate to SQL Server is if your system is used for other things,
> ideally a SQL server would be the only application that the server is used
> for in which case you can usually let SQL Server look after it's self i.e.
> don't specify a maximum, but if it is shared with other applications you
> will
> need to be more careful.
> John
> "Peter" wrote:
>> I am new to SQL Server 2005. When using SQL Server 2000, we usually give
>> 3GB in which 2GB will be used for SQL Server 2000.
>> How much memory is recommended for running SQL Server 2005 on a Windows
>> 2003
>> Server ? From MS web site, I learn that SQL Server 2005 Server can use
>> as
>> much as memory that the OS supports. Can I still use 2GB memory for it
>> or
>> it requires more memory ?
>> Thanks
>>|||Hi Peter
If there is not heavy use it may be enough but you will have to try it and
see. If you are not using all the memory slots then you can expand it without
removing any of the existing memory, it would also be worthwhile making sure
what the hardware manufacture recomends regarding memory, you may have to
pair like for like. Also check cost to see what is the best use of your funds.
John
"Peter" wrote:
> Dear John,
> Thank you for your advice. That Windows Server will run SQL Server 2005
> only and the loading will not be heavy. I will follow your advice and don't
> set any maximum for it.
> Does 3GB is sufficient for my case ?
> Thanks
> Peter
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:14E800BF-B9B0-4D2E-B95A-FC22F71C072C@.microsoft.com...
> > Hi Peter
> >
> > Memory usage will depend on many things, in general the more the better
> > upto
> > the point where you are memory rich. The only real way to know is to start
> > of
> > at a set amount and then if necessary increase it if monitoring shows that
> > your system can do with more. Something that will limit the amount of
> > memory
> > you can dedicate to SQL Server is if your system is used for other things,
> > ideally a SQL server would be the only application that the server is used
> > for in which case you can usually let SQL Server look after it's self i.e.
> > don't specify a maximum, but if it is shared with other applications you
> > will
> > need to be more careful.
> >
> > John
> >
> > "Peter" wrote:
> >
> >> I am new to SQL Server 2005. When using SQL Server 2000, we usually give
> >> 3GB in which 2GB will be used for SQL Server 2000.
> >>
> >> How much memory is recommended for running SQL Server 2005 on a Windows
> >> 2003
> >> Server ? From MS web site, I learn that SQL Server 2005 Server can use
> >> as
> >> much as memory that the OS supports. Can I still use 2GB memory for it
> >> or
> >> it requires more memory ?
> >>
> >> Thanks
> >>
> >>
> >>
>
>

How much memory should be used ?

Hi Peter
Memory usage will depend on many things, in general the more the better upto
the point where you are memory rich. The only real way to know is to start of
at a set amount and then if necessary increase it if monitoring shows that
your system can do with more. Something that will limit the amount of memory
you can dedicate to SQL Server is if your system is used for other things,
ideally a SQL server would be the only application that the server is used
for in which case you can usually let SQL Server look after it's self i.e.
don't specify a maximum, but if it is shared with other applications you will
need to be more careful.
John
"Peter" wrote:

> I am new to SQL Server 2005. When using SQL Server 2000, we usually give
> 3GB in which 2GB will be used for SQL Server 2000.
> How much memory is recommended for running SQL Server 2005 on a Windows 2003
> Server ? From MS web site, I learn that SQL Server 2005 Server can use as
> much as memory that the OS supports. Can I still use 2GB memory for it or
> it requires more memory ?
> Thanks
>
>
Hi Peter
If there is not heavy use it may be enough but you will have to try it and
see. If you are not using all the memory slots then you can expand it without
removing any of the existing memory, it would also be worthwhile making sure
what the hardware manufacture recomends regarding memory, you may have to
pair like for like. Also check cost to see what is the best use of your funds.
John
"Peter" wrote:

> Dear John,
> Thank you for your advice. That Windows Server will run SQL Server 2005
> only and the loading will not be heavy. I will follow your advice and don't
> set any maximum for it.
> Does 3GB is sufficient for my case ?
> Thanks
> Peter
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:14E800BF-B9B0-4D2E-B95A-FC22F71C072C@.microsoft.com...
>
>

How much memory should be used ?

I am new to SQL Server 2005. When using SQL Server 2000, we usually give
3GB in which 2GB will be used for SQL Server 2000.
How much memory is recommended for running SQL Server 2005 on a Windows 2003
Server ? From MS web site, I learn that SQL Server 2005 Server can use as
much as memory that the OS supports. Can I still use 2GB memory for it or
it requires more memory ?
ThanksHi Peter
Memory usage will depend on many things, in general the more the better upto
the point where you are memory rich. The only real way to know is to start o
f
at a set amount and then if necessary increase it if monitoring shows that
your system can do with more. Something that will limit the amount of memory
you can dedicate to SQL Server is if your system is used for other things,
ideally a SQL server would be the only application that the server is used
for in which case you can usually let SQL Server look after it's self i.e.
don't specify a maximum, but if it is shared with other applications you wil
l
need to be more careful.
John
"Peter" wrote:

> I am new to SQL Server 2005. When using SQL Server 2000, we usually give
> 3GB in which 2GB will be used for SQL Server 2000.
> How much memory is recommended for running SQL Server 2005 on a Windows 20
03
> Server ? From MS web site, I learn that SQL Server 2005 Server can use as
> much as memory that the OS supports. Can I still use 2GB memory for it or
> it requires more memory ?
> Thanks
>
>|||Dear John,
Thank you for your advice. That Windows Server will run SQL Server 2005
only and the loading will not be heavy. I will follow your advice and don't
set any maximum for it.
Does 3GB is sufficient for my case ?
Thanks
Peter
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:14E800BF-B9B0-4D2E-B95A-FC22F71C072C@.microsoft.com...[vbcol=seagreen]
> Hi Peter
> Memory usage will depend on many things, in general the more the better
> upto
> the point where you are memory rich. The only real way to know is to start
> of
> at a set amount and then if necessary increase it if monitoring shows that
> your system can do with more. Something that will limit the amount of
> memory
> you can dedicate to SQL Server is if your system is used for other things,
> ideally a SQL server would be the only application that the server is used
> for in which case you can usually let SQL Server look after it's self i.e.
> don't specify a maximum, but if it is shared with other applications you
> will
> need to be more careful.
> John
> "Peter" wrote:
>|||Hi Peter
If there is not heavy use it may be enough but you will have to try it and
see. If you are not using all the memory slots then you can expand it withou
t
removing any of the existing memory, it would also be worthwhile making sure
what the hardware manufacture recomends regarding memory, you may have to
pair like for like. Also check cost to see what is the best use of your fund
s.
John
"Peter" wrote:

> Dear John,
> Thank you for your advice. That Windows Server will run SQL Server 2005
> only and the loading will not be heavy. I will follow your advice and don
't
> set any maximum for it.
> Does 3GB is sufficient for my case ?
> Thanks
> Peter
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:14E800BF-B9B0-4D2E-B95A-FC22F71C072C@.microsoft.com...
>
>

How much memory is safe to reserve for SQL?

I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
Gb of RAM. One of the databases is taking 1GB of space and it has 8
connections running at all times. I want to limit the amount of memory
that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
server. How much memory is advise to let SQL use in this case? 50%, 60%
of the total memory of the server?Hi,
If the server is dedicated for SQL Server then I will go with dynamic memory
allocation. If you have any other application running in the same server
apart from SQL Server then probably limit the SQL Server memort by 1 GB or
so...
Thanks
Hari
SQL Server MVP
"WR" <wilsonrey@.gmail.com> wrote in message
news:1156132604.617900.25730@.h48g2000cwc.googlegroups.com...
>I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
> Gb of RAM. One of the databases is taking 1GB of space and it has 8
> connections running at all times. I want to limit the amount of memory
> that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
> server. How much memory is advise to let SQL use in this case? 50%, 60%
> of the total memory of the server?
>|||Thank you for your advise. The server actually runs a point of sale
system and some customize software too. My concern is that the memory
usage is always high, generally running at 70 of the memory. I read
that SQL is not very good releasing memory after a service runs, and
that it will only release it when a programa needs it. I don't want to
wait until Windows sends a low memory warning for SQL release the
memory.
If you recommend 1 GB, then my next question is: Can the SQL server
service be reprogram to restart one a day in the middle of the day? so,
it will release the memory that is hugging?
WR wrote:
> I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
> Gb of RAM. One of the databases is taking 1GB of space and it has 8
> connections running at all times. I want to limit the amount of memory
> that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
> server. How much memory is advise to let SQL use in this case? 50%, 60%
> of the total memory of the server?|||Hi,
Why do you want an outage in the middle of the day. Let SQL Server takes 1
GB RAM and use it for its own purpose.
The rest of the memory will be used for operating system and other
applications.You do not require a service restart.
Incase if you are experiancing slow performance; try monitoring the using
the memory related counters
mentioned in below URL.
[url]http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp[
/url]
Thanks
Hari
SQL Server MVP
"WR" <wilsonrey@.gmail.com> wrote in message
news:1156167157.496825.260490@.h48g2000cwc.googlegroups.com...
> Thank you for your advise. The server actually runs a point of sale
> system and some customize software too. My concern is that the memory
> usage is always high, generally running at 70 of the memory. I read
> that SQL is not very good releasing memory after a service runs, and
> that it will only release it when a programa needs it. I don't want to
> wait until Windows sends a low memory warning for SQL release the
> memory.
> If you recommend 1 GB, then my next question is: Can the SQL server
> service be reprogram to restart one a day in the middle of the day? so,
> it will release the memory that is hugging?
>
> WR wrote:
>|||Hello,
Thank you again for your information. I gets I was having too much
cafeine that day before I sent the email. What I meant to say what to
restart SQL once a day in the middle of the night.
Any way, I will follow your advise to your advise and monitor the
memory, still I would like to be able to not to have SQL taking all
that memory at all times. (I know, I am hard head) ;-)
Hari Prasad wrote:[vbcol=seagreen]
> Hi,
> Why do you want an outage in the middle of the day. Let SQL Server takes 1
> GB RAM and use it for its own purpose.
> The rest of the memory will be used for operating system and other
> applications.You do not require a service restart.
> Incase if you are experiancing slow performance; try monitoring the using
> the memory related counters
> mentioned in below URL.
> http://www.sql-server-performance.c...ver.as
p
>
> Thanks
> Hari
> SQL Server MVP
>
>
> "WR" <wilsonrey@.gmail.com> wrote in message
> news:1156167157.496825.260490@.h48g2000cwc.googlegroups.com...sql

How much memory is safe to reserve for SQL?

I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
Gb of RAM. One of the databases is taking 1GB of space and it has 8
connections running at all times. I want to limit the amount of memory
that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
server. How much memory is advise to let SQL use in this case? 50%, 60%
of the total memory of the server?Hi,
If the server is dedicated for SQL Server then I will go with dynamic memory
allocation. If you have any other application running in the same server
apart from SQL Server then probably limit the SQL Server memort by 1 GB or
so...
Thanks
Hari
SQL Server MVP
"WR" <wilsonrey@.gmail.com> wrote in message
news:1156132604.617900.25730@.h48g2000cwc.googlegroups.com...
>I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
> Gb of RAM. One of the databases is taking 1GB of space and it has 8
> connections running at all times. I want to limit the amount of memory
> that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
> server. How much memory is advise to let SQL use in this case? 50%, 60%
> of the total memory of the server?
>|||Thank you for your advise. The server actually runs a point of sale
system and some customize software too. My concern is that the memory
usage is always high, generally running at 70 of the memory. I read
that SQL is not very good releasing memory after a service runs, and
that it will only release it when a programa needs it. I don't want to
wait until Windows sends a low memory warning for SQL release the
memory.
If you recommend 1 GB, then my next question is: Can the SQL server
service be reprogram to restart one a day in the middle of the day? so,
it will release the memory that is hugging?
WR wrote:
> I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
> Gb of RAM. One of the databases is taking 1GB of space and it has 8
> connections running at all times. I want to limit the amount of memory
> that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
> server. How much memory is advise to let SQL use in this case? 50%, 60%
> of the total memory of the server?|||Hi,
Why do you want an outage in the middle of the day. Let SQL Server takes 1
GB RAM and use it for its own purpose.
The rest of the memory will be used for operating system and other
applications.You do not require a service restart.
Incase if you are experiancing slow performance; try monitoring the using
the memory related counters
mentioned in below URL.
http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp
Thanks
Hari
SQL Server MVP
"WR" <wilsonrey@.gmail.com> wrote in message
news:1156167157.496825.260490@.h48g2000cwc.googlegroups.com...
> Thank you for your advise. The server actually runs a point of sale
> system and some customize software too. My concern is that the memory
> usage is always high, generally running at 70 of the memory. I read
> that SQL is not very good releasing memory after a service runs, and
> that it will only release it when a programa needs it. I don't want to
> wait until Windows sends a low memory warning for SQL release the
> memory.
> If you recommend 1 GB, then my next question is: Can the SQL server
> service be reprogram to restart one a day in the middle of the day? so,
> it will release the memory that is hugging?
>
> WR wrote:
>> I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
>> Gb of RAM. One of the databases is taking 1GB of space and it has 8
>> connections running at all times. I want to limit the amount of memory
>> that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
>> server. How much memory is advise to let SQL use in this case? 50%, 60%
>> of the total memory of the server?
>|||Hello,
Thank you again for your information. I gets I was having too much
cafeine that day before I sent the email. What I meant to say what to
restart SQL once a day in the middle of the night.
Any way, I will follow your advise to your advise and monitor the
memory, still I would like to be able to not to have SQL taking all
that memory at all times. (I know, I am hard head) ;-)
Hari Prasad wrote:
> Hi,
> Why do you want an outage in the middle of the day. Let SQL Server takes 1
> GB RAM and use it for its own purpose.
> The rest of the memory will be used for operating system and other
> applications.You do not require a service restart.
> Incase if you are experiancing slow performance; try monitoring the using
> the memory related counters
> mentioned in below URL.
> http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp
>
> Thanks
> Hari
> SQL Server MVP
>
>
> "WR" <wilsonrey@.gmail.com> wrote in message
> news:1156167157.496825.260490@.h48g2000cwc.googlegroups.com...
> > Thank you for your advise. The server actually runs a point of sale
> > system and some customize software too. My concern is that the memory
> > usage is always high, generally running at 70 of the memory. I read
> > that SQL is not very good releasing memory after a service runs, and
> > that it will only release it when a programa needs it. I don't want to
> > wait until Windows sends a low memory warning for SQL release the
> > memory.
> > If you recommend 1 GB, then my next question is: Can the SQL server
> > service be reprogram to restart one a day in the middle of the day? so,
> > it will release the memory that is hugging?
> >
> >
> > WR wrote:
> >> I am running SQL 2000 in a Windows Server 2000. The server only has 1.5
> >> Gb of RAM. One of the databases is taking 1GB of space and it has 8
> >> connections running at all times. I want to limit the amount of memory
> >> that SQL uses, beucase SQLSVR.EXE is taking 1+ Gb of the memory of the
> >> server. How much memory is advise to let SQL use in this case? 50%, 60%
> >> of the total memory of the server?
> >

How much memory is allotted to a single user connection?

Hi, everybody!
I wonder how much memory space is allotted to a single user connection.
I know MS SQL Server 6.5 allots 37KB of memory space to a single connection.
And I heard that MS SQL 2000 allots 500KB of memory space to a single
connection.
Is it true?
Thanks in advance.Where did u get this stat from ... Will be interested to see the same ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
"Kim Keuk Tae" <zyuuzika@.korea.com> wrote in message
news:OevJwlqlDHA.2432@.TK2MSFTNGP10.phx.gbl...
> Hi, everybody!
> I wonder how much memory space is allotted to a single user connection.
> I know MS SQL Server 6.5 allots 37KB of memory space to a single
connection.
> And I heard that MS SQL 2000 allots 500KB of memory space to a single
> connection.
> Is it true?
> Thanks in advance.
>
>|||When in doubt, always first check the BOL. Under the section "Memory used by
SQL Server 2000 Objects Specifications", the BOL indicates that:
The memory used by a use connection uses 12 KB + (3 * Network Packet Size),
where Network Packet Size is controlled by the 'network packet size'
configuration option. The default is 4KB.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Kim Keuk Tae" <zyuuzika@.korea.com> wrote in message
news:OevJwlqlDHA.2432@.TK2MSFTNGP10.phx.gbl...
> Hi, everybody!
> I wonder how much memory space is allotted to a single user connection.
> I know MS SQL Server 6.5 allots 37KB of memory space to a single
connection.
> And I heard that MS SQL 2000 allots 500KB of memory space to a single
> connection.
> Is it true?
> Thanks in advance.
>
>

how much memory does a transaction take?

hello,
i found out that it is useful while testing queries in my company to
start with begin tran, to do a restore if i might forget the where
clause
in an update on day after a long night.
but what does that cost?
the changes i make are only in my scope, to the server seems
to create temp tables of all tables i use.
if i do queryes for example 30 minutes without commit,
how does that affect the server? is that a very bad practice
or is that a normal way of working on a live server?
we do not really have testing systems, so that ist my
problem here..
nice to have some opinions stated here,
thank you and happy new year,
mike> if i do queryes for example 30 minutes without commit,
> how does that affect the server? is that a very bad practice
> or is that a normal way of working on a live server?
It's very bad if your users have to wait 30 min till transaction will be
commited.It should be avoided
http://www.sql-server-performance.com/blocking.asp
<peppi911@.hotmail.com> wrote in message
news:1136460584.490204.3350@.g44g2000cwa.googlegroups.com...
> hello,
>
> i found out that it is useful while testing queries in my company to
> start with begin tran, to do a restore if i might forget the where
> clause
> in an update on day after a long night.
>
> but what does that cost?
> the changes i make are only in my scope, to the server seems
> to create temp tables of all tables i use.
> if i do queryes for example 30 minutes without commit,
> how does that affect the server? is that a very bad practice
> or is that a normal way of working on a live server?
> we do not really have testing systems, so that ist my
> problem here..
> nice to have some opinions stated here,
>
> thank you and happy new year,
> mike
>|||Hi,
but if i start my work with
BEGIN TRAN
...do something 30 min.
ROLLBACK TRAN
i think all the changes i do are in my scope and the records are not
locked,
so i surmised that the server creates a copy for me or whatever, am i
wrong?
you mean
begin tran
select * from customers
and i go to lunch does mean nobody can use this table until i do
rollback?
Well i guess i need to do further investigations here,-)
Thanks, mike|||First you should read in Books Online about SET TRANSACTION ISOLATION LEVEL and also about different
lock types. Shared locks are by default not held until end of transaction. Most other lock types
are, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<peppi911@.hotmail.com> wrote in message
news:1136462605.759961.248740@.g43g2000cwa.googlegroups.com...
> Hi,
> but if i start my work with
> BEGIN TRAN
> ...do something 30 min.
> ROLLBACK TRAN
> i think all the changes i do are in my scope and the records are not
> locked,
> so i surmised that the server creates a copy for me or whatever, am i
> wrong?
> you mean
> begin tran
> select * from customers
> and i go to lunch does mean nobody can use this table until i do
> rollback?
> Well i guess i need to do further investigations here,-)
> Thanks, mike
>|||Read the books is the best advice.
Anyway, you can hold the shared lock got by a select until the end of a
transaction if you specify the hint (HOLDLOCK).
--
Bazili
"Tibor Karaszi" wrote:
> First you should read in Books Online about SET TRANSACTION ISOLATION LEVEL and also about different
> lock types. Shared locks are by default not held until end of transaction. Most other lock types
> are, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> <peppi911@.hotmail.com> wrote in message
> news:1136462605.759961.248740@.g43g2000cwa.googlegroups.com...
> > Hi,
> > but if i start my work with
> >
> > BEGIN TRAN
> > ...do something 30 min.
> > ROLLBACK TRAN
> >
> > i think all the changes i do are in my scope and the records are not
> > locked,
> > so i surmised that the server creates a copy for me or whatever, am i
> > wrong?
> > you mean
> >
> > begin tran
> > select * from customers
> > and i go to lunch does mean nobody can use this table until i do
> > rollback?
> >
> > Well i guess i need to do further investigations here,-)
> > Thanks, mike
> >
>

how much memory does a transaction take?

hello,
i found out that it is useful while testing queries in my company to
start with begin tran, to do a restore if i might forget the where
clause
in an update on day after a long night.
but what does that cost?
the changes i make are only in my scope, to the server seems
to create temp tables of all tables i use.
if i do queryes for example 30 minutes without commit,
how does that affect the server? is that a very bad practice
or is that a normal way of working on a live server?
we do not really have testing systems, so that ist my
problem here..
nice to have some opinions stated here,
thank you and happy new year,
mike
> if i do queryes for example 30 minutes without commit,
> how does that affect the server? is that a very bad practice
> or is that a normal way of working on a live server?
It's very bad if your users have to wait 30 min till transaction will be
commited.It should be avoided
http://www.sql-server-performance.com/blocking.asp
<peppi911@.hotmail.com> wrote in message
news:1136460584.490204.3350@.g44g2000cwa.googlegrou ps.com...
> hello,
>
> i found out that it is useful while testing queries in my company to
> start with begin tran, to do a restore if i might forget the where
> clause
> in an update on day after a long night.
>
> but what does that cost?
> the changes i make are only in my scope, to the server seems
> to create temp tables of all tables i use.
> if i do queryes for example 30 minutes without commit,
> how does that affect the server? is that a very bad practice
> or is that a normal way of working on a live server?
> we do not really have testing systems, so that ist my
> problem here..
> nice to have some opinions stated here,
>
> thank you and happy new year,
> mike
>
|||Hi,
but if i start my work with
BEGIN TRAN
...do something 30 min.
ROLLBACK TRAN
i think all the changes i do are in my scope and the records are not
locked,
so i surmised that the server creates a copy for me or whatever, am i
wrong?
you mean
begin tran
select * from customers
and i go to lunch does mean nobody can use this table until i do
rollback?
Well i guess i need to do further investigations here,-)
Thanks, mike
|||First you should read in Books Online about SET TRANSACTION ISOLATION LEVEL and also about different
lock types. Shared locks are by default not held until end of transaction. Most other lock types
are, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<peppi911@.hotmail.com> wrote in message
news:1136462605.759961.248740@.g43g2000cwa.googlegr oups.com...
> Hi,
> but if i start my work with
> BEGIN TRAN
> ...do something 30 min.
> ROLLBACK TRAN
> i think all the changes i do are in my scope and the records are not
> locked,
> so i surmised that the server creates a copy for me or whatever, am i
> wrong?
> you mean
> begin tran
> select * from customers
> and i go to lunch does mean nobody can use this table until i do
> rollback?
> Well i guess i need to do further investigations here,-)
> Thanks, mike
>
|||Read the books is the best advice.
Anyway, you can hold the shared lock got by a select until the end of a
transaction if you specify the hint (HOLDLOCK).
Bazili
"Tibor Karaszi" wrote:

> First you should read in Books Online about SET TRANSACTION ISOLATION LEVEL and also about different
> lock types. Shared locks are by default not held until end of transaction. Most other lock types
> are, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> <peppi911@.hotmail.com> wrote in message
> news:1136462605.759961.248740@.g43g2000cwa.googlegr oups.com...
>

how much memory does a transaction take?

hello,
i found out that it is useful while testing queries in my company to
start with begin tran, to do a restore if i might forget the where
clause
in an update on day after a long night.
but what does that cost?
the changes i make are only in my scope, to the server seems
to create temp tables of all tables i use.
if i do queryes for example 30 minutes without commit,
how does that affect the server? is that a very bad practice
or is that a normal way of working on a live server?
we do not really have testing systems, so that ist my
problem here..
nice to have some opinions stated here,
thank you and happy new year,
mike> if i do queryes for example 30 minutes without commit,
> how does that affect the server? is that a very bad practice
> or is that a normal way of working on a live server?
It's very bad if your users have to wait 30 min till transaction will be
commited.It should be avoided
http://www.sql-server-performance.com/blocking.asp
<peppi911@.hotmail.com> wrote in message
news:1136460584.490204.3350@.g44g2000cwa.googlegroups.com...
> hello,
>
> i found out that it is useful while testing queries in my company to
> start with begin tran, to do a restore if i might forget the where
> clause
> in an update on day after a long night.
>
> but what does that cost?
> the changes i make are only in my scope, to the server seems
> to create temp tables of all tables i use.
> if i do queryes for example 30 minutes without commit,
> how does that affect the server? is that a very bad practice
> or is that a normal way of working on a live server?
> we do not really have testing systems, so that ist my
> problem here..
> nice to have some opinions stated here,
>
> thank you and happy new year,
> mike
>|||Hi,
but if i start my work with
BEGIN TRAN
..do something 30 min.
ROLLBACK TRAN
i think all the changes i do are in my scope and the records are not
locked,
so i surmised that the server creates a copy for me or whatever, am i
wrong?
you mean
begin tran
select * from customers
and i go to lunch does mean nobody can use this table until i do
rollback?
Well i guess i need to do further investigations here,-)
Thanks, mike|||First you should read in Books Online about SET TRANSACTION ISOLATION LEVEL
and also about different
lock types. Shared locks are by default not held until end of transaction. M
ost other lock types
are, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<peppi911@.hotmail.com> wrote in message
news:1136462605.759961.248740@.g43g2000cwa.googlegroups.com...
> Hi,
> but if i start my work with
> BEGIN TRAN
> ...do something 30 min.
> ROLLBACK TRAN
> i think all the changes i do are in my scope and the records are not
> locked,
> so i surmised that the server creates a copy for me or whatever, am i
> wrong?
> you mean
> begin tran
> select * from customers
> and i go to lunch does mean nobody can use this table until i do
> rollback?
> Well i guess i need to do further investigations here,-)
> Thanks, mike
>|||Read the books is the best advice.
Anyway, you can hold the shared lock got by a select until the end of a
transaction if you specify the hint (HOLDLOCK).
Bazili
"Tibor Karaszi" wrote:

> First you should read in Books Online about SET TRANSACTION ISOLATION LEVE
L and also about different
> lock types. Shared locks are by default not held until end of transaction.
Most other lock types
> are, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> <peppi911@.hotmail.com> wrote in message
> news:1136462605.759961.248740@.g43g2000cwa.googlegroups.com...
>sql

How much memory can SQL Server 2000 std. address

How much memory can SQL Server 2000 std. address?
SQL Server 2000 std. is running on Windows 2003 server std edition.
Total memory was 1.5 GB but extra memory was added later.
How can I check how much memory is the SQL server configured to
address? Is there a dbcc command.
Any help will be appreciated.
2 GB.
You can check the "max server memory (mb)" setting using sp_configure ...
But remember that just because it is configured to use 2 GB doesn't
necessarily mean that it will do so -- it might not need that much.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"sql" <sql@.discussions.microsoft.com> wrote in message
news:37D5B093-4776-4693-93BD-E17FA54B50F4@.microsoft.com...
> How much memory can SQL Server 2000 std. address?
> SQL Server 2000 std. is running on Windows 2003 server std edition.
> Total memory was 1.5 GB but extra memory was added later.
>
> How can I check how much memory is the SQL server configured to
> address? Is there a dbcc command.
>
> Any help will be appreciated.
>
|||Hello,
SQL Server standard can support at a maxuimum of 2 GB RAM. If the memory is
configured to access dynamically then based on the
requuirement SQL Server will use the memory. Use task manager to see how
much memory SQLServr.EXE is using...
Thanks
Hari
"sql" <sql@.discussions.microsoft.com> wrote in message
news:37D5B093-4776-4693-93BD-E17FA54B50F4@.microsoft.com...
> How much memory can SQL Server 2000 std. address?
> SQL Server 2000 std. is running on Windows 2003 server std edition.
> Total memory was 1.5 GB but extra memory was added later.
>
> How can I check how much memory is the SQL server configured to
> address? Is there a dbcc command.
>
> Any help will be appreciated.
>
|||On Jun 20, 5:00 am, sql <s...@.discussions.microsoft.com> wrote:
> How much memory can SQL Server 2000 std. address?
> SQL Server 2000 std. is running on Windows 2003 server std edition.
> Total memory was 1.5 GB but extra memory was added later.
> How can I check how much memory is the SQL server configured to
> address? Is there a dbcc command.
> Any help will be appreciated.
SELECT * FROM sysperfinfo WHERE counter_name ='Total Server Memory
(KB)'