Min and Max Server Memory

Min Server Memory (MB) and Max Server Memory (MB) control the allowable size of all SQL Server’s memory usage. As its name suggests, Min Server Memory controls the minimum amount of physical memory that SQL Server will try to keep committed. We say “try” because it can fall under that value if Windows is desperate enough, but to all intents and purposes it sets a floor for SQL Server’s memory usage. When the SQL Server service starts, it does not acquire all the memory configured in Min Server Memory but instead starts with only the minimum required, growing as necessary. Once memory usage has increased beyond the Min Server Memory setting, SQL Server won’t release any memory below that amount.  

Not surprisingly, Max Server Memory is the opposite of Min Server Memory, setting a ceiling for memory usage. Both values can be set using sp_configure or through Management Studio on the Memory page of the SQL Server Properties window. Configuring a maximum value for the buffer pool is the more important of the two settings and will prevent SQL Server from taking too much memory. This is particularly significant on 64-bit systems, where a lack of free physical memory can cause Windows to trim SQL Server’s working set. There are several different ways to calculate an appropriate value for configuring Max Server Memory, but two of the most straightforward are as follows:  

➤ Look at SQL Server’s maximum usage.  

➤ Determine the maximum potential for memory requirements outside SQL Server 

 

Looking at the SQL Server’s Maximum Usage With this method, you set SQL Server to dynamically manage memory and then monitor the MSSQL$:Memory Manager\Total Server Memory (KB) counter using Performance Monitor. This counter measures SQL Server’s total buffer pool usage.  

The Total Server Memory value will decrease if other requirements outside SQL Server need more physical memory than is currently free, and then increase again to use any free memory. If you monitor this counter for a period of time that is representative for your business (i.e., it includes busy and slack periods), you can then set Max Server Memory to the lowest value that was observed for Total Server Memory (KB), and you won’t have to worry about SQL Server having to shrink its usage during normal operations. 

 

Determining the Maximum Potential for Requirements Outside SQL Server This option is the most popular, as the aim is to calculate the worst-case scenario for memory requirements other than SQL Server’s. You should allow for the following:  

➤ 2GB for Windows  

➤ xGB for SQL Server worker threads. You can find your max workers count by querying sys.dm_os_sys_info. Each thread will use 0.5MB on x86, and 2MB on x64.  

➤ 512MB, if you use linked servers, extended stored procedure dlls, or objects created using Automation procedures (sp_OA calls)  

➤ 1–3GB, for other applications that might be running on the system, such as backup programs or anti-virus software For example, on a server with eight CPU cores and 64GB of RAM running SQL Server 2012, a third-party backup utility, and virus checker, you would allow for the following:  

➤ 2GB for Windows  

➤ 1GB for worker threads (576 3 2MB rounded down)  

➤ 512MB for linked servers, etc.  

➤ 1GB for the backup program and virus checker For a total of 4.5GB, you would configure Max Server Memory to 59.5GB. Both of these options can be valid in different circumstances. On a single SQL Server from which you need to squeeze every drop of performance, you might use option 1 and monitor Total Server Memory to see how often SQL Server has to give memory back to Windows. However, if you had dozens of SQL Servers to manage or a mission-critical server, you might go with option 2, as it would be easier to calculate across multiple servers and is less likely to cause a failure under exceptional circumstances.