SQL Server: sizing max server memory from installed RAM
Sizing rule, reference values and performance counters to set max server memory on a standalone SQL Server instance.
On a standalone SQL Server instance, max server memory (MB) must leave enough RAM for the operating system and for SQL Server's own threads. This memo gives the sizing rule, a reference table and the counters to watch to validate the setting.
Sizing rule
The RAM to reserve (outside SQL Server) is:
- 1 GB for the operating system;
- plus 1 GB for each 4 GB of installed RAM between 4 and 16 GB;
- plus 1 GB for each 8 GB of installed RAM above 16 GB.
The "Max Server Memory Calculator" script by Randolph West (BornSQL, MIT license, based on an algorithm by Jonathan Kehayias) applies this rule. It also adds the worker thread stack (max_workers_count times 2 MB on 64-bit), accounts for the Standard edition memory limits (64 GB before SQL Server 2012, 128 GB afterwards) and for optional NUMA affinity. It reads RAM from sys.dm_os_sys_memory, the thread count from sys.dm_os_sys_info, and generates the sp_configure command to run. The full script and an online calculator are available at bornsql.ca.
Reference values (without the thread stack)
| Installed RAM | Max server memory |
|---|---|
| 4 GB | 2 GB |
| 8 GB | 5 GB |
| 16 GB | 11 GB |
| 32 GB | 25 GB |
| 64 GB | 53 GB |
| 128 GB | 109 GB |
| 256 GB | 221 GB |
| 512 GB | 445 GB |
| 1024 GB | 893 GB |
Applying the value
EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max server memory (MB)', 53248; -- example: 64 GB server
EXEC sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE;
Performance counters to watch
After the change, check in Performance Monitor:
SQL Server:Buffer Manager\Page life expectancySQL Server:Buffer Manager\Page reads/secPhysical Disk\Disk Reads/sec
Notes
- These values are a starting point for a standalone instance; on a server shared with other services or several instances, reserve more.
- The table is an excerpt of the calculator's table.