SQL Server Max Server Memory Calculator Script

calendar May 16, 2026 / · 8 min read · sql server max server memory sys.dm_os_sys_info sys.configurations sp configure memory configuration database administration sql scripts performance sys.dm_os_performance_counters  ·
Share on: linkedin copy

Every SQL Server installation ships with max server memory set to its default value of 2,147,483,647 MB — effectively unlimited — which allows the Buffer Pool to consume all available RAM and starve the operating system of the memory it needs for network stack, disk I/O, and other critical processes. This script reads total physical RAM from sys.dm_os_sys_info, applies Jonathan Kehayias's widely-adopted OS reservation formula, and compares the recommended cap to the current sp_configure setting so DBAs can see at a glance whether action is needed.

Purpose and Overview

SQL Server's Buffer Pool is its primary memory consumer. When max server memory is left at the default unlimited value, the Buffer Pool expands to fill available RAM as query workloads grow — and it does not release that memory on demand. The operating system begins paging other processes to disk, causing latency spikes that affect not just other applications on the server but SQL Server itself, because its own threads and network buffers compete with the Buffer Pool for physical pages. On servers running SSRS, SSIS, or IIS alongside SQL Server, the problem is more acute.

Jonathan Kehayias of SQLskills.com published the industry-standard formula for calculating how much memory to reserve for the OS: 1 GB base plus 1 GB for every 4 GB of RAM above the first 4 GB, capped at 4 GB total OS reserve. This formula is adapted and distributed in the ktaranov/sqlserver-kit community repository. This script implements the Kehayias formula against the current server's physical RAM, reads the current max server memory configuration, and produces an Assessment column and a ready-to-run sp_configure command for applying the recommended setting.

Output columns:

  • TotalPhysicalRAM_MB — total RAM detected by SQL Server via the OS
  • RecommendedOSReserve_MB — how much to leave for the OS per the Kehayias formula
  • RecommendedMaxServerMemory_MB — recommended max server memory value
  • CurrentMaxServerMemory_MB — the currently configured value from sys.configurations
  • Assessment — one of four states: unlimited default, over-configured, under-configured, or OK
  • ApplyScript — a generated sp_configure + RECONFIGURE statement to copy and run

Code Breakdown

The script reads physical RAM, calculates the OS reserve, reads the current configuration, and produces both an assessment and a ready-to-apply command.

 1-- SQL Server Max Server Memory Calculator
 2-- Source: adapted from ktaranov/sqlserver-kit and Jonathan Kehayias formula (sqlskills.com)
 3-- Calculates recommended max server memory and compares to current setting
 4-- Requires: VIEW SERVER STATE
 5
 6SET NOCOUNT ON;
 7
 8DECLARE @TotalRAM_MB         BIGINT;
 9DECLARE @OSReserve_MB        BIGINT;
10DECLARE @RecommendedMax_MB   BIGINT;
11DECLARE @CurrentMax_MB       BIGINT;
12
13-- Read total physical RAM from the OS
14SELECT @TotalRAM_MB = physical_memory_kb / 1024
15FROM sys.dm_os_sys_info;
16
17-- Jonathan Kehayias formula: reserve OS memory based on total RAM
18-- Base: 1 GB for the OS + 1 GB for every 4 GB of RAM above 4 GB
19SET @OSReserve_MB =
20    1024                                              -- 1 GB base for OS
21    + (
22        CASE
23            WHEN @TotalRAM_MB > 4096
24            THEN ((@TotalRAM_MB - 4096) / 4096) * 1024
25            ELSE 0
26        END
27      );
28
29-- Cap OS reserve at 4 GB for very large RAM servers
30IF @OSReserve_MB > 4096
31    SET @OSReserve_MB = 4096;
32
33SET @RecommendedMax_MB = @TotalRAM_MB - @OSReserve_MB;
34
35-- Read current setting
36SELECT @CurrentMax_MB = CAST(value_in_use AS BIGINT)
37FROM sys.configurations
38WHERE name = 'max server memory (MB)';
39
40SELECT
41    @TotalRAM_MB                                       AS TotalPhysicalRAM_MB,
42    @OSReserve_MB                                      AS RecommendedOSReserve_MB,
43    @RecommendedMax_MB                                 AS RecommendedMaxServerMemory_MB,
44    @CurrentMax_MB                                     AS CurrentMaxServerMemory_MB,
45    CASE
46        WHEN @CurrentMax_MB >= 2147483647
47            THEN 'WARNING: max server memory is at default (unlimited) — set it explicitly'
48        WHEN @CurrentMax_MB > @RecommendedMax_MB
49            THEN 'WARNING: current setting exceeds recommended maximum'
50        WHEN @CurrentMax_MB < @RecommendedMax_MB * 0.75
51            THEN 'UNDER-CONFIGURED: current setting is less than 75% of recommended'
52        ELSE 'OK: current setting is within recommended range'
53    END                                                AS Assessment;
54
55-- Show how to apply the recommended setting (copy and run separately)
56SELECT
57    'EXEC sp_configure ''max server memory (MB)'', '
58    + CAST(@RecommendedMax_MB AS NVARCHAR(20))
59    + '; RECONFIGURE;'                                 AS ApplyScript;

