SQL Server max server memory: setting it so the OS and buffer pool both survive

SQL Server is built to consume memory until something stops it. On a dedicated box with no cap, the engine will eat nearly all physical RAM for the buffer pool and keep going until the OS pushes back. That is not a leak; it is the design. max server memory is the one knob that turns that behavior into something safe to run next to other processes.

The default is 2147483647 MB, effectively unlimited. Many production instances run with that default because setup never forced a choice. On Windows, SQL Server 2019 and later setup offers a recommended value based on available system memory if you pick the recommended configuration path. On every other path, including in-place upgrades, the old default survives.

For the broader memory pressure mental model and failure catalogue, see how Microsoft SQL Server works in production.

What the cap controls

max server memory (MB) is a server-level configuration option (sp_configure) that sets an upper bound on most allocations SQL Server makes through its own memory manager. Since SQL Server 2012 consolidated single-page, multi-page, and CLR allocations into one page allocator, the cap governs:

• The buffer pool (the 8 KB data page cache, by far the largest consumer)
• The plan cache (compiled plan storage for ad-hoc and procedural workloads)
• Query execution memory grants (sort and hash workspace)
• Compile memory
• The lock manager
• CLR memory

It does not govern everything. Outside the cap but inside sqlservr.exe, you will find thread stacks, extended stored procedure DLLs, OLE DB providers used by linked servers, automation objects, and any allocation by non-SQL Server DLLs the engine has loaded. On x64 the per-thread stack is 2,048 KB, so on a host with thousands of max worker threads the stack footprint alone can be hundreds of MB. None of that counts against the cap.

The practical consequence: max server memory is not the same number as process size in Task Manager. It is a budget for the engine’s own allocations. A SQL Server process at 90 percent of physical RAM is normal if the cap was set correctly. It is a problem if the cap was never set.

How the cap is enforced

The engine treats max server memory as a soft ceiling for the clerks listed above. When the budget is reached, the memory broker asks consumers to shrink. The buffer pool is the largest and most elastic consumer, so most trimming happens there: the lazy writer evicts data pages until total committed memory drops below the cap. The plan cache is the next most elastic target and gets pruned under sustained pressure.

The cap is enforced cooperatively, not by the OS. That is why LPIM (Lock Pages in Memory) and the cap must be configured together. Grant LPIM to the service account but leave max server memory at the default, and SQL Server can lock enough physical pages to starve the OS. The OS cannot page the working set out to recover because the pages are locked. Microsoft explicitly recommends setting max server memory to a specific value whenever LPIM is enabled.

Conversely, set the cap but leave LPIM off, and the OS is free to page SQL Server’s working set out under its own memory pressure. The engine does not see this through its normal counters. The only signal is error 17890 in the SQL Server error log: “A significant part of sql server process memory has been paged out.” By the time you see it, throughput has already collapsed, and buffer pool hit ratio and PLE will not explain why.

flowchart TD
    RAM["Total physical RAM"] --> OS["OS + non-SQL processes (keep headroom)"]
    RAM --> SQL["SQL Server process"]
    SQL --> CAP["max server memory (configurable cap)"]
    SQL --> EXT["Outside cap: thread stacks, extended proc DLLs, OLE DB providers"]
    CAP --> BP["Buffer pool: target 70-85% of cap"]
    CAP --> PC["Plan cache: warn if above 10-15% of cap"]
    CAP --> MG["Memory grants, lock manager, CLR, compile memory"]

Where it shows up in production

Two failure modes dominate. Both come from the same setting going wrong in opposite directions.

Cap too high: OS starvation

When max server memory is set close to physical RAM, or left at the default on a host with other workloads, the OS eventually runs out of pages for its own file cache, process working sets, and kernel allocations. Symptoms:

• Available physical memory drops below a few hundred MB.
• sys.dm_os_sys_memory.system_memory_state_desc flips to Available physical memory is low.
• sys.dm_os_process_memory.process_physical_memory_low goes to 1, telling the engine to shrink.
• If LPIM is on, the engine cannot give pages back fast enough and the host becomes unresponsive.
• If LPIM is off, the OS pages the SQL Server working set out and you see error 17890.

