SQL Server Max Server Memory: How to Calculate It, Set It and Check It

SQL Server is designed to use memory aggressively. Data pages that are already in the buffer pool don’t need to be read from disk again, so the more memory SQL Server has, the less I/O it needs. Left at its default, though, it will keep taking memory until Windows itself runs short, and then the operating system, backup agents, monitoring tools and even remote desktop sessions start fighting for what’s left.

max server memory is the setting that draws that line. This guide explains what the setting actually limits, how to calculate a sensible starting value, when to use min server memory and Lock Pages in Memory, how to handle virtual machines and multiple instances, and which metrics tell you whether the value is too high or too low.

Default Values and Why They Need Changing

The default for max server memory means “no limit”. On a server with 64 GB of RAM, SQL Server will gradually grow toward all 64 GB. It does respond to low-memory notifications from Windows and gives memory back, but that happens only after the operating system is already under pressure.

Since SQL Server 2019, setup on Windows recommends a max server memory value on the Memory page, based on the memory available at installation time. Many servers are still installed by clicking through that page, or were installed on older versions where the default stayed in place, so it’s worth checking on every instance:

SELECT name, value, value_in_use
FROM sys.configurations
WHERE name IN (N'max server memory (MB)', N'min server memory (MB)');

What max server memory Actually Limits

Since SQL Server 2012, max server memory governs most of SQL Server’s memory: the buffer pool, the plan cache, memory grants for sorts and hashes, the lock manager and most other memory clerks. It doesn’t cover everything the sqlservr.exe process uses. Outside the limit are:

• Thread stacks: about 2 MB for every worker thread on 64-bit systems.
• Extended stored procedures, COM objects and sp_OA automation.
• Linked server providers loaded into the process, such as OLE DB drivers for Oracle or ODBC sources.
• Backup buffers and other allocations made directly from Windows.

That’s why the SQL Server process can use more memory than max server memory in Task Manager, and why the setting must leave room for these allocations as well as for the operating system.

How to Calculate a Starting Value

Microsoft’s generic approach for a single instance on a dedicated server is:

1. Start from the total physical memory.
2. Subtract the memory for worker thread stacks: the thread stack size (2 MB on x64) multiplied by the calculated max worker threads.
3. Subtract 25% of the remainder for other allocations outside the limit and for the operating system.
4. What’s left is the starting value for max server memory.

For 64-bit SQL Server, the automatic max worker threads value is 512 for up to 4 logical processors, 512 + (logical processors − 4) × 16 for up to 64, and 512 + (logical processors − 4) × 32 above that.

The same calculation as a query, using the values of the server it runs on:

SELECT
    total_mb       = si.physical_memory_kb / 1024,
    max_workers    = si.max_workers_count,
    stacks_mb      = si.max_workers_count * 2,
    suggested_mb   = CAST(((si.physical_memory_kb / 1024) - si.max_workers_count * 2) * 0.75 AS int)
FROM sys.dm_os_sys_info AS si;

Changing max server memory

The change takes effect immediately and doesn’t require a restart:

EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure N'max server memory (MB)', 48000;
RECONFIGURE;

If you lower the value significantly, SQL Server releases memory gradually by trimming its caches, and you’ll see more disk reads until the working set settles. Make large reductions outside busy hours.

min server memory: When It Helps

min server memory isn’t allocated at startup. It only means that once SQL Server has grown past this value, it won’t release memory below it in response to pressure. It’s useful in two situations:

• Virtual machines: it stops the hypervisor’s memory reclamation (for example a balloon driver) from squeezing the buffer pool below what the workload needs.
• Several instances on one server: it guarantees each instance a floor, so a busy instance can’t starve a quiet one.

Don’t set min and max to the same or nearly the same value. SQL Server then loses the ability to adapt to memory pressure, which Microsoft explicitly advises against.

Lock Pages in Memory

Granting the SQL Server service account the Windows Lock pages in memory (LPIM) right lets SQL Server allocate memory that Windows can’t page out to disk. It’s available in Standard and Enterprise editions without trace flags. It’s most useful when Windows has been paging SQL Server’s memory, which shows up in the error log as error 17890:

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1250000, committed (KB): 30500000, memory utilization: 4%.

-- CONVENTIONAL = no LPIM, LOCK_PAGES = LPIM in use, LARGE_PAGES = LPIM with trace flag 834
SELECT sql_memory_model_desc FROM sys.dm_os_sys_info;

Virtual Machines

• Reserve the VM’s memory in the hypervisor, or at least don’t overcommit the host. A guest SQL Server can’t see that its “RAM” is being reclaimed by the host.
• Don’t use dynamic memory (Hyper-V Dynamic Memory or similar) for production SQL Server VMs. The buffer pool sizes itself to what it sees and reacts badly to memory that appears and disappears.
• Set min server memory to protect the buffer pool from balloon drivers, and size max server memory for the guest’s memory, not the host’s.
• Keep the VM’s NUMA layout aligned with the host; the same rules as for CPUs apply. See how many CPU cores SQL Server can use.

Multiple Instances on One Server

SQL Server makes no attempt to balance memory between instances. With default settings, whichever instance gets busy first takes most of the memory, and the others run starved. Give each instance its own max server memory, proportional to its workload, and make sure the total of all max values plus the operating system reserve fits in physical memory. Where instances must always keep a minimum, add min server memory as well.

Edition Limits

Standard edition limits the buffer pool per instance: 256 GB in SQL Server 2025, 128 GB in SQL Server 2016 to 2022. Columnstore segment cache and In-Memory OLTP data have their own separate limits (32 GB each in SQL Server 2025 Standard) on top of that. Express is limited to 1,410 MB of buffer pool. Adding memory beyond these limits doesn’t make the buffer pool bigger, but the extra memory still serves the operating system, other caches and other processes.

Is the Value Too High or Too Low?

-- Operating system view: is Windows short of memory?
SELECT total_physical_memory_kb / 1024     AS total_mb,
       available_physical_memory_kb / 1024 AS available_mb,
       system_memory_state_desc
FROM sys.dm_os_sys_memory;

-- SQL Server process view
SELECT physical_memory_in_use_kb / 1024 AS sql_in_use_mb,
       memory_utilization_percentage,
       process_physical_memory_low,
       process_virtual_memory_low
FROM sys.dm_os_process_memory;

-- Biggest memory consumers inside SQL Server
SELECT TOP (10) type, SUM(pages_kb) / 1024 AS mb
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY mb DESC;

Page life expectancy (PLE) shows how long a page stays in the buffer pool. There’s no single correct threshold, because it depends on memory size and workload; what matters is its trend. A PLE that drops sharply every time a certain job runs tells you which job reads more data than memory can hold.

Common Mistakes

• Leaving max server memory at the default on production servers.
• Treating SQL Server’s high memory use as a leak and restarting the service to “free” memory.
• Setting max server memory without subtracting other instances and services on the same server.
• Enabling Lock Pages in Memory without setting max server memory.
• Setting min and max server memory to the same value.
• Using dynamic memory for SQL Server VMs.
• Copying the old server’s value to a new server with different memory after a migration.

Memory Checklist

• max server memory is set on every instance and matches the current physical memory.
• Memory used by other instances and services has been subtracted.
• Available memory in Windows is monitored under peak load.
• min server memory is set on VMs and multi-instance servers.
• LPIM is used only together with a sized max server memory.
• VM memory is reserved, not dynamic.
• PLE, PAGEIOLATCH and RESOURCE_SEMAPHORE waits are tracked over time.