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
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;
— Set max server memory = 3500MB for the server
EXEC sp_configure‘max server memory (MB)’,3500;
-- 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).
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.
An excellent post that clearly explains a configuration setting that is often the source of much confusion.
How about if you\’re running multiple database instances on the same server?
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.
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.
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.
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!
Thanks for this great article. How did you determine these values for the MaxServerMem setting? Are you using some sort of multiplier?
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.
What is your opinion on setting the “min server memory (MB)”?
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.
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.
Made my day!! I owe you a beer next time your in mozambique!! thanks alot! this is pretty decriptive and concise.
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.
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.
Pingback: Monday Morning Mistakes: Setting Memory Limits | The SQL UPDATE Statement
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…..
Thanks Everyone for your precious post!!
Pingback: 6 Little Known Things That Can Cause Big Performance Issues | SQLRockstar | Thomas LaRock
“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?
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.
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.
512 Gb ram capacity of my production server.Max and memory should be for best performance
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!
I neglected to mention that it’s for SQL Server 2005.