On Linux the failure is sharper. SQL Server on Linux uses a separate memory.memorylimitmb setting via mssql-conf, which defaults to roughly 80 percent of physical RAM and is enforced as a hard cgroup ceiling. Leave the default and let the host run dry, and the kernel OOM killer selects a process to terminate. SQL Server is usually the largest process, so it is usually the target. On older builds of SQL Server 2017 in Docker, a known issue caused the engine to compute 80 percent of host memory instead of 80 percent of the container limit, so the container could be OOM-killed even with a small limit. KB4347055 documents the fix, which shipped in SQL Server 2017 CU10; afterwards SQL Server limits itself to 80 percent of the container’s allocated memory.

Cap too low: needless physical I/O

Set the cap too conservatively and the buffer pool never grows into the working set. Pages that would have been cached get evicted and re-read from disk. Symptoms:

• Page Life Expectancy is lower than the buffer-pool-size heuristic suggests.
• Buffer cache hit ratio drops below 99 percent on an OLTP workload that used to be above it.
• PAGEIOLATCH_* waits show up as a meaningful fraction of total wait time.
• Physical read IOPS on data files rises above baseline at the same batch request rate.

The degradation is gradual until the I/O subsystem saturates, at which point it becomes a memory pressure spiral.

Columnstore and in-memory OLTP over-commit

Even with a correct cap, large columnstore queries, batch mode operations, and index rebuilds can push total engine memory above max server memory temporarily. In-Memory OLTP tables have their own memory clerks and can consume memory outside the buffer pool portion of the cap. SQL Server 2022 enabled over-commitment cleanup by default, so the engine trims these clerks without intervention. Microsoft documents trace flag 8121 in SQL Server 2019 as speeding up this cleanup, and notes the flag has no effect on SQL Server 2022 and later.

Setting it: the headroom calculation

Microsoft’s current guidance is to set max server memory to 75 percent of available system memory not consumed by other processes. SQL Server 2022 setup applies that recommendation if you accept the recommended configuration. For a dedicated single-instance host with no other major workload, 75 percent of total physical RAM is a safe starting point.

The 75 percent rule does not scale linearly. On a 1 TB host, 250 GB for the OS is excessive. On a 16 GB host, 4 GB is tight if you also run an AG secondary, an SSIS package, or an agent job. A common community refinement for larger hosts: leave roughly 10 percent of physical RAM or 4 GB, whichever is greater, then subtract any memory reserved for other instances or major co-located processes.

Multi-instance hosts need the most care. Each instance has its own max server memory. Two instances each set to 75 percent of physical RAM leaves the OS nothing. Size each instance’s cap as a fraction of the host, sum the caps, leave OS headroom, and account for thread stacks and DLLs per instance.

Checklist for picking a value

• Start from physical RAM, not the current process size. Process size is a consequence of the current cap, not an input.
• Subtract co-located workloads first. Other SQL instances, SSAS, SSIS, antivirus, backup agents, monitoring agents.
• Leave OS headroom. 25 percent on small hosts, tapering toward 10 percent or 4 GB minimum on large hosts.
• Account for thread stacks and linked server DLLs. These live outside the cap but inside the process.
• Re-evaluate after edition or version changes. Standard edition has a buffer pool size limit. It was 128 GB through SQL Server 2022 and was raised to 256 GB in SQL Server 2025, per Microsoft’s compute capacity limits. Setting max server memory above the edition limit does not help the buffer pool.
• Pair every LPIM grant with a concrete cap. Never grant LPIM without also setting max server memory to a specific value.

Linux specifics

On Linux, memory.memorylimitmb and max server memory both apply. The mssql-conf setting is the hard cgroup ceiling for the process. The sp_configure setting is the engine’s internal budget and should be at or below memory.memorylimitmb so the engine self-limits before the cgroup does. If the engine self-limits first, you get graceful shrinkage. If the cgroup ceiling is hit first under burst, you risk OOM-kill. The default 80 percent for memory.memorylimitmb is reasonable for a dedicated single-instance host but should be lowered if other processes share the host or if you want explicit headroom for kernel page cache.