Reading Total RAM from sys.dm_os_sys_info

sys.dm_os_sys_info is a single-row DMV that exposes OS-level information visible to SQL Server, including total physical memory. The physical_memory_kb column returns total RAM in kilobytes; dividing by 1,024 converts to megabytes. This value reflects the physical RAM installed in the server as reported by the OS — it does not change with workload and does not require VIEW SERVER STATE on SQL Server 2019 and later (the permission requirement was relaxed). On older versions, VIEW SERVER STATE is required.

Jonathan Kehayias OS Reserve Formula

The formula reserves 1 GB as a base for OS overhead, then adds 1 GB for every 4 GB of RAM above the first 4 GB. The integer division ((@TotalRAM_MB - 4096) / 4096) * 1024 implements this in steps of 4 GB using T-SQL integer arithmetic, which truncates the remainder rather than rounding. The result is capped at 4 GB total OS reserve: on servers with 256 GB or more of RAM, dedicating more than 4 GB to the OS is generally unnecessary for a dedicated SQL Server host. For servers running multiple workloads alongside SQL Server (SSRS, IIS, custom services), increase the OS reserve manually by subtracting additional memory from @RecommendedMax_MB before applying.

sys.configurations — Reading the Current Setting

sys.configurations contains one row per SQL Server configuration option. The value column stores the persisted setting (saved to disk), while value_in_use reflects what is currently active in the running instance. When max server memory has been changed but RECONFIGURE has not been run, value and value_in_use will differ — value_in_use is the correct column for the running state. The default value of 2,147,483,647 maps to INT max value and means unlimited; any result at or above this threshold triggers the unlimited-default warning.

Assessment and ApplyScript

The Assessment CASE covers four states in priority order: the unlimited default (highest priority — always warn regardless of RAM), over-configured (current > recommended, meaning SQL Server may be taking more than it should), under-configured (current < 75% of recommended, meaning SQL Server is unnecessarily constrained and may spill to disk prematurely), and OK (within range). The ApplyScript column generates the exact sp_configure command with the recommended value. It is output as a separate SELECT so the DBA must consciously copy and execute it — the script does not auto-apply any changes.

Key Benefits and Use Cases

  • Detects the unlimited-default anti-pattern — the single most common SQL Server memory misconfiguration is leaving max server memory at the factory default; this script flags it immediately
  • Formula-based recommendation — removes guesswork by applying the Kehayias formula to the server's actual RAM rather than relying on memory of the correct values
  • Current vs recommended comparison — shows both numbers in context so over-constrained and over-allocated servers are immediately visible
  • Ready-to-apply command — the ApplyScript output eliminates transcription errors when applying the setting
  • Repeatable audit — run after hardware changes, VM resizing, or memory additions to verify the setting is still appropriate
  • Multi-workload adjustment baseline — the OS reserve cap is a starting point; the script makes it easy to recalculate with a higher reserve for shared hosts

Performance Considerations

  • sys.dm_os_sys_info is a single lightweight DMV call — the query executes in milliseconds regardless of instance size or workload
  • max server memory does not govern all SQL Server memory — thread stacks, linked server memory, CLR, and extended stored procedures consume memory outside the Buffer Pool and outside this cap; on instances using these features, the effective memory ceiling is lower than max server memory alone
  • RECONFIGURE is online — changing max server memory via sp_configure + RECONFIGURE takes effect immediately without a service restart; the Buffer Pool shrinks or expands dynamically
  • RECONFIGURE WITH OVERRIDE is not required — max server memory is not an advanced option requiring override; standard RECONFIGURE is sufficient
  • The 75% under-configured threshold is conservative — some workloads intentionally cap SQL Server well below the formula recommendation to reserve memory for in-memory OLAP tools or OS disk cache; treat the UNDER-CONFIGURED assessment as a conversation starter, not an absolute directive

Practical Tips

  • Always set max server memory explicitly on every SQL Server instance — there is no scenario where leaving it at the unlimited default is correct on a production server
  • On shared hosts, increase OS reserve — add the memory requirements of each co-resident service to @OSReserve_MB before calculating @RecommendedMax_MB; for example, add 2 GB if SSRS runs on the same host
  • Monitor Memory Grants Pending after adjusting — query sys.dm_os_performance_counters WHERE counter_name = 'Memory Grants Pending' before and after the change; a spike indicates the new cap is too low for the current query workload
  • Use sys.dm_os_memory_clerks to understand Buffer Pool consumption — if SQL Server is not consuming its allowed maximum, the constraint may be elsewhere; SELECT type, SUM(pages_kb)/1024 AS MB FROM sys.dm_os_memory_clerks GROUP BY type ORDER BY MB DESC shows the breakdown
  • For Always On Availability Groups, size memory on all replicas — the secondary replica runs the same workload during failover; under-sizing memory on the secondary causes a performance cliff immediately after failover

Conclusion

This script from the ktaranov/sqlserver-kit community repository implements Jonathan Kehayias's OS reservation formula against the current server's physical RAM to produce an actionable max server memory recommendation. Combined with the current configuration value from sys.configurations, it gives DBAs a one-query answer to whether the most common SQL Server memory misconfiguration — the unlimited default — is present on their instance, and a ready-to-run command to correct it.

References

Posts in this series