sqlservr.exe memory usage

Hello All,

On all of my SQL servers (2000 with SP3), when I go to task manager and
look at memory usage, sqlservr.exe is always at 1.7gigs.  If I reduce
the maximum to let's say 1 gig it will go down to 1 gig.  But if it is
even at 2gigs or 3 gigs it will be showing 1.7 gigs.  Why?  Is is ok to
reduce the memory usage of sqlservr.exe?

Raziq.


*** Sent via Developersdex http://www.droptable.com ***

Raziq Shekha  (raziq_shekha@anadar
ko.com)  writes:
> On all of my SQL servers (2000 with SP3), when I go to task manager and
> look at memory usage, sqlservr.exe is always at 1.7gigs.  If I reduce
> the maximum to let's say 1 gig it will go down to 1 gig.  But if it is
> even at 2gigs or 3 gigs it will be showing 1.7 gigs.  Why?  Is is ok to
> reduce the memory usage of sqlservr.exe?

I'm not really sure what the question is, so I answer both.

1) Why does SQL Server take so much memory? SQL Server is designed to
grab as much memory it can. The more memory it has, the more data it
can have in cache, which is good for quick response times. If other
processes need memory, SQL Server will yield memory, but it may not
yield fast enough. Therefore, if other applications are running on
the machine, you may want to constrain how much memory SQL Server
may use. The typical example is when you run SQL Server on a
developer workstation. If SQL Server runs on a dedicated server, there
is no reason to set a max memory limit.

2) Why is SQL Server not using more than 1.7 GB? Most probably, you
running Standard Edition which does not support more than 2GB of
memory. If you want SQL Server you to use more memory, you need to
upgrade to Enterprise Edition. Or SQL 2005.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx