We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Sqlservr.exe memory usage issues

healthcheckinc
on
Medium Priority
619 Views
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...
Comment
Watch Question

Marten RuneSQL Expert/Infrastructure Architect
CERTIFIED EXPERT

Commented:
Quote:
'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
Marten RuneSQL Expert/Infrastructure Architect
CERTIFIED EXPERT

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

/Marten

Author

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
SQL Expert/Infrastructure Architect
CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
thanks
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.