LLI Wiki — My Digital Knowledge Base

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:

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:

Notes

Mastodon LLIMastodon SocialFosstodon