SQL Server 2025 Memory Configuration on Small Windows Instances: max server memory and Lock Pages

SQL Server’s default memory behaviour is designed for a machine that does nothing else, and on a shared Windows instance that default is the fastest route to a swapping, unresponsive server. SQL Server 2025 will happily grow its buffer pool until the operating system starts paging, at which point IIS, RDP, and even the SQL Server worker threads contend for what remains. The fix is a specific number, not a checkbox, and the number depends on what else the box runs.

Why the Default 2,147,483,647 Is Not a Limit

max server memory (MB) defaults to an effectively unlimited value. It caps only the buffer pool, compiled plan cache, and related internal caches — not CLR memory, not extended stored procedures, not the thread stacks themselves. Setting it does not guarantee the process stays under the figure, so budget headroom rather than treating it as a hard ceiling.

Sizing Table for Common Instance Profiles

Total RAM	SQL-plus-IIS host	SQL-only host	OS reserve
8 GB	4,608 MB	5,632 MB	1,024 MB + IIS 2 GB
16 GB	9,216 MB	11,264 MB	1,536 MB + IIS 4 GB
32 GB	18,432 MB	23,552 MB	2,048 MB + IIS 8 GB
64 GB	36,864 MB	47,104 MB	4,096 MB + IIS 12 GB

The OS reserve grows with total RAM because Windows kernel structures, driver pools, and the file system cache scale with physical memory and connection counts, not with a fixed constant. On anything above 32 GB, a 10% reserve is the floor, not a target.

Lock Pages in Memory: When It Helps and When It Hurts

The Lock Pages in Memory right prevents SQL Server’s buffer pool from being paged to disk. On a dedicated host with plenty of RAM, it stabilises latency and is enabled by default for SQL Server on Linux. On a small Windows instance shared with other workloads it is a trade: locked pages cannot be reclaimed under pressure, so a memory-hungry mail filter or badly-behaved app pool will push the whole machine into a harder failure instead of degrading gracefully.

• Enable it on dedicated SQL hosts with 16 GB or more, where the buffer pool is the dominant consumer.
• Leave it off on 8 GB shared instances combining IIS and SQL, where flexibility is worth more than peak buffer-pool stability.
• Grant the right via secpol.msc → Local Policies → User Rights Assignment, or a GPO at scale. Confirm with the sys.dm_os_sys_memory DMV after restart, checking that locked_page_allocations_kb is non-zero.
• Do not enable it without also setting max server memory. Locking pages with no cap is how an instance consumes the entire machine.

Tempdb and File Sizing

Tempdb defaults to autogrowth of 64 MB, which means a busy reporting query can trigger dozens of growth events with their attendant latency spikes. Pre-size data and log files to expected working size and set growth to a fixed MB value:

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev,  SIZE = 2048MB, FILEGROWTH = 256MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = templog,  SIZE = 512MB,  FILEGROWTH = 128MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev2, SIZE = 2048MB, FILEGROWTH = 256MB);

Use one tempdb data file per four to eight logical cores, with the first file sized so all are equal. Instant file initialisation removes the zeroing cost on grow, and trace flag 1118 — once a manual requirement for mixed-extent contention — is now the default on SQL Server 2025 and should not be set explicitly.

Verify, Then Leave It Alone

Confirm the effective configuration and watch pressure over a full business cycle rather than a quiet afternoon:

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

SELECT total_physical_memory_kb/1024 AS phys_mb,
       available_physical_memory_kb/1024 AS avail_mb,
       system_memory_state_desc
FROM sys.dm_os_sys_memory;

Persistent system_memory_state_desc values of "Physical memory is low" mean the cap is too high for the working set, not too low. Pair this with the host-level counters described in the Windows performance monitoring guides so you catch paging before users do.

Reading Memory Pressure Correctly

The misleading counter is SQL Server’s own Total Server Memory, which will happily sit near your cap in a healthy system because a large buffer pool is the goal, not a symptom. Watch the host side instead: Pages/sec sustained above 100 to a disk-backed pagefile, or Available MBytes below the OS reserve, is real pressure. On a virtualised instance also check the hypervisor’s ballooning or swapping counters, since a host-level memory squeeze produces SQL Server latency spikes that look internal and are not. Correlating SQL waits with host memory state is the difference between tuning the database and fixing the machine.

A Worked Example at 16 GB

Take a single hosted instance running IIS and SQL Server 2025 on 16 GB. Reserve 1,536 MB for the OS, and if web traffic peaks around eight worker processes, budget roughly 4 GB for IIS and its app pools. That leaves about 10 GB, so set max server memory to 9,216 MB and leave min server memory at its default. Pre-size tempdb to 2 GB across two files, set growth to 256 MB, and keep Lock Pages in Memory off so the OS can reclaim under pressure. Watch Available MBytes and Pages/sec over a full week; if Available never dips near the reserve and paging stays flat, the split is right. If SQL Server regularly reports physical memory low while host counters look calm, the cap is too aggressive for the buffer pool’s working set and should be raised by 1 GB at a time rather than slashed.

The Shared-Instance Decision

Running IIS and SQL Server on the same instance is economical and perfectly workable under roughly 16 GB, provided you cap the buffer pool generously for the OS and the app pools. Above that threshold the coupling costs more than the consolidation saves, because a single noisy query can degrade web response times with no obvious cause. For teams that need clean separation, splitting web and database across two hosted Windows instances on a private network removes the contention entirely and makes each side independently sized.

Whatever the topology, set the cap first, size tempdb second, and only then consider Lock Pages in Memory. Doing it in that order prevents the most common failure mode, where a locked and uncapped buffer pool slowly starves every other service on the machine.