Power Plan. Max Server Memory. Instant File Initialization. Lock Pages in Memory.
Four host-level settings. All documented. All decades old. All considered standard SQL Server best practices for production environments to maximize performance and stability. Yet I still find them missing again and again.
Here they are in the order that I check them.
1. Power Plan: Balanced
Windows ships with the Balanced power plan as the default. On a dedicated SQL Server host, that is wrong. Balanced throttles CPU frequency down when load is low and ramps it back up under demand. The ramp is not instant. On bursty SQL Server workloads, cores can run at reduced clock speeds even when queries are waiting for them.
The symptom in the wait stats is elevated SOS_SCHEDULER_YIELD and high signal wait percentages that do not match the actual workload intensity. You look at the numbers and think you have a CPU capacity problem? No. You just have a configuration problem.
This is not a theoretical impact. Glenn Berry at SQLskills has measured 20-25% performance differences between Balanced and High Performance on SQL Server workloads, and the effect persists across every Windows Server release since 2008.
Check it from an elevated command prompt on the server using powercfg:
If it returns Balanced, change it in Control Panel \ Power Options, like you see here. The change is immediate, no reboot required:
2. Max Server Memory set too low
Max Server Memory caps how much RAM SQL Server will use for its buffer pool and related caches. Set too low, SQL Server cannot cache as much data as the host has RAM to support, and you pay for it in disk reads that should have been memory hits.
The instance I was looking at had 64 GB of physical RAM on a dedicated SQL Server box. Max Server Memory was set to 38 GB. That is 26 GB of RAM sitting idle that SQL Server wasn't able to touch.
Check the current setting:
SELECT name, value_in_use
FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'min server memory (MB)');
SELECT
physical_memory_kb / 1024 / 1024 AS PhysicalMemoryGB,
committed_kb / 1024 / 1024 AS SQLCommittedGB,
committed_target_kb / 1024 / 1024 AS SQLTargetGB
FROM sys.dm_os_sys_info;
Microsoft's current guidance is to set Max Server Memory to approximately 75% of physical RAM on a dedicated host, leaving the remainder for the OS and everything else. Brent Ozar covered this change in September 2025 — Microsoft's own installer now sets 75% by default when you pick the recommended memory option. On a 64 GB box, that lands around 48 GB. Brent's simpler rule of thumb is to leave 4 GB or 10% free, whichever is larger, and I tend to lean closer to that on boxes with lots of RAM. Either way, 38 GB allocation to a dedicated host with 64 GB physical is a little low.
Adjust it:
EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'max server memory (MB)', 58880; -- ~57.5 GB RECONFIGURE;
The change is immediate. No restart required.
3. Instant File Initialization (IFI)
When SQL Server grows a data file, Windows by default writes zeros across every byte of the newly allocated space before SQL Server can use it. A 1 GB data file growth means writing 1 GB of zeros to disk first. On slow storage, that can be painful.
Instant File Initialization skips the zero-writing step. The space is marked as allocated in the filesystem metadata and SQL Server uses it immediately. Growth is near-instant regardless of size.
IFI applies to data files only. Transaction log files always zero-initialize, with one narrow exception introduced in SQL Server 2022: log autogrowth events up to 64 MB can now benefit from IFI. For all practical purposes, treat IFI as 'speeds up data file growth.'
Check whether it is enabled:
SELECT instant_file_initialization_enabled FROM sys.dm_server_services WHERE servicename LIKE 'SQL Server (%';
If it returns N, the SQL Server service account does not have the Perform Volume Maintenance Tasks privilege.
To grant it, open Run and type secpol.msc to open Local Security Policy. Navigate to Local Policies \ User Rights Assignment, double-click 'Perform volume maintenance tasks,' and add the SQL Server service account. This will only take effect after SQL Server is restarted -- so you've got to plan this one and communicate it. Microsoft's instructions are here.
IFI pairs directly with oversized filegrowth increments. A data file set to grow by 1024MB without IFI stalls every growth event while Windows zeroes out the full gigabyte. The same file with IFI enabled grows in milliseconds. If you inherit an instance with large data file growth settings and have not enabled IFI, fixing IFI first makes the existing growth settings tolerable while you work on right-sizing the data files.
4. Lock Pages in Memory (LPIM)
Windows treats SQL Server's memory allocations like any other process. Under memory pressure, the OS can page portions of SQL Server's working set out to the pagefile. When SQL Server later needs those pages, it reads them back from disk. For a database engine whose performance model is built on keeping hot data in RAM, this can be catastrophic.
Lock Pages in Memory prevents that. With LPIM granted, SQL Server allocates its buffer pool using the AWE API and those pages are locked in physical RAM. Windows cannot page them out.
The smoking-gun symptom of not having LPIM, when it bites, is 'A significant part of sql server process memory has been paged out' in the SQL Server error log. It often shows up alongside sudden Page Life Expectancy cliffs that do not correspond to any workload change.
Check it:
SELECT sql_memory_model, sql_memory_model_desc FROM sys.dm_os_sys_info;
If sql_memory_model_desc returns CONVENTIONAL, then LPIM is not in effect. If it says LOCK_PAGES, then it is.
We enable this policy just like IFI: Run, type secpol.msc, navigate to Local Policies \ User Rights Assignment, look for 'Lock pages in memory', and add in the SQL Server service account. Microsoft's instructions are here.
LPIM also requires a SQL Server service restart. Pair it with the IFI change and you'll get them both with one restart.
One honest caveat: LPIM is a little more opinionated than the other three. On a dedicated SQL Server box with Max Server Memory set correctly, it is the right call. If a server hosts multiple applications beyond just SQL Server, or faces extreme memory pressure, it could starve the OS and cause instability. It is designed for dedicated database servers to prevent Windows from paging RAM to disk, but it can hinder performance if not paired with the appropriate maximum server memory limit. This is important.
LPIM is crucial on physical servers, but often debated or said to be 'unnecessary' for VMs, where the hypervisor manages memory pressure. VM or not, it is a setting that I include in my defaults.
The Pattern
Every one of these four settings are documented, well-known, and decades old. None of them are obscure. Yet I find them wrong more often than I find them right.
If you are running a health check on a new instance, check all four. Always.
And if you are looking at performance data that does not quite make sense — CPU pressure that feels out of proportion to the workload, I/O that seems slow against the storage tier, or memory that seems tight on a box with headroom — check these four before you start tuning queries. Host-level misconfigurations can often look like query-level symptoms until you rule them out.
More to read
Glenn Berry — Windows Power Plan Effects on Newer Intel Processors
Microsoft Learn — Server Memory Configuration Options
Brent Ozar — Microsoft Now Recommends You Set Max Memory to 75%
Brent Ozar — Instant File Initialization
Microsoft Learn — Enable the Lock Pages in Memory Option (Windows)
No comments:
Post a Comment