High Memory Usage by SQL 2000

Hi,
Recently i've moved some of our databases from a Windows 2000 Server with
512 physical memory to a newer Windows 2003 Server with 1024MB physical
memory.
The new server functions OK, but there is a very high usage of memory in
comparison to the old server: the sqlservr.exe process used around 400MB of
memory,
while the new one is using near 850MB after only 2 weeks (while at the
beginning it used something like 600 MB).
Is such a behaviour is normal and can be explained by that the new server
has larger available amount of physical memory?
In any case, are there any tools that helps to determine which application
catch the largest amount of memory used by SQL?
I think that such a large usage for relatively no so hard-working server is
something strange.
Any help/tips will be highly appreciated!!!
Thanks in advance,
Barak
Barak,
SQL Server 2000 will grab and hold on to as much memory as it needs. SQL
Server 2000, will not, by default, release memory back to the operating
system after using it. This is normal and expected behavior. The basic
reason is that Microsoft expects that SQL Server 2000 is installed as a
stand-alone application on a server. Thus not only is nothing gained by SQL
Server 2000 releasing memory back to the OS, but, in fact, performance is
hurt if SQL Server 2000 was continually requesting and releasing memory ...
the request for and release of memory is fairly obviously an activity that
requires some resources, at the very least CPU time, that could otherwise be
utilized.
Additional information is available in the SQL Server 2000 Books Online in
sections "Memory Architecture" and "Dynamically Managing Memory on Windows
NT and Windows 2000".
Chief Tenaya
"Barak Turovsky" <baraktur@.mail.ru> wrote in message
news:uslssSIHEHA.1432@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Recently i've moved some of our databases from a Windows 2000 Server with
> 512 physical memory to a newer Windows 2003 Server with 1024MB physical
> memory.
> The new server functions OK, but there is a very high usage of memory in
> comparison to the old server: the sqlservr.exe process used around 400MB
of
> memory,
> while the new one is using near 850MB after only 2 weeks (while at the
> beginning it used something like 600 MB).
> Is such a behaviour is normal and can be explained by that the new server
> has larger available amount of physical memory?
> In any case, are there any tools that helps to determine which application
> catch the largest amount of memory used by SQL?
> I think that such a large usage for relatively no so hard-working server
is
> something strange.
> Any help/tips will be highly appreciated!!!
> Thanks in advance,
> Barak
>
|||Barak,
BTW, within limits, the more memory available to SQL Server 2000, the better
one can expect performance from SQL Server 2000. Additional memory allows
SQL Server 2000 to create and use a larger memory cache area, thus reducing
physical I/O ... and physical I/O is more often then not what drags down
performance in most any database.
Chief Tenaya
"Barak Turovsky" <baraktur@.mail.ru> wrote in message
news:uslssSIHEHA.1432@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Recently i've moved some of our databases from a Windows 2000 Server with
> 512 physical memory to a newer Windows 2003 Server with 1024MB physical
> memory.
> The new server functions OK, but there is a very high usage of memory in
> comparison to the old server: the sqlservr.exe process used around 400MB
of
> memory,
> while the new one is using near 850MB after only 2 weeks (while at the
> beginning it used something like 600 MB).
> Is such a behaviour is normal and can be explained by that the new server
> has larger available amount of physical memory?
> In any case, are there any tools that helps to determine which application
> catch the largest amount of memory used by SQL?
> I think that such a large usage for relatively no so hard-working server
is
> something strange.
> Any help/tips will be highly appreciated!!!
> Thanks in advance,
> Barak
>
|||Chief Tenaya is correct and gave you some good references. You should also
take a look at 321363 INF: SQL Server Memory Usage
http://support.microsoft.com/?id=321363.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.