Sql Memory Leaks: 2gb

Showing posts with label 2gb. Show all posts

Wednesday, March 21, 2012

Memory useage

Hi,
We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at 1.23GB
Total Commit Charge. Sqlservr.exe takes up about 980MB of this memory.
We have a few intensive data extractions that we would like to speed up and
the server seems to be the bottle neck.
Is there a point in adding more RAM to this server or will sqlservr.exe just
keep on taking as much memory as we give it? It is does keep taking the
memory, how do we know when enough is enough?
Svend.
Svend wrote:
> Hi,
> We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at
> 1.23GB Total Commit Charge. Sqlservr.exe takes up about 980MB of this
> memory.
> We have a few intensive data extractions that we would like to speed
> up and the server seems to be the bottle neck.
> Is there a point in adding more RAM to this server or will
> sqlservr.exe just keep on taking as much memory as we give it? It is
> does keep taking the memory, how do we know when enough is enough?
> Svend.
Depends entirely on your server, your databases, and how they are used.
There's no question that more RAM is helpful. Data extraction routines
might not benefit much from a increase, however. You are likely
accessing a lot of data during the extraction. SQL Server loads this
data into cache as it is read and consumes memory during the process.
Once memory is depleted, it dumps the oldest pages as it reads new ones
from disk. If you're not going back to the same data pages a second
time, then you might benefit from more efficient disk access or added
CPU to speed up these routines depending on where the bottlenexk really
is. For normal production use, added memory helps if users continually
access the same data since it prevent SQL Server from having to pull the
information from disk.
David Gugick
Imceda Software
www.imceda.com

Memory useage

Hi,
We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at 1.23GB
Total Commit Charge. Sqlservr.exe takes up about 980MB of this memory.
We have a few intensive data extractions that we would like to speed up and
the server seems to be the bottle neck.
Is there a point in adding more RAM to this server or will sqlservr.exe just
keep on taking as much memory as we give it? It is does keep taking the
memory, how do we know when enough is enough?
Svend.Svend wrote:
> Hi,
> We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at
> 1.23GB Total Commit Charge. Sqlservr.exe takes up about 980MB of this
> memory.
> We have a few intensive data extractions that we would like to speed
> up and the server seems to be the bottle neck.
> Is there a point in adding more RAM to this server or will
> sqlservr.exe just keep on taking as much memory as we give it? It is
> does keep taking the memory, how do we know when enough is enough?
> Svend.
Depends entirely on your server, your databases, and how they are used.
There's no question that more RAM is helpful. Data extraction routines
might not benefit much from a increase, however. You are likely
accessing a lot of data during the extraction. SQL Server loads this
data into cache as it is read and consumes memory during the process.
Once memory is depleted, it dumps the oldest pages as it reads new ones
from disk. If you're not going back to the same data pages a second
time, then you might benefit from more efficient disk access or added
CPU to speed up these routines depending on where the bottlenexk really
is. For normal production use, added memory helps if users continually
access the same data since it prevent SQL Server from having to pull the
information from disk.
David Gugick
Imceda Software
www.imceda.com

Memory useage

Hi,
We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at 1.23GB
Total Commit Charge. Sqlservr.exe takes up about 980MB of this memory.
We have a few intensive data extractions that we would like to speed up and
the server seems to be the bottle neck.
Is there a point in adding more RAM to this server or will sqlservr.exe just
keep on taking as much memory as we give it? It is does keep taking the
memory, how do we know when enough is enough?
Svend.Svend wrote:
> Hi,
> We have an SQL 2000 server with 1.2GB of RAM. Memory useage runs at
> 1.23GB Total Commit Charge. Sqlservr.exe takes up about 980MB of this
> memory.
> We have a few intensive data extractions that we would like to speed
> up and the server seems to be the bottle neck.
> Is there a point in adding more RAM to this server or will
> sqlservr.exe just keep on taking as much memory as we give it? It is
> does keep taking the memory, how do we know when enough is enough?
> Svend.
Depends entirely on your server, your databases, and how they are used.
There's no question that more RAM is helpful. Data extraction routines
might not benefit much from a increase, however. You are likely
accessing a lot of data during the extraction. SQL Server loads this
data into cache as it is read and consumes memory during the process.
Once memory is depleted, it dumps the oldest pages as it reads new ones
from disk. If you're not going back to the same data pages a second
time, then you might benefit from more efficient disk access or added
CPU to speed up these routines depending on where the bottlenexk really
is. For normal production use, added memory helps if users continually
access the same data since it prevent SQL Server from having to pull the
information from disk.
David Gugick
Imceda Software
www.imceda.com

Monday, March 19, 2012

Memory Usage, SBS 2000 & SQL 2000

Hello,
I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
processor box with 2GB of RAM) continues to grow over a few day period. I
have double-checked to make sure that SP3a is installed and am trying to
double-check to make sure that MDAC 2.71 is installed (correctly). First,
what's the best way to assure that each of these are installed without
errors or issues? Second, if those are then, what else could be causing the
memory to continue to grow over time?
I know that I can limit the SQL configuration to 1GB (for example) but
that's not really the key problem here. As I write this the memory has
grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
Adding RAM is obviously an option but that looks like it's only going to be
a band-aid to the problem because it will just continue to eat the
additional memory unless I lock the SQL RAM usage however I feel the key
issue to determine why the RAM is continuing to grow day-by-day.
Thanks in advance!
ChrisI expect that the behavior you are seeing is normal. There is no cause for
alarm.
SQL Server 2000 Standard Edition is able to use up to 2GB of RAM. It will
not use more than that. SQL Server stores data and query plans in RAM. As
you use SQL Server it caches data and query plans to memory.
If SQL Server is the only thing running on the box you might want to leave
it as is. If you have other server apps running and consuming resources you
might want to limit memory usage to something that allows your other apps to
have enough memory. The other option, of course is to leave SQL Server
as-is and add additional memory for the other apps that you may have on this
server.
By the way, how big are your databases? If all of your user databases are
"small" (they all add up to 1GB or LESS) you should not see SQL Server
consume much over 1GB.
Bottom line: don't worry unless your server is RAM hungry. If it is RAM
hungry add some or limit SQL Server to "enough" memory.
--
Keith
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:OOfxH9alEHA.3968@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
> processor box with 2GB of RAM) continues to grow over a few day period. I
> have double-checked to make sure that SP3a is installed and am trying to
> double-check to make sure that MDAC 2.71 is installed (correctly). First,
> what's the best way to assure that each of these are installed without
> errors or issues? Second, if those are then, what else could be causing
the
> memory to continue to grow over time?
>
> I know that I can limit the SQL configuration to 1GB (for example) but
> that's not really the key problem here. As I write this the memory has
> grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
> forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
> Adding RAM is obviously an option but that looks like it's only going to
be
> a band-aid to the problem because it will just continue to eat the
> additional memory unless I lock the SQL RAM usage however I feel the key
> issue to determine why the RAM is continuing to grow day-by-day.
>
> Thanks in advance!
>
> Chris
>|||Thank you...
The database(s) right now are just about a 1/2GB and I've noticed the SQL
box hitting about 1GB of RAM. I guess I will limit it for the moment so
that other apps don't eatup too much or go past the physical limit.
Chris
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:e1VmGYblEHA.2968@.TK2MSFTNGP14.phx.gbl...
>I expect that the behavior you are seeing is normal. There is no cause for
> alarm.
> SQL Server 2000 Standard Edition is able to use up to 2GB of RAM. It will
> not use more than that. SQL Server stores data and query plans in RAM.
> As
> you use SQL Server it caches data and query plans to memory.
> If SQL Server is the only thing running on the box you might want to leave
> it as is. If you have other server apps running and consuming resources
> you
> might want to limit memory usage to something that allows your other apps
> to
> have enough memory. The other option, of course is to leave SQL Server
> as-is and add additional memory for the other apps that you may have on
> this
> server.
> By the way, how big are your databases? If all of your user databases are
> "small" (they all add up to 1GB or LESS) you should not see SQL Server
> consume much over 1GB.
> Bottom line: don't worry unless your server is RAM hungry. If it is RAM
> hungry add some or limit SQL Server to "enough" memory.
> --
> Keith
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:OOfxH9alEHA.3968@.TK2MSFTNGP11.phx.gbl...
>> Hello,
>>
>> I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
>> processor box with 2GB of RAM) continues to grow over a few day period.
>> I
>> have double-checked to make sure that SP3a is installed and am trying to
>> double-check to make sure that MDAC 2.71 is installed (correctly).
>> First,
>> what's the best way to assure that each of these are installed without
>> errors or issues? Second, if those are then, what else could be causing
> the
>> memory to continue to grow over time?
>>
>> I know that I can limit the SQL configuration to 1GB (for example) but
>> that's not really the key problem here. As I write this the memory has
>> grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
>> forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
>> Adding RAM is obviously an option but that looks like it's only going to
> be
>> a band-aid to the problem because it will just continue to eat the
>> additional memory unless I lock the SQL RAM usage however I feel the key
>> issue to determine why the RAM is continuing to grow day-by-day.
>>
>> Thanks in advance!
>>
>> Chris
>>
>

