Suggested Max Memory Settings for SQL Server 2005/2008

It is pretty important to make sure you set the Max server memory setting for SQL Server 2005/2008 to something besides the default setting (which allows SQL Server to use as much memory as it wants, subject to signals from the host OS that it is under memory pressure). This is especially important with larger, busier systems that may be under memory pressure.  This setting controls how much memory can be used by the SQL Server Buffer Pool.  If you don’t set an upper limit for this value, other parts of SQL Server, and the operating system can be starved for memory, which can cause instability and performance problems.

 

This is for x64, on a dedicated DB server.

Physical RAM                        MaxMem Setting

2GB                                        1500

4GB                                        3200

6GB                                        4800

8GB                                        6700

12GB                                     10600

16GB                                     14500

24GB                                     22400

32GB                                     30000

48GB                                     45000

64GB                                     59000

 

This is how much RAM should be available in Task Manager while you are under load (on Windows Server 2003)

Physical RAM             Target Avail RAM in Task Manager

< 4GB                                   512MB – 1GB

4-32GB                                 1GB – 2GB

32-128GB                             2GB – 4GB

> 128GB                               > 4GB

 

You can set this value with Transact-SQL like this:

-- Turn on advanced options
EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO

-- See what the current value is for 'max server memory (MB)'
EXEC sp_configure

-- Set max server memory = 2300MB for the server
EXEC sp_configure 'max server memory (MB)', 2300
GO
RECONFIGURE
GO

 

Or you can set it with SQL Server Management Studio (SSMS)

 

About these ads
This entry was posted in SQL Server 2008. Bookmark the permalink.

5 Responses to Suggested Max Memory Settings for SQL Server 2005/2008

  1. scott says:

    I’m running sql 2005 on 2008 R2 DataCenter edition. It’s on a VM with 2 Procs and 4 GB of ram, and is x86. Do you have a x86 chart?

  2. Pingback: SQL Server Max Server Memory - Logical SQLLogical SQL

  3. Rodo says:

    Excellent post! works perfectly
    Regards!

  4. biju says:

    Glenn ,
    I think you have some formula or something to calculate these right if you have it please post it also so one can easily calculate the required memory to allocate

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s