Sqlservr.exe memory usage issues

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...
Who is Participating?
Marten RuneConnect With a Mentor SQL Expert/Infrastructure ArchitectCommented:
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.

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
'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).

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
When upgrading OS, choose the enterprice ed. Quote from link above:
'Windows Server 2003, Enterprise Edition supports 32 GB of physical Memory'

healthcheckincAuthor Commented:
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
healthcheckincAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.