Verifying the setting

Confirm the engine actually lives within the cap and the internal allocation mix is healthy.

-- OS-level memory state and available headroom:
SELECT
    total_physical_memory_kb / 1024 AS total_physical_mb,
    available_physical_memory_kb / 1024 AS available_physical_mb,
    system_memory_state_desc
FROM sys.dm_os_sys_memory;

-- SQL Server process footprint and pressure flags:
SELECT
    physical_memory_in_use_kb / 1024 AS sql_physical_mb,
    locked_page_allocations_kb / 1024 AS locked_pages_mb,
    process_physical_memory_low,
    process_virtual_memory_low
FROM sys.dm_os_process_memory;

-- Current configured cap:
SELECT value_in_use AS max_server_memory_mb
FROM sys.configurations
WHERE name = 'max server memory (MB)';

The internal breakdown tells you whether the buffer pool is getting its share:

-- Top memory clerks by size:
SELECT
    type, name,
    pages_kb / 1024 AS size_mb
FROM sys.dm_os_memory_clerks
WHERE pages_kb > 0
ORDER BY pages_kb DESC;

Healthy patterns on a dedicated OLTP host:

• MEMORYCLERK_SQLBUFFERPOOL is the dominant clerk, sitting at 70 to 85 percent of the configured cap.
• CACHESTORE_SQLCP (ad-hoc and prepared SQL plan cache) is well under 10 to 15 percent of the cap. Above that range, suspect plan cache pollution from non-parameterized ad-hoc queries, not a memory cap problem.
• system_memory_state_desc reads Available physical memory is high or Physical memory usage is steady under normal load.
• process_physical_memory_low stays at 0. A value of 1 means the OS has signalled pressure and the engine is shrinking.
• available_physical_memory_mb stays above a few hundred MB on a dedicated host.

On Linux, also check that the engine’s cap is below the cgroup ceiling:

# Show the configured process ceiling:
sudo /opt/mssql/bin/mssql-conf get memory.memorylimitmb

LPIM status matters for interpreting the numbers. On SQL Server 2019 and later, LPIM is granted via Windows Local Security Policy or Group Policy, not through setup. Confirm the memory model in effect:

SELECT sql_memory_model_desc
FROM sys.dm_os_sys_info;

LOCK_PAGES means LPIM is active and the working set cannot be paged. CONVENTIONAL means it is not, and error 17890 is a real risk under OS pressure.

Signals to watch in production

How Netdata helps

• Correlate SQL Server memory clerks with host-level available_memory and process_physical_memory_low on the same timeline, so you can tell whether a PLE drop is caused by an undersized cap or OS page reclaim.
• Surface per-NUMA-node PLE alongside the instance-wide value, exposing imbalances the aggregate buffer manager counter hides.
• Track system_memory_state_desc and process_physical_memory_low as discrete states, making OS pressure episodes visible as events instead of buried in a line chart.
• Watch memory grants pending next to buffer pool clerk size. A growing grant queue with a healthy buffer pool points to query workspace pressure, not a cap problem.
• Alert on error 17890 and Linux OOM-kill events involving the SQL Server process.
• Trend the plan cache clerk as a fraction of the cap over time, catching plan cache pollution before it is misdiagnosed as a memory shortage.

Microsoft SQL Server monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• SQL Server user connections climbing: connection pool leaks and retry storms
• SQL Server CPU utilization high: telling query load apart from a bad plan
• SQL Server CXPACKET and CXCONSUMER waits: parallelism, MAXDOP, and what is actually wrong
• SQL Server Error 9002: the transaction log for the database is full
• SQL Server high compilations per second: plan cache pollution and CPU burn
• How Microsoft SQL Server actually works in production: a mental model for operators
• SQL Server log autogrow stall: why every write pauses while the log file grows
• SQL Server log backups missing: the full-recovery log that grows forever
• SQL Server log_reuse_wait_desc: why the transaction log will not truncate
• SQL Server transaction log percent used climbing toward full
• Microsoft SQL Server monitoring checklist: the signals every production instance needs
• Microsoft SQL Server monitoring maturity model: from survival to expert