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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n 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:<\/p>\n\n\n\n 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.<\/p>\n\n\n\n Confirm the effective configuration and watch pressure over a full business cycle rather than a quiet afternoon:<\/p>\n\n\n\n Persistent 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.<\/p>\n\n\n\n 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 (MB)<\/code> 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.<\/p>\n\n\n\nSizing Table for Common Instance Profiles<\/h2>\n\n\n\n
Total RAM<\/th> SQL-plus-IIS host<\/th> SQL-only host<\/th> OS reserve<\/th><\/tr><\/thead> 8 GB<\/td> 4,608 MB<\/td> 5,632 MB<\/td> 1,024 MB + IIS 2 GB<\/td><\/tr> 16 GB<\/td> 9,216 MB<\/td> 11,264 MB<\/td> 1,536 MB + IIS 4 GB<\/td><\/tr> 32 GB<\/td> 18,432 MB<\/td> 23,552 MB<\/td> 2,048 MB + IIS 8 GB<\/td><\/tr> 64 GB<\/td> 36,864 MB<\/td> 47,104 MB<\/td> 4,096 MB + IIS 12 GB<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Lock Pages in Memory: When It Helps and When It Hurts<\/h2>\n\n\n\n
secpol.msc<\/code> → Local Policies → User Rights Assignment, or a GPO at scale. Confirm with the sys.dm_os_sys_memory<\/code> DMV after restart, checking that locked_page_allocations_kb<\/code> is non-zero.<\/li>max server memory<\/code>.<\/strong> Locking pages with no cap is how an instance consumes the entire machine.<\/li><\/ul>\n\n\n\nTempdb and File Sizing<\/h2>\n\n\n\n
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 2048MB, FILEGROWTH = 256MB);\nALTER DATABASE tempdb MODIFY FILE (NAME = templog, SIZE = 512MB, FILEGROWTH = 128MB);\nALTER DATABASE tempdb MODIFY FILE (NAME = tempdev2, SIZE = 2048MB, FILEGROWTH = 256MB);<\/code><\/pre>\n\n\n\nVerify, Then Leave It Alone<\/h2>\n\n\n\n
SELECT name, value_in_use FROM sys.configurations\nWHERE name IN ('max server memory (MB)','min server memory (MB)');\n\nSELECT total_physical_memory_kb\/1024 AS phys_mb,\n available_physical_memory_kb\/1024 AS avail_mb,\n system_memory_state_desc\nFROM sys.dm_os_sys_memory;<\/code><\/pre>\n\n\n\nsystem_memory_state_desc<\/code> 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<\/a> so you catch paging before users do.<\/p>\n\n\n\nReading Memory Pressure Correctly<\/h2>\n\n\n\n
A Worked Example at 16 GB<\/h2>\n\n\n\n
max server memory<\/code> 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.<\/p>\n\n\n\nThe Shared-Instance Decision<\/h2>\n\n\n\n