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 operating system 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. It is even more important to set this correctly if you have “Lock Pages in Memory” enabled for the SQL Server service account (which I always do for x64 systems with more than 4GB of memory).

These settings are for x64, on a dedicated database server, only running the DB engine, (which is the ideal situation).

Physical RAM                        MaxServerMem Setting
2GB                                           1500
4GB                                           3200
6GB                                           4800
8GB                                           6400
12GB                                         10000
16GB                                         13500
24GB                                         21500
32GB                                         29000
48GB                                         44000
64GB                                         60000
72GB                                         68000
96GB                                         92000
128GB                                       124000

If you are running other SQL Server components, such as SSIS or Full Text Search, you will want to allocate less memory for the SQL Server Buffer Pool. You also want to pay close attention to how much memory is still available in Task Manager. 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 use T-SQL to set your MaxServerMemory setting. The sample below sets it to 3500, which is the equivalent of 3.5GB. This setting is dynamic in SQL Server 2005/2008, which means that you can change it and it goes into effect immediately, without restarting SQL Server.

– Turn on advanced options
EXEC  sp_configure‘Show Advanced Options’,1;
GO
RECONFIGURE
;
GO

– Set max server memory = 3500MB for the server
EXEC  sp_configure‘max server memory (MB)’,3500;
GO
RECONFIGURE
;
GO

-- See what the current values are
EXEC sp_configure;

You can also change this setting in the SSMS GUI, as you see below:

Finally, I have learned that it is a good idea to temporarily adjust your MaxServerMemory setting downward by a few GB if you know you will be doing a large file copy on your database server (such as copying a large database backup file).

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

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

  1. Vic says:

    thanks for this post, good info. What would be the ideal Max Server Memory Setting for SQL Server 2005 Standard 32bit SP2, running on Windows Server 2003 R2 Standard 32bit with 4gb of physical RAM.

  2. John says:

    An excellent post that clearly explains a configuration setting that is often the source of much confusion.

  3. Scott says:

    How about if you\’re running multiple database instances on the same server?

  4. Glenn says:

    If you are running multiple database instances on a database server, you would want to set your MaxServerMemory for each instance according to its workload and priority, keeping in mind how much total RAM is available on the server. Personally, I try to avoid having named instances on a Production server, but sometimes you are forced to do that.

  5. Glenn says:

    On a 32-bit server with 4GB of RAM, I would consider using the /3GB switch in the boot.ini file, and setting MaxServerMemory to something like 2800-3200.

  6. wasim says:

    What would be the ideal Max Server Memory Setting for SQL Server 2008 Standard 32bit SP1, running on Windows Server 2008 R2 enterprise 64bit with 24gb of physical RAM.

  7. Chris says:

    Thanks for explaining this. Can you suggest a good way to monitor the performance of the server before and after making this change? I\’ve seen some articles discussing using perfmon. Would this be the best way? Thanks!

  8. Carla says:

    Thanks for this great article. How did you determine these values for the MaxServerMem setting? Are you using some sort of multiplier?

    • Glenn Berry says:

      The original settings came from a blog post from Slava Oks (who used to work on the SQL Server Database Engine Team), along with my experience at NewsGator. The idea is to have roughly 2-4GB of RAM free after your database server has been under a load for a while. The setting is dynamic, so it is pretty easy to experiment with.

  9. Chris Taylor says:

    What is your opinion on setting the “min server memory (MB)”?

  10. Doug Thomas says:

    What about if you are running SSIS on the same “dedicated” SQL Server entry? Let’s say the server has 64 GB of RAM is on Windows 2008 R2 and SQL Server 2008 SP2 CU 5 and x64. We currently have ours set to 56 GB and have thought about adjusting it lower for our ETL/SSIS packages to have more memory to consume.

  11. Traci Dukes says:

    I’m having a hard time finding recommended setting for Windows Server 2008 32-bit.
    Does anyone know where I can look or can anyone provide recommended values.
    Thanks Traci

  12. Rui-Gabriel says:

    Made my day!! I owe you a beer next time your in mozambique!! thanks alot! this is pretty decriptive and concise.

  13. Joe Kafri says:

    I have a Server w/32GB RAM running Windows Enterprise Server 32 bit and SQL 2008 standard, is there a way to have SQL recognize more then 4GB?, accoding to Microsoft it supposed to use the OS limit but for some reason it will not recognize more then 4GB. Thanks in advance.

    • Glenn Berry says:

      Which version of Windows Server are you running? On older versions (such as Windows Server 2003 and 2003 R2), you are going to have to edit the boot.ini file to add the /pae switch and then enabe the AWE setting in SQL Server in order to let the OS and SQL Server use more than 4GB of RAM. Even if you do this, only the SQL Server buffer pool can use that extra RAM (which is better than nothing), but you will still run into 32-bit limits for other parts of SQL Server 2008. You are much better off running the 64-bit versions of the OS and of SQL Server 2008.

  14. Pingback: Monday Morning Mistakes: Setting Memory Limits | The SQL UPDATE Statement

  15. JayKant Shikrey says:

    I was reading your article and I would like to appreciate you for making it very simple and understandable. This article gives me a basic idea of Configuring SQL Server Memory Options and it will help me a lot. Check this link too its also having nice post with wonderful explanation on Configuring SQL Server Memory…..
    http://mindstick.com/Blog/242/How%20to%20configuring%20SQL%20Server%20memory%20settings

    Thanks Everyone for your precious post!!

  16. Pingback: 6 Little Known Things That Can Cause Big Performance Issues | SQLRockstar | Thomas LaRock

  17. “which can cause instability and performance problems”- what exactly is those performance problems? High IO i guess? Or it could also be possible that this memory setting causes high CPU usage at some points of time?

    • Glenn Berry says:

      If the OS is low enough on memory, it will set a flag that is supposed to signal to applications that they should try to release some memory. If this happens to SQL Server, it can have a bad effect on performance (when some of its working set is paged out of memory). If the OS gets critically low on memory, it might cause the entire OS to become unstable and perhaps even lock up or freeze.

  18. Thanks for the great article.
    Just wanted to add that if your instance is part of a cluster, you need to make sure that there is enough memory for situations where all the instances are running on the same box otherwise you may have problem failing over.

  19. Sudhir says:

    512 Gb ram capacity of my production server.Max and memory should be for best performance

  20. Geoff Turner says:

    Glenn, thanks for the great article. What would you recommend the maximum memory setting be for SQL Server where IIS 7 is also installed and running in the same box? The server has 65 GB of RAM and Windows Server 2008 is the OS. Thanks!

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