Sqlservr.exe memory usage issues

Posted on 2009-02-10
Medium Priority
Last Modified: 2012-08-13
I am running SQL Server 2005 on windows 2000 server machine with 16gb of ram. What would be my most efficient memory settings because I am getting serious sqlservr.exe memory spikes. Dont know if this will resolve the problem but I want to make sure I cover my back as far as my settings...
Question by:healthcheckinc
  • 3
  • 2
LVL 20

Expert Comment

by:Marten Rune
ID: 23605247
'With Windows 2000 Professional and Server, the maximum amount of memory that can be supported is 4 GB'
'Windows 2000 Advanced Server supports 8 GB of physical RAM and Windows 2000 Datacenter Server supports 32 GB of physical RAM'
Link: http://www.microsoft.com/whdc/system/platform/server/PAE/PAEmem.mspx

These are OS limitations. I guess you need to upgrade the OS to W2k3 R2 to use all unless you have the w2000 datacenter edition (not sold seperately).

LVL 20

Expert Comment

by:Marten Rune
ID: 23605281
When upgrading OS, choose the enterprice ed. Quote from link above:
'Windows Server 2003, Enterprise Edition supports 32 GB of physical Memory'


Author Comment

ID: 23605328
I guess I should have included that it is a Windows 2003 R2 Enterprise Edition SP2....Sorry about that...Knowing that what would be your recommendations on Max - Min memory settings
LVL 20

Accepted Solution

Marten Rune earned 1500 total points
ID: 23607887
Still not all the information about the OS. But ok.
32bit system
If you have less than 4GB, use the /3GB switch (boot.ini).
Between 4GB and up to (including) 16GB, use /3GB switch and /PAE switch (boot.ini) and the AWE (SQL Server) in SQL Server.
More than 16GB, use /PAE switch (boot.ini) and the AWE (SQL Server) in SQL.

64 bit system
No special configuration required.

If you are on a 64 bit system, congrats, otherwise consider a upgrade. The SQL handles the memory dynamically. It's recommended to begin with default settings. You can set a minimum and maximum threshold. The minimum I would recommend you to set from the beginning.

If your system is only for sql I would consider setting the max to 14GB, always leaving 2GB for the OS. It's a matter of opinion.

If you use the enterprise edition, be sure to configure the 'lock pages in memory' option. This prevents the OS from stealing back memory for OS operations. If SQL is your sole purpose with this server, you will be better off with pages locked to the SQL process. The OS is ok with 2GB.

Then monitor your server, if you have huge amounts of connections, maybe the OS needs a small boost, then leave 3 or 4 GB to the OS. This you will find out by tuning and monitoring performance.


Author Closing Comment

ID: 31545260

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question