Memory Usage, SBS 2000 & SQL 2000

Hello,
I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
processor box with 2GB of RAM) continues to grow over a few day period. I
have double-checked to make sure that SP3a is installed and am trying to
double-check to make sure that MDAC 2.71 is installed (correctly). First,
what's the best way to assure that each of these are installed without
errors or issues? Second, if those are then, what else could be causing the
memory to continue to grow over time?
I know that I can limit the SQL configuration to 1GB (for example) but
that's not really the key problem here. As I write this the memory has
grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
Adding RAM is obviously an option but that looks like it's only going to be
a band-aid to the problem because it will just continue to eat the
additional memory unless I lock the SQL RAM usage however I feel the key
issue to determine why the RAM is continuing to grow day-by-day.
Thanks in advance!
Chris
I expect that the behavior you are seeing is normal. There is no cause for
alarm.
SQL Server 2000 Standard Edition is able to use up to 2GB of RAM. It will
not use more than that. SQL Server stores data and query plans in RAM. As
you use SQL Server it caches data and query plans to memory.
If SQL Server is the only thing running on the box you might want to leave
it as is. If you have other server apps running and consuming resources you
might want to limit memory usage to something that allows your other apps to
have enough memory. The other option, of course is to leave SQL Server
as-is and add additional memory for the other apps that you may have on this
server.
By the way, how big are your databases? If all of your user databases are
"small" (they all add up to 1GB or LESS) you should not see SQL Server
consume much over 1GB.
Bottom line: don't worry unless your server is RAM hungry. If it is RAM
hungry add some or limit SQL Server to "enough" memory.
Keith
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:OOfxH9alEHA.3968@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> I am noticing that memory on our SQL 2000 (on an SBS 2000 box, a dual
> processor box with 2GB of RAM) continues to grow over a few day period. I
> have double-checked to make sure that SP3a is installed and am trying to
> double-check to make sure that MDAC 2.71 is installed (correctly). First,
> what's the best way to assure that each of these are installed without
> errors or issues? Second, if those are then, what else could be causing
the
> memory to continue to grow over time?
>
> I know that I can limit the SQL configuration to 1GB (for example) but
> that's not really the key problem here. As I write this the memory has
> grown from 287MB to 314MB and by the weekend will likely be over 1GB thus
> forcing the box to be using 2.2 - 2.8 GB of RAM when it only has 2GB's.
> Adding RAM is obviously an option but that looks like it's only going to
be
> a band-aid to the problem because it will just continue to eat the
> additional memory unless I lock the SQL RAM usage however I feel the key
> issue to determine why the RAM is continuing to grow day-by-day.
>
> Thanks in advance!
>
> Chris
>
|||Thank you...
The database(s) right now are just about a 1/2GB and I've noticed the SQL
box hitting about 1GB of RAM. I guess I will limit it for the moment so
that other apps don't eatup too much or go past the physical limit.
Chris
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:e1VmGYblEHA.2968@.TK2MSFTNGP14.phx.gbl...
>I expect that the behavior you are seeing is normal. There is no cause for
> alarm.
> SQL Server 2000 Standard Edition is able to use up to 2GB of RAM. It will
> not use more than that. SQL Server stores data and query plans in RAM.
> As
> you use SQL Server it caches data and query plans to memory.
> If SQL Server is the only thing running on the box you might want to leave
> it as is. If you have other server apps running and consuming resources
> you
> might want to limit memory usage to something that allows your other apps
> to
> have enough memory. The other option, of course is to leave SQL Server
> as-is and add additional memory for the other apps that you may have on
> this
> server.
> By the way, how big are your databases? If all of your user databases are
> "small" (they all add up to 1GB or LESS) you should not see SQL Server
> consume much over 1GB.
> Bottom line: don't worry unless your server is RAM hungry. If it is RAM
> hungry add some or limit SQL Server to "enough" memory.
> --
> Keith
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:OOfxH9alEHA.3968@.TK2MSFTNGP11.phx.gbl...
> the
> be
>

Memory Usage on SQL2k

We have a SQL Server Enterprise edition cluster.
The server has 2.5GB of RAM and SQL Server has Max Server Memory set to 2GB.
However, the Task Manager indicates that SQL Server is only using about 1750MB
There's no question in my mind that this server's load is such that it should
use every ounce of RAM.
Do I need to set the /3GB switch?
I think this /3 switch will be ignored since your OS is not running with the Windows extended memory switch (AWE) with 3+ gig physical memory. ie... the sql mem manager is dependent on OS mem allocation on startup ?
This apparent margin of RAM that appears to not be utilized by sql could still be possibly or potentially utilized under certain load conditions that you have yet to observe from the perspective of task manager. SS memory management certainly does have a
"mind" of it's own...
|||The behavior you show is as expected. Your using TASK Manager to view
memory and that is not always accurate with sql server. Use the sql memory
counters in perfmon instead. By default any application (SQL Server is an
application) can use up to 2GB of memory and the OS can use up to 2GB. You
have 2.5GB of which SQL Server will use up to 2GB. There are different
parts of sql server that use different parts of the available memory. When
SQL Server starts up it will reserve by default 256MB of memory for what is
called Memory to Leave. This is where the memory for among several other
things the worker threads are spawned. This comes right off the top of the
2GB and the rest is left for the memory pool which will dynamically use the
rest up to 2GB. So 2GB - 256MB is approx. 1.75GB and what you are seeing.
This is normal. The OS can use the other .5GB for it's use and any other
apps that you may need to run on the server. You could use the /3GB switch
to allow sql server to use up to 3GB (if you had that much) but in your case
you would starve the OS and paging would occur. Unless you add more ram I
wouldn't touch what you have.
Andrew J. Kelly SQL MVP
<bb_43@.hotmail.com> wrote in message
news:v5Ndc.50141$dF7.24237@.newssvr25.news.prodigy. com...
> We have a SQL Server Enterprise edition cluster.
> The server has 2.5GB of RAM and SQL Server has Max Server Memory set to
2GB.
> However, the Task Manager indicates that SQL Server is only using about
1750MB
> There's no question in my mind that this server's load is such that it
should
> use every ounce of RAM.
> Do I need to set the /3GB switch?
|||The other answers are great. I wouldn't to stress what Andrew pointed out...
regardless of whether you can or not.. use the switch in this case...
you would ALWAYS want to allow the OS to have a reasonable amount of space
for memory .5G on a 2.5 G big is really a min IMHO. Many people starve the
OS without ever realizing it... For example... on a 4G box.. you would
really want to give the OS ~1G.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
<bb_43@.hotmail.com> wrote in message
news:v5Ndc.50141$dF7.24237@.newssvr25.news.prodigy. com...
> We have a SQL Server Enterprise edition cluster.
> The server has 2.5GB of RAM and SQL Server has Max Server Memory set to
2GB.
> However, the Task Manager indicates that SQL Server is only using about
1750MB
> There's no question in my mind that this server's load is such that it
should
> use every ounce of RAM.
> Do I need to set the /3GB switch?

Memory Usage on SQL2k

We have a SQL Server Enterprise edition cluster.
The server has 2.5GB of RAM and SQL Server has Max Server Memory set to 2GB.
However, the Task Manager indicates that SQL Server is only using about 1750
MB
There's no question in my mind that this server's load is such that it shoul
d
use every ounce of RAM.
Do I need to set the /3GB switch?I think this /3 switch will be ignored since your OS is not running with the
Windows extended memory switch (AWE) with 3+ gig physical memory. ie... the
sql mem manager is dependent on OS mem allocation on startup ?
This apparent margin of RAM that appears to not be utilized by sql could sti
ll be possibly or potentially utilized under certain load conditions that yo
u have yet to observe from the perspective of task manager. SS memory manage
ment certainly does have a
"mind" of it's own...|||The behavior you show is as expected. Your using TASK Manager to view
memory and that is not always accurate with sql server. Use the sql memory
counters in perfmon instead. By default any application (SQL Server is an
application) can use up to 2GB of memory and the OS can use up to 2GB. You
have 2.5GB of which SQL Server will use up to 2GB. There are different
parts of sql server that use different parts of the available memory. When
SQL Server starts up it will reserve by default 256MB of memory for what is
called Memory to Leave. This is where the memory for among several other
things the worker threads are spawned. This comes right off the top of the
2GB and the rest is left for the memory pool which will dynamically use the
rest up to 2GB. So 2GB - 256MB is approx. 1.75GB and what you are seeing.
This is normal. The OS can use the other .5GB for it's use and any other
apps that you may need to run on the server. You could use the /3GB switch
to allow sql server to use up to 3GB (if you had that much) but in your case
you would starve the OS and paging would occur. Unless you add more ram I
wouldn't touch what you have.
Andrew J. Kelly SQL MVP
<bb_43@.hotmail.com> wrote in message
news:v5Ndc.50141$dF7.24237@.newssvr25.news.prodigy.com...
> We have a SQL Server Enterprise edition cluster.
> The server has 2.5GB of RAM and SQL Server has Max Server Memory set to
2GB.
> However, the Task Manager indicates that SQL Server is only using about
1750MB
> There's no question in my mind that this server's load is such that it
should
> use every ounce of RAM.
> Do I need to set the /3GB switch?|||The other answers are great. I wouldn't to stress what Andrew pointed out...
regardless of whether you can or not.. use the switch in this case...
you would ALWAYS want to allow the OS to have a reasonable amount of space
for memory .5G on a 2.5 G big is really a min IMHO. Many people starve the
OS without ever realizing it... For example... on a 4G box.. you would
really want to give the OS ~1G.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
<bb_43@.hotmail.com> wrote in message
news:v5Ndc.50141$dF7.24237@.newssvr25.news.prodigy.com...
> We have a SQL Server Enterprise edition cluster.
> The server has 2.5GB of RAM and SQL Server has Max Server Memory set to
2GB.
> However, the Task Manager indicates that SQL Server is only using about
1750MB
> There's no question in my mind that this server's load is such that it
should
> use every ounce of RAM.
> Do I need to set the /3GB switch?

Memory Usage on SQL2k

We have a SQL Server Enterprise edition cluster.
The server has 2.5GB of RAM and SQL Server has Max Server Memory set to 2GB.
However, the Task Manager indicates that SQL Server is only using about 1750MB
There's no question in my mind that this server's load is such that it should
use every ounce of RAM.
Do I need to set the /3GB switch?I think this /3 switch will be ignored since your OS is not running with the Windows extended memory switch (AWE) with 3+ gig physical memory. ie... the sql mem manager is dependent on OS mem allocation on startup
This apparent margin of RAM that appears to not be utilized by sql could still be possibly or potentially utilized under certain load conditions that you have yet to observe from the perspective of task manager. SS memory management certainly does have a "mind" of it's own...|||The behavior you show is as expected. Your using TASK Manager to view
memory and that is not always accurate with sql server. Use the sql memory
counters in perfmon instead. By default any application (SQL Server is an
application) can use up to 2GB of memory and the OS can use up to 2GB. You
have 2.5GB of which SQL Server will use up to 2GB. There are different
parts of sql server that use different parts of the available memory. When
SQL Server starts up it will reserve by default 256MB of memory for what is
called Memory to Leave. This is where the memory for among several other
things the worker threads are spawned. This comes right off the top of the
2GB and the rest is left for the memory pool which will dynamically use the
rest up to 2GB. So 2GB - 256MB is approx. 1.75GB and what you are seeing.
This is normal. The OS can use the other .5GB for it's use and any other
apps that you may need to run on the server. You could use the /3GB switch
to allow sql server to use up to 3GB (if you had that much) but in your case
you would starve the OS and paging would occur. Unless you add more ram I
wouldn't touch what you have.
--
Andrew J. Kelly SQL MVP
<bb_43@.hotmail.com> wrote in message
news:v5Ndc.50141$dF7.24237@.newssvr25.news.prodigy.com...
> We have a SQL Server Enterprise edition cluster.
> The server has 2.5GB of RAM and SQL Server has Max Server Memory set to
2GB.
> However, the Task Manager indicates that SQL Server is only using about
1750MB
> There's no question in my mind that this server's load is such that it
should
> use every ounce of RAM.
> Do I need to set the /3GB switch?|||The other answers are great. I wouldn't to stress what Andrew pointed out...
regardless of whether you can or not.. use the switch in this case...
you would ALWAYS want to allow the OS to have a reasonable amount of space
for memory .5G on a 2.5 G big is really a min IMHO. Many people starve the
OS without ever realizing it... For example... on a 4G box.. you would
really want to give the OS ~1G.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
<bb_43@.hotmail.com> wrote in message
news:v5Ndc.50141$dF7.24237@.newssvr25.news.prodigy.com...
> We have a SQL Server Enterprise edition cluster.
> The server has 2.5GB of RAM and SQL Server has Max Server Memory set to
2GB.
> However, the Task Manager indicates that SQL Server is only using about
1750MB
> There's no question in my mind that this server's load is such that it
should
> use every ounce of RAM.
> Do I need to set the /3GB switch?

Memory usage on SQL Server over 2 GB

