Lock Pages In Memory prevents SQL Server memory pages from being trimmed from the working set. Without it, Windows may reclaim buffer-cache memory under pressure, page data to disk, and trigger severe degradation or query timeouts. section 10.4
Mistake: treating the setting as an automatic win. In virtualized environments, locked memory cannot be reclaimed through hypervisor ballooning, potentially creating pressure across the virtualization estate. Use it deliberately, coordinate with virtualization administrators, and reserve sufficient RAM for Windows and other applications. section 10.4 section 10.14
A resource-constrained SQL Server
Users report severe performance degradation and query timeouts. The SQL Server log indicates that part of the buffer cache has been flushed to disk and reclaimed by the operating system. This makes working-set pressure a concrete diagnostic clue rather than merely a configuration concern. section 10.4
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): 6081740, committed (KB): 17175674, memory utilization: 35%.
Use the memory-pressure listing to connect paged-out SQL Server process memory with possible performance degradation. section 10.4
Apply these rules:
- Use Lock Pages In Memory in many situations, but consider disabling it in private clouds; public cloud providers recommend it. section 10.14
- Always leave enough RAM for the operating system and other applications, especially when pages are locked. section 10.14
- Use Max Server Memory to bound the SQL Server buffer pool rather than consuming all available RAM. section 10.14
