SQL Server is designed to manage the memory on a server dynamically. That is, it will acquire memory as needed and it will release it when asked to (by OS). Its the good kid on the play ground, making sure now other processes on OS don’t starve. Unlike 32-bit (x86), where the SQL Server process was capped to 1.7GB. On 64-bit (x64) we do not have this limitation, as the virtual address space (VAS) for user applications is 8TB. Therefore for SQL Servers running on x64, I often recommend to set Max Memory to control the amount of memory each instance can use. Until SQL Server 2008 R2, the Max Memory stated the amount of memory assigned to Buffer Pool only. However starting SQL 2012, it is almost all the memory SQL can consume. (How memory architecture changed in SQL 2012 is beyond this post, maybe a future post?). However even with new SQL 2012, we still should reserve some memory for SQL threads and external components. So I am still using similar formula for all versions until I have reason to change it.
* Please note all calculations are for x64 bit system.
Where …
Max Physical Memory = Total Physical Memory Available on Server. If you are calculating this for Clustered Environment, then I would use the Available Physical Memory from the Node with Least Amount of Memory.
Memory Reserved For OS = Really its not operating system I am referring to, any other applications running on server that is not SQL Server Engine. I have used the guidelines below for initial configuration. I will adjust this value for further monitoring and tuning. Namely I will monitor Memory\Available Mbytes and Page File\% Usage(_Total). On x64 I want Available Mbytes to be 1024MB or above and % Page File Usage <= 20%.
| Physical Ram | Ram Left for OS |
| 4096MB (4GB) | 1024MB (1GB) |
| 8192MB (8GB) | 2048MB (2GB) |
| 16384MB (16GB) | 3072MB (3GB) |
| 24576MB (24GB) | 4096MB (4GB) |
| 32768MB (32GB) | 5120MB (5GB) |
| 40960MB+ (40GB+) | 6144MB (6GB) |
Memory Reserved for SQL Server: This formula is borrowed from really SQL 2005/2008 days, but I am still using it in SQL 2012 with positive results.
Where ….
# of Max Threads: This is referring to maximum number of worker threads SQL Server can create. By default this value is “0”, meaning SQL calculates the maximum number based on number of CPUs as below. CPUs in this context referrers to all visible CPU to operating system. For example, if you have 2 Physical CPUs with 4 Physical Cores each and Hyper-Threading; for the purpose of this formula the # Of CPU = (2 * 4 * 2) = 16.
This formula by all mean not absolute, please feel free to adjust it to your needs and environment. After you configure SQL Server with Max Memory as listed above monitor your Available Memory (Memory\Available MBytes), Page File (Page File\% Usage (_Total), SQL Server Memory Pressure (SQLServer:Buffer Manager\Page Life Expectancy) and adjust the memory you reserve for the OS and SQLOS.
Lets work through an example …
- Operating System: Windows 2008 R2 (x64)
- # of Physical Sockets (CPUS): 4
- # of Physical Cores per CPU: 6
- Hyper-Threading Enabled: Yes
- Number of SQL Server Instances: 2
- Physical Ram Available: 16GB (16384MB)
First Step: Calculate the maximum number of worker threads…
# of Max Threads = 512 + {[(4*6*2)-4)*16} = 1216
Second Step: Memory Reserved for each SQL OS
SQLOS = 1216 Threads * 2MB = 2432MB
Third Step: Memory Reserved for Operating System
Memory Reserved for OS = 3072MB (Based on my reference table above)
Forth Step: Total Memory Reserved for all Instances
Total Memory Reserved for SQLOS = 2 * 2432 = 4864MB
Final Step: Putting it All together
Max Memory Per Instance = [16384MB (Physical) – 3072MB (Reserved for OS) – 4864MB (Reserved for SQLOS)] / 2 (Number of Instances)
Therefore Max Memory Per Instance = 4224MB
WOW? Even though server has 16GB, we are leaving SQL server with only 4.125GB per instance. Please note this is extreme value, that is you have a SQL Server that is going to use all 1216 threads. Therefore if you monitor your SQL Server and actually see your Available Memory is ALWAYS above 1024MB, maybe you want to give more to your SQL instances. In this example lets suppose we know Instance 2 only requires 2048MB. Then we can assign 6400MB to instance #1 without working about performance.
I hope I explained this topic a bit, generally troubleshooting performance issues with Memory is lots of work. But I usually start here for dealing with memory related issues.
PS Please note I only use MB in my calculations, because we set our max memory in SQL Server to MB not GB or KB.
July 23, 2013 – Small update for calculating the # of CPU formula in # of Max Threads section.
SQL Canada (SQLCAN) 
Dear Mohit
How does your calculation differ from VM machines?
I would recommend same for VM machines.