Our SQL Server was using almost all 2GB of available RAM on the machine, so
we upgraded RAM to 3GB. Since then I've noticed it's still using almost
exactly 2GB. I'm guessing this means there is some limitation on how much
memory SQL Server will make use of. Is there some switch that allows SQL to
make use of the additional memory ?
SQL Server 2000 on Windows 2000 (both standard editions)Hi
SQL Server 2000 Standard edition will only allow a maximum of 2GB RAM. So the rest 1 GB will be used for all the other application including OS
SQL Server Enterprise will support 4 GB RAM
Thank
Har
MCDB
-- Richard wrote: --
Our SQL Server was using almost all 2GB of available RAM on the machine, s
we upgraded RAM to 3GB. Since then I've noticed it's still using almos
exactly 2GB. I'm guessing this means there is some limitation on how muc
memory SQL Server will make use of. Is there some switch that allows SQL t
make use of the additional memory
SQL Server 2000 on Windows 2000 (both standard editions

Memory usage on SQL Server over 2 GB

Our SQL Server was using almost all 2GB of available RAM on the machine, so
we upgraded RAM to 3GB. Since then I've noticed it's still using almost
exactly 2GB. I'm guessing this means there is some limitation on how much
memory SQL Server will make use of. Is there some switch that allows SQL to
make use of the additional memory ?
SQL Server 2000 on Windows 2000 (both standard editions)
Hi,
SQL Server 2000 Standard edition will only allow a maximum of 2GB RAM. So the rest 1 GB will be used for all the other application including OS.
SQL Server Enterprise will support 4 GB RAM.
Thanks
Hari
MCDBA
-- Richard wrote: --
Our SQL Server was using almost all 2GB of available RAM on the machine, so
we upgraded RAM to 3GB. Since then I've noticed it's still using almost
exactly 2GB. I'm guessing this means there is some limitation on how much
memory SQL Server will make use of. Is there some switch that allows SQL to
make use of the additional memory ?
SQL Server 2000 on Windows 2000 (both standard editions)

Memory usage on SQL Server over 2 GB

Our SQL Server was using almost all 2GB of available RAM on the machine, so
we upgraded RAM to 3GB. Since then I've noticed it's still using almost
exactly 2GB. I'm guessing this means there is some limitation on how much
memory SQL Server will make use of. Is there some switch that allows SQL to
make use of the additional memory ?
SQL Server 2000 on Windows 2000 (both standard editions)Hi,
SQL Server 2000 Standard edition will only allow a maximum of 2GB RAM. So th
e rest 1 GB will be used for all the other application including OS.
SQL Server Enterprise will support 4 GB RAM.
Thanks
Hari
MCDBA
-- Richard wrote: --
Our SQL Server was using almost all 2GB of available RAM on the machine, so
we upgraded RAM to 3GB. Since then I've noticed it's still using almost
exactly 2GB. I'm guessing this means there is some limitation on how much
memory SQL Server will make use of. Is there some switch that allows SQL to
make use of the additional memory ?
SQL Server 2000 on Windows 2000 (both standard editions)

Monday, March 12, 2012

Memory usage

Friday, March 9, 2012

Memory usage

Hi,
We have a server with 4gb of memory, the server is configured to dynamically
allocate memory to the max yet consistently only uses 2gb.
We are using enterprise edition on 2k advanced server. Am I missing
something? Is there something else limiting the server to 2gb?
Any pointers appreciated
Ta
SimonHave a look at
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
"skilla31" <simon@.ris.org.uk> wrote in message
news:uEFQUOrOFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have a server with 4gb of memory, the server is configured to
dynamically
> allocate memory to the max yet consistently only uses 2gb.
> We are using enterprise edition on 2k advanced server. Am I missing
> something? Is there something else limiting the server to 2gb?
> Any pointers appreciated
> Ta
> Simon
>|||See if the following link helps:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
"skilla31" wrote:
> Hi,
> We have a server with 4gb of memory, the server is configured to dynamically
> allocate memory to the max yet consistently only uses 2gb.
> We are using enterprise edition on 2k advanced server. Am I missing
> something? Is there something else limiting the server to 2gb?
> Any pointers appreciated
> Ta
> Simon
>
>|||Wow,
Thanks Uri. Does this really mean that by default sql server will only use
2GB? Unless you use AWE
The devil really is in the detail
Simon
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OiGtESrOFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Have a look at
> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>
>
>
> "skilla31" <simon@.ris.org.uk> wrote in message
> news:uEFQUOrOFHA.3072@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> We have a server with 4gb of memory, the server is configured to
> dynamically
>> allocate memory to the max yet consistently only uses 2gb.
>> We are using enterprise edition on 2k advanced server. Am I missing
>> something? Is there something else limiting the server to 2gb?
>> Any pointers appreciated
>> Ta
>> Simon
>>
>|||Set the /3GB switch in the Boot.ini file and it will be able to use 3GB. It
is usually best to leave the other 1GB for the OS and any other apps thay
may run on the server. You can use AWE to use some of the memory in between
3 and 4GB but it is usually not worth the effort for a few hundred MB's.
--
Andrew J. Kelly SQL MVP
"skilla31" <simon@.ris.org.uk> wrote in message
news:eJfTOZrOFHA.4028@.tk2msftngp13.phx.gbl...
> Wow,
> Thanks Uri. Does this really mean that by default sql server will only use
> 2GB? Unless you use AWE
> The devil really is in the detail
> Simon
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OiGtESrOFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> Have a look at
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>>
>>
>>
>> "skilla31" <simon@.ris.org.uk> wrote in message
>> news:uEFQUOrOFHA.3072@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> We have a server with 4gb of memory, the server is configured to
>> dynamically
>> allocate memory to the max yet consistently only uses 2gb.
>> We are using enterprise edition on 2k advanced server. Am I missing
>> something? Is there something else limiting the server to 2gb?
>> Any pointers appreciated
>> Ta
>> Simon
>>
>>
>|||This is true for ALL Windows server apps (excluding 64 bit servers.)
the OS for standard servers has 4 GB memory of which a max of 2 GB can be
allocated to ANY program..., so this is not a SQL issue, but an OS issue...
We can use the /3GB boot. ini switch and/or the AWE extensions to increase
memory...
64 bit editiions of the OS will open the door to greater memory ...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skilla31" <simon@.ris.org.uk> wrote in message
news:uEFQUOrOFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have a server with 4gb of memory, the server is configured to
> dynamically allocate memory to the max yet consistently only uses 2gb.
> We are using enterprise edition on 2k advanced server. Am I missing
> something? Is there something else limiting the server to 2gb?
> Any pointers appreciated
> Ta
> Simon
>|||Thanks Andrew,
We are using advanced server. I'm thinking of upgrading the server to 6GB
and allowing SQL Server to use up to 5GB.
So I'm thinking, I set the switch to /5GB and set max memory to 5GB, is this
feasible
Simon
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O%23S5bhrOFHA.3000@.TK2MSFTNGP10.phx.gbl...
> Set the /3GB switch in the Boot.ini file and it will be able to use 3GB.
> It is usually best to leave the other 1GB for the OS and any other apps
> thay may run on the server. You can use AWE to use some of the memory in
> between 3 and 4GB but it is usually not worth the effort for a few hundred
> MB's.
> --
> Andrew J. Kelly SQL MVP
>
> "skilla31" <simon@.ris.org.uk> wrote in message
> news:eJfTOZrOFHA.4028@.tk2msftngp13.phx.gbl...
>> Wow,
>> Thanks Uri. Does this really mean that by default sql server will only
>> use 2GB? Unless you use AWE
>> The devil really is in the detail
>> Simon
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OiGtESrOFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> Have a look at
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>>
>>
>>
>> "skilla31" <simon@.ris.org.uk> wrote in message
>> news:uEFQUOrOFHA.3072@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> We have a server with 4gb of memory, the server is configured to
>> dynamically
>> allocate memory to the max yet consistently only uses 2gb.
>> We are using enterprise edition on 2k advanced server. Am I missing
>> something? Is there something else limiting the server to 2gb?
>> Any pointers appreciated
>> Ta
>> Simon
>>
>>
>>
>|||No that is not how it works<g>. You set the /3GB to allow sql server to
access 3GB (vs. 2GB) of directly addressable memory. You use AWE on top of
that to gain access to memory above 4GB and that would leave 1GB for the OS.
So you need to do this:
Set /3GB in Boot.ini (allows apps to use 3GB vs. 2GB)
Set /PAE in Boot.ini (allows the OS to use more than 4GB)
Set AWE enabled wtih sp_configure (Allows Sql Server to access more than
4GB.)
Set MAX Memory to 5GB (Limits the memory usage of Sql server to 5GB since
it is not dynamic with AWE so you leave 1 GB for the OS)
http://sqljunkies.com/Tutorial/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.scuk
Memory
http://www.sql-server-performance.com/awe_memory.asp Using AWE Memory
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
http://www.support.microsoft.com/?id=274750 Memory config
http://www.support.microsoft.com/?id=283037 Large Memory Support Is
Available in Windows 2000 (AWE)
http://www.support.microsoft.com/?id=811891 Can not use more than 2GB of
memory
Andrew J. Kelly SQL MVP
"skilla31" <simon@.ris.org.uk> wrote in message
news:eZZVCesOFHA.3144@.tk2msftngp13.phx.gbl...
> Thanks Andrew,
> We are using advanced server. I'm thinking of upgrading the server to 6GB
> and allowing SQL Server to use up to 5GB.
> So I'm thinking, I set the switch to /5GB and set max memory to 5GB, is
> this feasible
> Simon
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O%23S5bhrOFHA.3000@.TK2MSFTNGP10.phx.gbl...
>> Set the /3GB switch in the Boot.ini file and it will be able to use 3GB.
>> It is usually best to leave the other 1GB for the OS and any other apps
>> thay may run on the server. You can use AWE to use some of the memory in
>> between 3 and 4GB but it is usually not worth the effort for a few
>> hundred MB's.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "skilla31" <simon@.ris.org.uk> wrote in message
>> news:eJfTOZrOFHA.4028@.tk2msftngp13.phx.gbl...
>> Wow,
>> Thanks Uri. Does this really mean that by default sql server will only
>> use 2GB? Unless you use AWE
>> The devil really is in the detail
>> Simon
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OiGtESrOFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> Have a look at
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>>
>>
>>
>> "skilla31" <simon@.ris.org.uk> wrote in message
>> news:uEFQUOrOFHA.3072@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> We have a server with 4gb of memory, the server is configured to
>> dynamically
>> allocate memory to the max yet consistently only uses 2gb.
>> We are using enterprise edition on 2k advanced server. Am I missing
>> something? Is there something else limiting the server to 2gb?
>> Any pointers appreciated
>> Ta
>> Simon
>>
>>
>>
>>
>|||Thanks again Andrew,
Sorry for being so dim, I think I've got this. So based on my desired
configuration (6 GB with 5GB available to sql server and 1GB left for the
OS)
I would
1. set the following switches in the boot.ini /3GB /PAE
2. Then run
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 5120
RECONFIGURE
GO
Interestingly on my test system I think max memory is displayed in KB, when
I try to set max memory in kb above 2GB I get a conversion error (decimal to
int). When I then set max memory in mb and run sp_configure it displays it
mb. Which makes me nervous.
Although I'm almost certain memory is the bottleneck can you point me in the
right direction for articles on memory sizing for db servers
Ta
Simon
PS: didn't get the "<g>" in previous message
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eh2kO1sOFHA.3704@.TK2MSFTNGP12.phx.gbl...
> No that is not how it works<g>. You set the /3GB to allow sql server to
> access 3GB (vs. 2GB) of directly addressable memory. You use AWE on top
> of that to gain access to memory above 4GB and that would leave 1GB for
> the OS. So you need to do this:
> Set /3GB in Boot.ini (allows apps to use 3GB vs. 2GB)
> Set /PAE in Boot.ini (allows the OS to use more than 4GB)
> Set AWE enabled wtih sp_configure (Allows Sql Server to access more than
> 4GB.)
> Set MAX Memory to 5GB (Limits the memory usage of Sql server to 5GB since
> it is not dynamic with AWE so you leave 1 GB for the OS)
>
> http://sqljunkies.com/Tutorial/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.scuk
> Memory
> http://www.sql-server-performance.com/awe_memory.asp Using AWE Memory
> http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000
> memory usage
> http://www.support.microsoft.com/?id=274750 Memory config
> http://www.support.microsoft.com/?id=283037 Large Memory Support
> Is Available in Windows 2000 (AWE)
> http://www.support.microsoft.com/?id=811891 Can not use more than 2GB of
> memory
>
> --
> Andrew J. Kelly SQL MVP
>
> "skilla31" <simon@.ris.org.uk> wrote in message
> news:eZZVCesOFHA.3144@.tk2msftngp13.phx.gbl...
>> Thanks Andrew,
>> We are using advanced server. I'm thinking of upgrading the server to 6GB
>> and allowing SQL Server to use up to 5GB.
>> So I'm thinking, I set the switch to /5GB and set max memory to 5GB, is
>> this feasible
>> Simon
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O%23S5bhrOFHA.3000@.TK2MSFTNGP10.phx.gbl...
>> Set the /3GB switch in the Boot.ini file and it will be able to use 3GB.
>> It is usually best to leave the other 1GB for the OS and any other apps
>> thay may run on the server. You can use AWE to use some of the memory
>> in between 3 and 4GB but it is usually not worth the effort for a few
>> hundred MB's.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "skilla31" <simon@.ris.org.uk> wrote in message
>> news:eJfTOZrOFHA.4028@.tk2msftngp13.phx.gbl...
>> Wow,
>> Thanks Uri. Does this really mean that by default sql server will only
>> use 2GB? Unless you use AWE
>> The devil really is in the detail
>> Simon
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OiGtESrOFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> Have a look at
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>>
>>
>>
>> "skilla31" <simon@.ris.org.uk> wrote in message
>> news:uEFQUOrOFHA.3072@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> We have a server with 4gb of memory, the server is configured to
>> dynamically
>> allocate memory to the max yet consistently only uses 2gb.
>> We are using enterprise edition on 2k advanced server. Am I missing
>> something? Is there something else limiting the server to 2gb?
>> Any pointers appreciated
>> Ta
>> Simon
>>
>>
>>
>>
>>
>|||Here are some links related to setting memory but there isn't much you can
do other than determine how much SQL Server will use. Basically give it all
you have after leaving some for the OS. If you run any other apps on the
machine you must account for them as well. If memory is your bottleneck you
most likely have poorly tuned queries or improperly indexed tables. Running
some traces should help to determine that pretty quickly.
http://sqljunkies.com/Tutorial/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.scuk
Memory
http://www.sql-server-performance.com/awe_memory.asp Using AWE Memory
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
http://www.support.microsoft.com/?id=274750 Memory config
http://www.support.microsoft.com/?id=283037 Large Memory Support Is
Available in Windows 2000 (AWE)
http://www.support.microsoft.com/?id=811891 Can not use more than 2GB of
memory
--
Andrew J. Kelly SQL MVP
"skilla31" <simon@.ris.org.uk> wrote in message
news:u68zCw2OFHA.3380@.TK2MSFTNGP15.phx.gbl...
> Thanks again Andrew,
> Sorry for being so dim, I think I've got this. So based on my desired
> configuration (6 GB with 5GB available to sql server and 1GB left for the
> OS)
> I would
> 1. set the following switches in the boot.ini /3GB /PAE
> 2. Then run
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', 5120
> RECONFIGURE
> GO
> Interestingly on my test system I think max memory is displayed in KB,
> when I try to set max memory in kb above 2GB I get a conversion error
> (decimal to int). When I then set max memory in mb and run sp_configure it
> displays it mb. Which makes me nervous.
> Although I'm almost certain memory is the bottleneck can you point me in
> the right direction for articles on memory sizing for db servers
> Ta
> Simon
> PS: didn't get the "<g>" in previous message
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eh2kO1sOFHA.3704@.TK2MSFTNGP12.phx.gbl...
>> No that is not how it works<g>. You set the /3GB to allow sql server to
>> access 3GB (vs. 2GB) of directly addressable memory. You use AWE on top
>> of that to gain access to memory above 4GB and that would leave 1GB for
>> the OS. So you need to do this:
>> Set /3GB in Boot.ini (allows apps to use 3GB vs. 2GB)
>> Set /PAE in Boot.ini (allows the OS to use more than 4GB)
>> Set AWE enabled wtih sp_configure (Allows Sql Server to access more than
>> 4GB.)
>> Set MAX Memory to 5GB (Limits the memory usage of Sql server to 5GB
>> since it is not dynamic with AWE so you leave 1 GB for the OS)
>>
>> http://sqljunkies.com/Tutorial/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.scuk
>> Memory
>> http://www.sql-server-performance.com/awe_memory.asp Using AWE Memory
>> http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000
>> memory usage
>> http://www.support.microsoft.com/?id=274750 Memory config
>> http://www.support.microsoft.com/?id=283037 Large Memory Support
>> Is Available in Windows 2000 (AWE)
>> http://www.support.microsoft.com/?id=811891 Can not use more than 2GB of
>> memory
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "skilla31" <simon@.ris.org.uk> wrote in message
>> news:eZZVCesOFHA.3144@.tk2msftngp13.phx.gbl...
>> Thanks Andrew,
>> We are using advanced server. I'm thinking of upgrading the server to
>> 6GB and allowing SQL Server to use up to 5GB.
>> So I'm thinking, I set the switch to /5GB and set max memory to 5GB, is
>> this feasible
>> Simon
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O%23S5bhrOFHA.3000@.TK2MSFTNGP10.phx.gbl...
>> Set the /3GB switch in the Boot.ini file and it will be able to use
>> 3GB. It is usually best to leave the other 1GB for the OS and any other
>> apps thay may run on the server. You can use AWE to use some of the
>> memory in between 3 and 4GB but it is usually not worth the effort for
>> a few hundred MB's.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "skilla31" <simon@.ris.org.uk> wrote in message
>> news:eJfTOZrOFHA.4028@.tk2msftngp13.phx.gbl...
>> Wow,
>> Thanks Uri. Does this really mean that by default sql server will only
>> use 2GB? Unless you use AWE
>> The devil really is in the detail
>> Simon
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OiGtESrOFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> Have a look at
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
>>
>>
>>
>> "skilla31" <simon@.ris.org.uk> wrote in message
>> news:uEFQUOrOFHA.3072@.TK2MSFTNGP09.phx.gbl...
>>> Hi,
>>>
>>> We have a server with 4gb of memory, the server is configured to
>> dynamically
>>> allocate memory to the max yet consistently only uses 2gb.
>>>
>>> We are using enterprise edition on 2k advanced server. Am I missing
>>> something? Is there something else limiting the server to 2gb?
>>>
>>> Any pointers appreciated
>>>
>>> Ta
>>>
>>> Simon
>>>
>>>
>>
>>
>>
>>
>>
>

Memory settings/considerations when running multiple instances of SQL Server

I've got a server, Dual PIII 733's with 2gb of RAM that I setup with 3 named
instances of SQL Server. I'm not sure what I should do with the memory
settings, specifically the min and max and whether that has any bearing or
not.
I noticed when pulling up task manager that the memory for 1 instance will
grow to be 800mb or so while the other 2 will hover around 46-60 mb. I
tried setting them each to use exactly 500mb but that didn't appear to have
any impact. I know that the memory will grow as the databases get used but
the other 2 instances still stayed low even when I was restoring a 40gb
database. I also noticed that stopping the services from the first instance
would kill the sqlserver.exe process that had the high allocation of memory,
but the others still stayed in the 46-60 mb range.
How can I manage the memory used by each instance of SQL Server or what
should I set so that I don't have to worry about it? Any help with this is
greatly appreciated.
Thx,
KenThis is a multi-part message in MIME format.
--000107050009040201040201
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
My advice would be just to leave it how it is. By default, SQL 2000 out
of the box uses a dynamic memory management strategy (as it seems you
already know). With only 2G of physical RAM in the box you don't need
to play with /3GB switches or AWE memory or anything fancy like that.
SQL Server will cache data into its address space as it is accessed
(restoring a DB is not the same as accessing data in that DB, which is
done via standard T-SQL statements like SELECT, INSERT, UPDATE &
DELETE). It will try to maintain a minimum free physical RAM threshold
(by default 10MB but configurable) so when another app (like one of your
other sqlservr.exe instances for example) make a request for memory and
there is less than that threshold available, it will release some of its
memory allocation in order for the OS to satisfy the new memory request
of the other app AND maintain that free memory threshold.
If the SQL instances using less RAM need the memory then they will ask
for it and get it at the other instance's expense. Basically, the most
needy SQL instances will have the most memory (as it should be).
If you really want to set hard limits for your SQL instances you can set
high & low limits for the dynamic memory management to work with via
sp_configure ("max server memory" and "min server memory") or you can
set a fixed amount of memory for SQL Server to work with (so that it
does *not* swap pages out of RAM) via sp_configure ("set working set size").
But I'd just leave it exactly how it is if I were you.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Ken Patton wrote:
>I've got a server, Dual PIII 733's with 2gb of RAM that I setup with 3 named
>instances of SQL Server. I'm not sure what I should do with the memory
>settings, specifically the min and max and whether that has any bearing or
>not.
>I noticed when pulling up task manager that the memory for 1 instance will
>grow to be 800mb or so while the other 2 will hover around 46-60 mb. I
>tried setting them each to use exactly 500mb but that didn't appear to have
>any impact. I know that the memory will grow as the databases get used but
>the other 2 instances still stayed low even when I was restoring a 40gb
>database. I also noticed that stopping the services from the first instance
>would kill the sqlserver.exe process that had the high allocation of memory,
>but the others still stayed in the 46-60 mb range.
>How can I manage the memory used by each instance of SQL Server or what
>should I set so that I don't have to worry about it? Any help with this is
>greatly appreciated.
>Thx,
>Ken
>
>
--000107050009040201040201
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>My advice would be just to leave it how it is. By default, SQL
2000 out of the box uses a dynamic memory management strategy (as it
seems you already know). With only 2G of physical RAM in the box you
don't need to play with /3GB switches or AWE memory or anything fancy
like that.<br>
<br>
SQL Server will cache data into its address space as it is accessed
(restoring a DB is not the same as accessing data in that DB, which is
done via standard T-SQL statements like SELECT, INSERT, UPDATE &
DELETE). It will try to maintain a minimum free physical RAM threshold
(by default 10MB but configurable) so when another app (like one of
your other sqlservr.exe instances for example) make a request for
memory and there is less than that threshold available, it will release
some of its memory allocation in order for the OS to satisfy the new
memory request of the other app AND maintain that free memory threshold.<br>
<br>
If the SQL instances using less RAM need the memory then they will ask
for it and get it at the other instance's expense. Basically, the most
needy SQL instances will have the most memory (as it should be).<br>
<br>
If you really want to set hard limits for your SQL instances you can
set high & low limits for the dynamic memory management to work
with via sp_configure ("max server memory" and "min server memory") or
you can set a fixed amount of memory for SQL Server to work with (so
that it does <b>not</b> swap pages out of RAM) via sp_configure ("set
working set size").<br>
<br>
But I'd just leave it exactly how it is if I were you.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Ken Patton wrote:
<blockquote cite="midd6j517$7rh$1@.news01.intel.com" type="cite">
<pre wrap="">I've got a server, Dual PIII 733's with 2gb of RAM that I setup with 3 named
instances of SQL Server. I'm not sure what I should do with the memory
settings, specifically the min and max and whether that has any bearing or
not.
I noticed when pulling up task manager that the memory for 1 instance will
grow to be 800mb or so while the other 2 will hover around 46-60 mb. I
tried setting them each to use exactly 500mb but that didn't appear to have
any impact. I know that the memory will grow as the databases get used but
the other 2 instances still stayed low even when I was restoring a 40gb
database. I also noticed that stopping the services from the first instance
would kill the sqlserver.exe process that had the high allocation of memory,
but the others still stayed in the 46-60 mb range.
How can I manage the memory used by each instance of SQL Server or what
should I set so that I don't have to worry about it? Any help with this is
greatly appreciated.
Thx,
Ken
</pre>
</blockquote>
</body>
</html>
--000107050009040201040201--

Memory required for SQL Server 2000

We have been running an application on SQL Server 2000 on a Windows 2000
Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
From the statistics, we find that the memory utilization of that Windows
2000 Server is around 200MB of memory. In this way, we attempt to change
the RAM from 2GB to 1GB. However, we find that the memory utilization is as
high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
We just thinking the rise of memory required is only for the first 1 to 2
hours OR not ? If there any reason for a SQL Server 2000 to grab more
memory when we start it up ? Will the memory be stabilized to become 200MB
again ?
Your advice is sought.This is a multi-part message in MIME format.
--060500000502050507020305
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
In most situations, SQL Server's memory allocation will be lowest when
you first start the service. SQL Server will cache each data page as it
is read from disk until a minimum free memory threshold is reached (from
memory the threshold is 10MB by default). SQL Server then removes least
recently used pages from its cache, and releases memory, as necessary in
order to maintain the free memory threshold. This means that the longer
the service runs the more memory it will use (up to a limit). This is
Microsoft's dynamic memory management model.
One exception to this "increasing memory use" model is when you
configure SQL Server to grab its entire memory allocation on service
start-up, in which case all the memory, up to the configured maximum
limit, is reserved up front. However, there are no situations (that I
can think of) where SQL Server initially uses more memory and then
releases a substantial portion of it to "stabilise" at a certain level
(unless you start messing around with DBCC to flush the data buffer &
procedure cache).
--
*mike hodgson*
http://sqlnerd.blogspot.com
Robert wrote:
>We have been running an application on SQL Server 2000 on a Windows 2000
>Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
>From the statistics, we find that the memory utilization of that Windows
>2000 Server is around 200MB of memory. In this way, we attempt to change
>the RAM from 2GB to 1GB. However, we find that the memory utilization is as
>high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
>We just thinking the rise of memory required is only for the first 1 to 2
>hours OR not ? If there any reason for a SQL Server 2000 to grab more
>memory when we start it up ? Will the memory be stabilized to become 200MB
>again ?
>
>Your advice is sought.
>
>
--060500000502050507020305
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>In most situations, SQL Server's memory allocation will be lowest
when you first start the service. SQL Server will cache each data page
as it is read from disk until a minimum free memory threshold is
reached (from memory the threshold is 10MB by default). </tt><tt>SQL
Server then removes least recently used pages from its cache,
and releases memory, as necessary in order to maintain the free memory
threshold. </tt><tt>This means that the longer the service runs the
more memory it will use (up to a limit). This is Microsoft's dynamic
memory management model.<br>
<br>
One exception to this "increasing memory use" model is when you
configure SQL Server to grab its entire memory allocation on service
start-up, in which case all the memory, up to the configured maximum
limit, is reserved up front. However, there are no situations (that I
can think of) where SQL Server initially uses more memory and then
releases a substantial portion of it to "stabilise" at a certain level
(unless you start messing around with DBCC to flush the data buffer
& procedure cache).</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Robert wrote:
<blockquote cite="midOeJwQEbnGHA.4868@.TK2MSFTNGP02.phx.gbl" type="cite">
<pre wrap="">We have been running an application on SQL Server 2000 on a Windows 2000
Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
From the statistics, we find that the memory utilization of that Windows
2000 Server is around 200MB of memory. In this way, we attempt to change
the RAM from 2GB to 1GB. However, we find that the memory utilization is as
high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
We just thinking the rise of memory required is only for the first 1 to 2
hours OR not ? If there any reason for a SQL Server 2000 to grab more
memory when we start it up ? Will the memory be stabilized to become 200MB
again ?
Your advice is sought.
</pre>
</blockquote>
</body>
</html>
--060500000502050507020305--|||This is a multi-part message in MIME format.
--=_NextPart_000_0016_01C69ED0.60E2CBA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Dear Mike,
Thank you for your advice.
If the physical RAM is 2GB and I change the maximum RAM setting for the =SQL Server 2000 to 1.5GB. Does it mean that the SQL Server 2000 will =use most up to 1.5GB RAM ?
Thanks
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message =news:OYx8BvknGHA.3340@.TK2MSFTNGP02.phx.gbl...
In most situations, SQL Server's memory allocation will be lowest when =you first start the service. SQL Server will cache each data page as it =is read from disk until a minimum free memory threshold is reached (from =memory the threshold is 10MB by default). SQL Server then removes least =recently used pages from its cache, and releases memory, as necessary in =order to maintain the free memory threshold. This means that the longer =the service runs the more memory it will use (up to a limit). This is =Microsoft's dynamic memory management model.
One exception to this "increasing memory use" model is when you =configure SQL Server to grab its entire memory allocation on service =start-up, in which case all the memory, up to the configured maximum =limit, is reserved up front. However, there are no situations (that I =can think of) where SQL Server initially uses more memory and then =releases a substantial portion of it to "stabilise" at a certain level =(unless you start messing around with DBCC to flush the data buffer & =procedure cache).
--
mike hodgson
http://sqlnerd.blogspot.com=20
Robert wrote: We have been running an application on SQL Server 2000 on a Windows 2000 =
Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
From the statistics, we find that the memory utilization of that Windows =
2000 Server is around 200MB of memory. In this way, we attempt to =change the RAM from 2GB to 1GB. However, we find that the memory utilization =is as high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
We just thinking the rise of memory required is only for the first 1 to =2 hours OR not ? If there any reason for a SQL Server 2000 to grab more memory when we start it up ? Will the memory be stabilized to become =200MB again ?
Your advice is sought.
--=_NextPart_000_0016_01C69ED0.60E2CBA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Dear Mike,
Thank you for your advice.
If the physical RAM is 2GB and I change =the maximum RAM setting for the SQL Server 2000 to 1.5GB. Does it mean that =the SQL Server 2000 will use most up to 1.5GB RAM ?
Thanks
"Mike Hodgson" wrote =in message news:OYx8BvknGHA.3340=@.TK2MSFTNGP02.phx.gbl...In most situations, SQL Server's memory allocation will be lowest when =you first start the service. SQL Server will cache each data page as it is =read from disk until a minimum free memory threshold is reached (from =memory the threshold is 10MB by default). SQL Server then removes =least recently used pages from its cache, and releases memory, as necessary =in order to maintain the free memory threshold. This means that =the longer the service runs the more memory it will use (up to a =limit). This is Microsoft's dynamic memory management model.One =exception to this "increasing memory use" model is when you configure SQL Server to =grab its entire memory allocation on service start-up, in which case all =the memory, up to the configured maximum limit, is reserved up =front. However, there are no situations (that I can think of) where SQL =Server initially uses more memory and then releases a substantial portion of =it to "stabilise" at a certain level (unless you start messing around with =DBCC to flush the data buffer & procedure cache).
--mike =hodgsonhttp://sqlnerd.blogspot.com Robert wrote: We have been running an application on SQL =Server 2000 on a Windows 2000 Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
From the statistics, we find that the memory utilization of that Windows =2000 Server is around 200MB of memory. In this way, we attempt to =change the RAM from 2GB to 1GB. However, we find that the memory utilization =is as high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
We just thinking the rise of memory required is only for the first 1 to =2 hours OR not ? If there any reason for a SQL Server 2000 to grab more memory when we start it up ? Will the memory be stabilized to become =200MB again ?
Your advice is sought.

--=_NextPart_000_0016_01C69ED0.60E2CBA0--|||This is a multi-part message in MIME format.
--060007000708090601070304
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
It means that SQL Server will be allowed, at most, a 1.5GB address
space. It does not necessarily mean this will be backed by physical
memory. If there is not enough physical memory to support SQL Server's
requests, in addition to the other applications requesting memory on the
box (including the OS itself), then part of that memory space will be
backed by virtual memory, which, needless-to-say, is not a
high-performance situation. The exception to this, which does not apply
to you because you are playing with less than 4GB of physical memory, is
AWE memory, which is always satisfied from physical memory (it is never
swapped to disk).
--
*mike hodgson*
http://sqlnerd.blogspot.com
Robert wrote:
> Dear Mike,
> Thank you for your advice.
> If the physical RAM is 2GB and I change the maximum RAM setting for
> the SQL Server 2000 to 1.5GB. Does it mean that the SQL Server 2000
> will use most up to 1.5GB RAM ?
> Thanks
>
> "Mike Hodgson" <e1minst3r@.gmail.com <mailto:e1minst3r@.gmail.com>>
> wrote in message news:OYx8BvknGHA.3340@.TK2MSFTNGP02.phx.gbl...
> In most situations, SQL Server's memory allocation will be lowest
> when you first start the service. SQL Server will cache each data
> page as it is read from disk until a minimum free memory threshold
> is reached (from memory the threshold is 10MB by default). SQL
> Server then removes least recently used pages from its cache, and
> releases memory, as necessary in order to maintain the free memory
> threshold. This means that the longer the service runs the more
> memory it will use (up to a limit). This is Microsoft's dynamic
> memory management model.
> One exception to this "increasing memory use" model is when you
> configure SQL Server to grab its entire memory allocation on
> service start-up, in which case all the memory, up to the
> configured maximum limit, is reserved up front. However, there
> are no situations (that I can think of) where SQL Server initially
> uses more memory and then releases a substantial portion of it to
> "stabilise" at a certain level (unless you start messing around
> with DBCC to flush the data buffer & procedure cache).
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Robert wrote:
>>We have been running an application on SQL Server 2000 on a Windows 2000
>>Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
>>From the statistics, we find that the memory utilization of that Windows
>>2000 Server is around 200MB of memory. In this way, we attempt to change
>>the RAM from 2GB to 1GB. However, we find that the memory utilization is as
>>high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
>>We just thinking the rise of memory required is only for the first 1 to 2
>>hours OR not ? If there any reason for a SQL Server 2000 to grab more
>>memory when we start it up ? Will the memory be stabilized to become 200MB
>>again ?
>>
>>Your advice is sought.
>>
>>
--060007000708090601070304
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>It means that SQL Server will be allowed, at most, a 1.5GB address
space. It does not necessarily mean this will be backed by physical
memory. If there is not enough physical memory to support SQL Server's
requests, in addition to the other applications requesting memory on
the box (including the OS itself), then part of that memory space will
be backed by virtual memory, which, needless-to-say, is not a
high-performance situation. The exception to this, which does not
apply to you because you are playing with less than 4GB of physical
memory, is AWE memory, which is always satisfied from physical memory
(it is never swapped to disk).</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Robert wrote:
<blockquote cite="miducrwUrnnGHA.4728@.TK2MSFTNGP03.phx.gbl" type="cite">
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<meta content="MSHTML 6.00.2900.2912" name="GENERATOR">
<style></style>
<div><font face="Arial" size="2">Dear Mike,</font></div>
<div> </div>
<div><font face="Arial" size="2">Thank you for your advice.</font></div>
<div> </div>
<div><font face="Arial" size="2">If the physical RAM is 2GB and I
change the maximum RAM setting for the SQL Server 2000 to 1.5GB. Does
it mean that the SQL Server 2000 will use most up to 1.5GB RAM ?</font></div>
<div> </div>
<div><font face="Arial" size="2">Thanks</font></div>
<div> </div>
<blockquote
style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;"
dir="ltr">
<div>"Mike Hodgson" <<a href="http://links.10026.com/?link=mailto:e1minst3r@.gmail.com">e1minst3r@.gmail.com</a>>
wrote in message <a href="http://links.10026.com/?link=news:OYx8BvknGHA.3340@.TK2MSFTNGP02.phx.gbl">news:OYx8BvknGHA.3340@.TK2MSFTNGP02.phx.gbl</a>...</div>
<tt>In most situations, SQL Server's memory allocation will be
lowest when you first start the service. SQL Server will cache each
data page as it is read from disk until a minimum free memory threshold
is reached (from memory the threshold is 10MB by default). </tt><tt>SQL
Server then removes least recently used pages from its cache, and
releases memory, as necessary in order to maintain the free memory
threshold. </tt><tt>This means that the longer the service runs the
more memory it will use (up to a limit). This is Microsoft's dynamic
memory management model.<br>
<br>
One exception to this "increasing memory use" model is when you
configure SQL Server to grab its entire memory allocation on service
start-up, in which case all the memory, up to the configured maximum
limit, is reserved up front. However, there are no situations (that I
can think of) where SQL Server initially uses more memory and then
releases a substantial portion of it to "stabilise" at a certain level
(unless you start messing around with DBCC to flush the data buffer
& procedure cache).</tt><br>
<div class="moz-signature">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Robert wrote:
<blockquote cite="midOeJwQEbnGHA.4868@.TK2MSFTNGP02.phx.gbl"
type="cite">
<pre wrap="">We have been running an application on SQL Server 2000 on a Windows 2000
Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
From the statistics, we find that the memory utilization of that Windows
2000 Server is around 200MB of memory. In this way, we attempt to change
the RAM from 2GB to 1GB. However, we find that the memory utilization is as
high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
We just thinking the rise of memory required is only for the first 1 to 2
hours OR not ? If there any reason for a SQL Server 2000 to grab more
memory when we start it up ? Will the memory be stabilized to become 200MB
again ?
Your advice is sought.
</pre>
</blockquote>
</blockquote>
</blockquote>
</body>
</html>
--060007000708090601070304--|||This is a multi-part message in MIME format.
--070103030000040705020106
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
For a little more background info about it, see the blog entry I just wrote:
http://sqlnerd.blogspot.com/2006/07/memory-use-in-sql-server.html
Plus there's a stack of info about it in BOL and several quality
publications including The Guru's Guide to SQL Server Architecture and
Internals
<http://www.amazon.com/gp/product/0201700476/sr=8-2/qid=1152005971/ref=sr_1_2/104-4550407-8498336?ie=UTF8>
by Ken Henderson and Inside SQL Server 2000
<http://www.amazon.com/gp/product/0735609985/sr=8-2/qid=1152006007/ref=pd_bbs_2/104-4550407-8498336?ie=UTF8>
by Kalen Delaney.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Mike Hodgson wrote:
> It means that SQL Server will be allowed, at most, a 1.5GB address
> space. It does not necessarily mean this will be backed by physical
> memory. If there is not enough physical memory to support SQL
> Server's requests, in addition to the other applications requesting
> memory on the box (including the OS itself), then part of that memory
> space will be backed by virtual memory, which, needless-to-say, is not
> a high-performance situation. The exception to this, which does not
> apply to you because you are playing with less than 4GB of physical
> memory, is AWE memory, which is always satisfied from physical memory
> (it is never swapped to disk).
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Robert wrote:
>> Dear Mike,
>> Thank you for your advice.
>> If the physical RAM is 2GB and I change the maximum RAM setting for
>> the SQL Server 2000 to 1.5GB. Does it mean that the SQL Server 2000
>> will use most up to 1.5GB RAM ?
>> Thanks
>>
>> "Mike Hodgson" <e1minst3r@.gmail.com <mailto:e1minst3r@.gmail.com>>
>> wrote in message news:OYx8BvknGHA.3340@.TK2MSFTNGP02.phx.gbl...
>> In most situations, SQL Server's memory allocation will be lowest
>> when you first start the service. SQL Server will cache each
>> data page as it is read from disk until a minimum free memory
>> threshold is reached (from memory the threshold is 10MB by
>> default). SQL Server then removes least recently used pages from
>> its cache, and releases memory, as necessary in order to maintain
>> the free memory threshold. This means that the longer the
>> service runs the more memory it will use (up to a limit). This
>> is Microsoft's dynamic memory management model.
>> One exception to this "increasing memory use" model is when you
>> configure SQL Server to grab its entire memory allocation on
>> service start-up, in which case all the memory, up to the
>> configured maximum limit, is reserved up front. However, there
>> are no situations (that I can think of) where SQL Server
>> initially uses more memory and then releases a substantial
>> portion of it to "stabilise" at a certain level (unless you start
>> messing around with DBCC to flush the data buffer & procedure cache).
>> --
>> *mike hodgson*
>> http://sqlnerd.blogspot.com
>>
>> Robert wrote:
>>We have been running an application on SQL Server 2000 on a Windows 2000
>>Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
>>From the statistics, we find that the memory utilization of that Windows
>>2000 Server is around 200MB of memory. In this way, we attempt to change
>>the RAM from 2GB to 1GB. However, we find that the memory utilization is as
>>high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
>>We just thinking the rise of memory required is only for the first 1 to 2
>>hours OR not ? If there any reason for a SQL Server 2000 to grab more
>>memory when we start it up ? Will the memory be stabilized to become 200MB
>>again ?
>>
>>Your advice is sought.
>>
>>
--070103030000040705020106
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>For a little more background info about it, see the blog entry I
just wrote:<br>
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com/2006/07/memory-use-in-sql-server.html</a><br>">http://sqlnerd.blogspot.com/2006/07/memory-use-in-sql-server.html">http://sqlnerd.blogspot.com/2006/07/memory-use-in-sql-server.html</a><br>
<br>
Plus there's a stack of info about it in BOL and several quality
publications including <a
href="http://links.10026.com/?link=The">http://www.amazon.com/gp/product/0201700476/sr=8-2/qid=1152005971/ref=sr_1_2/104-4550407-8498336?ie=UTF8">The
Guru's Guide to SQL Server Architecture and Internals</a> by Ken
Henderson and <a
href="http://links.10026.com/?link=Inside">http://www.amazon.com/gp/product/0735609985/sr=8-2/qid=1152006007/ref=pd_bbs_2/104-4550407-8498336?ie=UTF8">Inside
SQL Server 2000</a> by Kalen Delaney.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Mike Hodgson wrote:
<blockquote cite="midef076WpnGHA.3340@.TK2MSFTNGP02.phx.gbl" type="cite">
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
<tt>It means that SQL Server will be allowed, at most, a 1.5GB
address
space. It does not necessarily mean this will be backed by physical
memory. If there is not enough physical memory to support SQL Server's
requests, in addition to the other applications requesting memory on
the box (including the OS itself), then part of that memory space will
be backed by virtual memory, which, needless-to-say, is not a
high-performance situation. The exception to this, which does not
apply to you because you are playing with less than 4GB of physical
memory, is AWE memory, which is always satisfied from physical memory
(it is never swapped to disk).</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Robert wrote:
<blockquote cite="miducrwUrnnGHA.4728@.TK2MSFTNGP03.phx.gbl"
type="cite">
<title></title>
<meta http-equiv="Content-Type" content="text/html;">
<meta content="MSHTML 6.00.2900.2912" name="GENERATOR">
<style></style>
<div><font face="Arial" size="2">Dear Mike,</font></div>
<div> </div>
<div><font face="Arial" size="2">Thank you for your advice.</font></div>
<div> </div>
<div><font face="Arial" size="2">If the physical RAM is 2GB and I
change the maximum RAM setting for the SQL Server 2000 to 1.5GB. Does
it mean that the SQL Server 2000 will use most up to 1.5GB RAM ?</font></div>
<div> </div>
<div><font face="Arial" size="2">Thanks</font></div>
<div> </div>
<blockquote
style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;"
dir="ltr">
<div>"Mike Hodgson" <<a href="http://links.10026.com/?link=mailto:e1minst3r@.gmail.com">e1minst3r@.gmail.com</a>>
wrote in message <a href="http://links.10026.com/?link=news:OYx8BvknGHA.3340@.TK2MSFTNGP02.phx.gbl">news:OYx8BvknGHA.3340@.TK2MSFTNGP02.phx.gbl</a>...</div>
<tt>In most situations, SQL Server's memory allocation will be
lowest when you first start the service. SQL Server will cache each
data page as it is read from disk until a minimum free memory threshold
is reached (from memory the threshold is 10MB by default). </tt><tt>SQL
Server then removes least recently used pages from its cache, and
releases memory, as necessary in order to maintain the free memory
threshold. </tt><tt>This means that the longer the service runs the
more memory it will use (up to a limit). This is Microsoft's dynamic
memory management model.<br>
<br>
One exception to this "increasing memory use" model is when you
configure SQL Server to grab its entire memory allocation on service
start-up, in which case all the memory, up to the configured maximum
limit, is reserved up front. However, there are no situations (that I
can think of) where SQL Server initially uses more memory and then
releases a substantial portion of it to "stabilise" at a certain level
(unless you start messing around with DBCC to flush the data buffer
& procedure cache).</tt><br>
<div class="moz-signature">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Robert wrote:
<blockquote cite="midOeJwQEbnGHA.4868@.TK2MSFTNGP02.phx.gbl"
type="cite">
<pre wrap="">We have been running an application on SQL Server 2000 on a Windows 2000
Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
From the statistics, we find that the memory utilization of that Windows
2000 Server is around 200MB of memory. In this way, we attempt to change
the RAM from 2GB to 1GB. However, we find that the memory utilization is as
high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
We just thinking the rise of memory required is only for the first 1 to 2
hours OR not ? If there any reason for a SQL Server 2000 to grab more
memory when we start it up ? Will the memory be stabilized to become 200MB
again ?
Your advice is sought.
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</body>
</html>
--070103030000040705020106--

Wednesday, March 21, 2012

Memory useage

Memory useage

Memory useage

Monday, March 19, 2012

Memory Usage, SBS 2000 & SQL 2000

Memory Usage, SBS 2000 & SQL 2000

Memory Usage on SQL2k

Memory Usage on SQL2k

Memory Usage on SQL2k

Memory usage on SQL Server over 2 GB

Memory usage on SQL Server over 2 GB

Memory usage on SQL Server over 2 GB

Monday, March 12, 2012

Memory usage

Memory usage

Friday, March 9, 2012

Memory usage

Memory settings/considerations when running multiple instances of SQL Server

Memory required for SQL Server 2000

Sql Memory Leaks

Blog Archive

About Me