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 6000072GB 6800096GB 92000128GB 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 optionsEXEC sp_configure'Show Advanced Options',1;GORECONFIGURE;GO-- Set max server memory = 3500MB for the serverEXEC sp_configure'max server memory (MB)',3500;GORECONFIGURE;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).
Nice info about Memory configuration.
Great info. I've never seen a great chart like this. Do you have a reference for picking those numbers?
Also, I thought on x86, if you go AWE, the Task manager doesn't necessarily show the correct RAM usage. Is that correct?
The chart about how much RAM should be available in Task Manager came from Slava Ok's old blog:
blogs.msdn.com/.../q-a-does-sql-server-always-respond-to-memory-pressure.aspx
The rest of the numbers come from my experience at NewsGator, with SQL 2005 and 2008.
You are right that if you use AWE on x86 or if you use "Lock Pages in Memory" with x64, the SQL Server Process does not report its true memory usage in Task Manager. That is why I like to look at how much is still available in Task Manager.
Pingback from My Weekly Bookmarks for October 30th | Brent Ozar - SQL Server DBA
links for 2009-10-31
Good post. From bitter experience with a 12G x64 system, NOT setting this value will kill the system. We complained to Microsoft because in our opinion SQL Server should work with default settings, but it does not. At least they did not charge for the support call.
The need to set the Max Server Memory has apparently changed when running on Windows 2008, due to improvements in the memory manager.
support.microsoft.com/.../918483
We're giving this a try, will be interesting to see the result.
Though theoretically, at least it does not require a restart on 2005, our server got unstable (failing several dbs over to the mirror) and I had to restart the service to clean things up.
(percentage wise we did not make a big change in the memory allocation)
Hi Mike,
I know that Microsoft sometimes says that you don't need to set MaxServerMemory when you are running on Windows Server 2008, but most DBAs that I know still do it. If nothing else, it is a "belt and suspenders" kind of thing.
Excellent
In your article you show a table with suggested MaxServerMem Setting for x64 servers... what about SQL2005 on x86 servers?
Thank you!
I'm with flavio - I'd like to know about SQL2005 on x86 servers as well.
Thanks!
Superb article.
It is extremely informative while being short.
Congratulations!
Very informative, I have been using similar settings myself. At least now I don't look like I am pulling numbers from the air.
Thanks.
Adam
Very good list. I would like to save it within SSC, but seems no "briefcase" available here.
The good thing is that "Lock Pages in Memory" is now available on Standard editions!
extended64.com/.../lock-pages-in-memory-now-available-for-64-bit-standard-edition-of-sql-server.aspx
Though on Microsoft's site it always says... "Lock Pages in memory" is not requried on 64-bit systems.. what do they mean by not required? Just that you don't have to set it? Is it required on any OS version etc?
Thanks
Steve
I would like to know about SQL2005 and 2008 on x86 servers as well.
Please give a chart.
Jefy
How about 2000? (Yes, some of us are still on that...) I am running on a home-build 64bit with 8 Gb of RAM. Shoud I use the 6400 setting?
Great article with sound advice.
Apart from knowing the recommendations for x86 usage I'd be interested to know how you go about configuring this setting in virtual machines. The problem being that the memory in the VM grows to accommodate the applications running in that VM.
Would you base the setting on a maximum available to the VM e.g. in VMware would you use the maximum recommended or the maximum for best performance?
Or do you monitor the memory usage and then base the setting on the peak usage?
Cheers
David
I am going to write another post that covers x86, but here are a few quick thoughts:
First running x86 really complicates the situation. By default, x86 Windows Server 2003/2008 will only be able to access 4GB of RAM of which x86 SQL Server 2000/2005/2008 will only access 2GB of RAM (while the OS gets 2GB of RAM)
If you add the /3GB switch to the boot.ini file, then SQL Server can access 3GB (actually about 2.75GB because of MemToLeave), and the OS gets 1GB.
If you have more than 4GB of RAM, you can add the /pae switch to boot.ini, and then enable "Lock pages in memory" to enable AWE. AWE lets you access up to 64GB (less 2GB for the OS), but it can only use that extra memory for data (not for other things like procedure caches).
The moral of this is that x86 really limits your ability to use a lot of RAM compared to x64.
The question on whether to enable "Lock pages in memory" on x64 is hotly debated by some people in the community. My current take on it is this:
It was definitely needed on Windows Server 2003 RTM, running SQL Server 2005. There were some bugs in Windows Server 2003, and there were issues with third party applications and device drivers that made it a very good idea to "Lock pages" in many situations.
Windows Server 2008 and Windows Server 2008 R2 seem less vulnerable to the issues that caused SQL Server to be paged out. If you do enable "Lock pages in memory", make sure you set MaxServerMemory to an appropriate value so that the OS is not starved for memory.
Agree, very concise article, thanks.
Can i ask, why is it a good idea to temporarily adjust your MaxServerMemory setting downward by a few GB for a large file copy on your database server?
Does the file copy consume a lot of RAM?
Last night, my sql server 2005 x64 standard on windows 2003 sp 2 x64 (4 quad cores, 32GB RAM) started paging out its process memory and then reduced its buffer pool from about 25GB to about 15GB. There was loads of physical RAM still available, so i do not understand why the paging.
Except .... signficant network activty at around this time. 16MB/sec out of the blue. I have no idea what this was. But does it sound consistent with what you have seen in your network copies?
Subsequently, i am not sure if it was the paging itself, or the running out of the 4GB page file space, which caused the dropping of memory from buffer pool.
Anyway, sorry, probably one for a separate post, but am interested in your experiences leading to your article's final comment.Thanks.
Good article - we've just gone through our first implementation using lock pages & this helps verify what we've done in a succint manner & adds a practical element missed from other articles I read.
Just a very little addition: when activated PAE + lock pages in memory + AWE, on Windows Task Manager you could see the "sqlservr.exe" process using very little memory (about 100Mb). This is because the task manager does NOT see the memory used in AWE mode! To see the real memory used by sql server it is enough to execute (on master db):
select * from dbo.sysperfinfo where counter_name like '%Total Server Memory (KB)%'
Bye!
Flavio.
Pingback from Christian Bolton on SQL Server memory | Brent Ozar - SQL Server DBA
this is very useful,
but i have a problem that i migrated my SQL Server to Win 2008 and SQl 2008, i noticed that SQL is exceeding the Max Server memory, i configured it to 100 GB and when i checked the Task manager i found that sqlserv.exe is consuming 107GB ......is this normal??
