How to calculate Max Memory for SQL Server?

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.

Max Memory - Main Formula

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.

Max Memory - Memory Reserved for SQL OS

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.

Max Memory - Max Number of Worker Threads

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.


Leave a Reply

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

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.