Reviewing Max Server Memory and Lock Pages in Memory
Someone hands you a list of forty instances and asks whether their memory settings are sane. Nobody remembers who installed half of them. Some were built by a setup wizard, some by a script, and some by someone who accepted every default. You don’t want to open forty Server Properties dialogs, and you don’t want to change anything yet. You want a short list of instances to look at more closely.
The data for that list comes from one catalog view and three dynamic management views (DMVs). This post walks through what each one tells you, gives a read-only query that flags the common problems, and shows what my two test instances returned.
Max and Min Server Memory
The two settings live in sys.configurations. The value column holds what was configured, and value_in_use holds what the instance is running with. The default for max server memory (MB) is 2,147,483,647, which in practice means no limit. The default for min server memory (MB) is 0.[1] Microsoft notes that a configured max of 0 shows up as 2147483647 in value_in_use, so check value_in_use when you test for the default.[2]
Microsoft Learn recommends setting an upper limit in all versions. Its table lists the recommended max as 75% of available system memory not consumed by other processes, including other instances. For an initial setting on a single instance, it also gives a method: start with total OS memory, subtract the thread stack size multiplied by the calculated max worker threads, then subtract another 25% for allocations outside the max server memory limit. Learn calls that 25% a generic approximation. It also says to monitor the server’s memory use under normal load before you settle on a value.[1] I won’t add a formula of my own. The query below doesn’t try to pick a value for you. It flags values that are wrong on their face, such as the default.
Learn also says not to set min and max to the same value, or to values close to each other. It also says SQL Server doesn’t allocate min server memory at startup. Once memory use reaches that value, SQL Server can’t release memory below it unless you lower the setting.[1]
Is Lock Pages in Memory in Effect?
Lock Pages in Memory (LPIM) is a Windows user right. When the SQL Server service account holds it, SQL Server allocates memory that Windows can’t page out to disk. You grant it with the Local Security Policy (or Group Policy) “Lock pages in memory” setting, to the service account or the service SID. The instance has to restart before the setting takes effect.[3]
That restart is why the Windows policy is the wrong place to check. The policy tells you what will happen at the next start. For the memory model the instance is using now, read sys.dm_os_sys_info.sql_memory_model_desc:[4]
CONVENTIONAL: the service account didn’t have the right when the instance started.LOCK_PAGES: the service account had the right at startup, and SQL Server is using locked pages.LARGE_PAGES: Enterprise edition, the right at startup, and trace flag 834 turned on.
Learn lists three other ways to confirm it. locked_page_allocations_kb is nonzero, the error log has the startup message Using locked pages in the memory manager, or DBCC MEMORYSTATUS shows a nonzero AWE Allocated.[1] My instances logged Using conventional memory in the memory manager. at startup instead.
Learn doesn’t recommend LPIM for every server. It says LPIM should be used when there are signs that the process is being paged out, such as error 17890 in the error log. It also warns that LPIM with a max server memory value that doesn’t leave room for other memory users might cause instability, and it strongly recommends setting max server memory when the right is granted.[1] So my query reports “conventional memory on a large host” as information to look into, and leaves the decision to you.
Two More DMVs
sys.dm_os_process_memory reports memory for the SQL Server process. locked_page_allocations_kb and large_page_allocations_kb show locked and large page memory. memory_utilization_percentage is the share of committed memory that’s in the working set. process_physical_memory_low shows whether the process is responding to a low physical memory notification.[5]
sys.dm_os_sys_memory is the operating system’s view. It reports total and available physical memory, and system_memory_state_desc turns the Windows high and low memory signals into a phrase such as Available physical memory is high.[6]
sys.dm_os_sys_info also has committed_kb and committed_target_kb. When the target is larger than committed memory, the memory manager tries to get more memory. When it’s smaller, the memory manager tries to shrink.[4] Those two columns show a max server memory change taking effect, which I’ll come back to.
The Review Query
The query reads one instance and returns one row. It changes nothing, and it needs VIEW SERVER STATE (or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later) for the DMVs.[5]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
USE [master]; SET NOCOUNT ON; DECLARE @large_memory_gb int = 64; WITH [cfg] AS ( SELECT MAX(CASE WHEN [c].[name] = N'max server memory (MB)' THEN CONVERT(bigint, [c].[value_in_use]) END) AS [max_mb] , MAX(CASE WHEN [c].[name] = N'min server memory (MB)' THEN CONVERT(bigint, [c].[value_in_use]) END) AS [min_mb] , MAX(CASE WHEN [c].[name] = N'max server memory (MB)' THEN CONVERT(bigint, [c].[value]) END) AS [max_mb_configured] FROM [sys].[configurations] AS [c] WHERE [c].[name] IN (N'max server memory (MB)', N'min server memory (MB)') ) SELECT @@SERVERNAME AS [instance_name] , CONVERT(nvarchar(128), SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS')) AS [host_name] , CONVERT(nvarchar(128), SERVERPROPERTY(N'ProductVersion')) AS [product_version] , [si].[physical_memory_kb] / 1024 AS [physical_memory_mb] , [cfg].[min_mb] , [cfg].[max_mb] , [cfg].[max_mb_configured] , [si].[committed_kb] / 1024 AS [committed_mb] , [si].[committed_target_kb] / 1024 AS [committed_target_mb] , [si].[sql_memory_model_desc] , [pm].[locked_page_allocations_kb] , [pm].[large_page_allocations_kb] , [pm].[memory_utilization_percentage] , [pm].[process_physical_memory_low] , [sm].[available_physical_memory_kb] / 1024 AS [available_physical_memory_mb] , [sm].[system_memory_state_desc] , CASE WHEN [cfg].[max_mb] = 2147483647 THEN 1 ELSE 0 END AS [flag_max_at_default] , CASE WHEN [cfg].[max_mb] <> 2147483647 AND [cfg].[max_mb] > [si].[physical_memory_kb] / 1024 THEN 1 ELSE 0 END AS [flag_max_over_physical] , CASE WHEN [cfg].[min_mb] = [cfg].[max_mb] THEN 1 ELSE 0 END AS [flag_min_equals_max] , CASE WHEN [si].[sql_memory_model_desc] = N'CONVENTIONAL' AND [si].[physical_memory_kb] >= CONVERT(bigint, @large_memory_gb) * 1024 * 1024 THEN 1 ELSE 0 END AS [info_conventional_memory_large_host] FROM [cfg] CROSS JOIN [sys].[dm_os_sys_info] AS [si] CROSS JOIN [sys].[dm_os_process_memory] AS [pm] CROSS JOIN [sys].[dm_os_sys_memory] AS [sm]; |
The four flags:
flag_max_at_default: max server memory is 2147483647.flag_max_over_physical: max server memory is set, but to more than the physical memory on the machine. The default is excluded so the two flags don’t fire together.flag_min_equals_max: min and max are equal. Learn also warns against values that are close, which this flag doesn’t catch. Compare the two columns by eye.info_conventional_memory_large_host: the instance runs with conventional memory on a machine with at least@large_memory_gbof RAM. The 64 GB threshold is my choice, not Microsoft’s. Change it to suit your estate.
Each row covers one instance, but max server memory is a per-instance limit on a shared machine. Learn says that when several instances share a host, the total of their max values shouldn’t exceed the machine’s physical memory.[1] The host_name column is there so you can group the rows by machine and add up max_mb.
To run it across a list of instances, a loop is enough:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
$instances = @('SQLPROD01', 'SQLPROD02\SALES', 'SQLPROD03') $query = Get-Content -Path '.\fleet-memory-review.sql' -Raw $results = foreach ($instance in $instances) { Invoke-Sqlcmd -ServerInstance $instance -Query $query } $results | Where-Object { $_.flag_max_at_default -eq 1 -or $_.flag_max_over_physical -eq 1 -or $_.flag_min_equals_max -eq 1 -or $_.info_conventional_memory_large_host -eq 1 } | Select-Object instance_name, host_name, physical_memory_mb, min_mb, max_mb, sql_memory_model_desc |
I tested this loop with Invoke-Sqlcmd from the SQLPS 17.0 module in Windows PowerShell 5.1. Newer versions of the SqlServer module may need extra connection or encryption parameters, depending on how your instances are set up. A Central Management Server multi-server query may also work, since the query returns one row per instance, but I didn’t test that.
What My Instances Returned
I ran the query on two instances on the same workstation: SQL Server 2019 (15.0.4500.2, Developer) and SQL Server 2025 (17.0.5015.4, Enterprise Developer).
| Column | SQL Server 2019 | SQL Server 2025 |
|---|---|---|
| physical_memory_mb | 31,878 | 31,878 |
| min_mb | 256 | 256 |
| max_mb | 2,048 | 2,048 |
| committed_mb | 2,071 | 1,298 |
| committed_target_mb | 2,048 | 2,048 |
| sql_memory_model_desc | CONVENTIONAL | CONVENTIONAL |
| locked_page_allocations_kb | 0 | 0 |
| large_page_allocations_kb | 0 | 0 |
| memory_utilization_percentage | 96 | 79 |
| process_physical_memory_low | 0 | 0 |
| system_memory_state_desc | Available physical memory is high | Available physical memory is high |
All four flags were 0 on both. The two max values add up to 4,096 MB, well under the 31,878 MB on the machine. When I set @large_memory_gb to 16, the informational flag returned 1 on both instances, which is what I expected: both run as NT AUTHORITY\NetworkService, and I haven’t granted that account the right. I didn’t grant it for this post, so the LOCK_PAGES and LARGE_PAGES values come from the documentation, not from my test.
Changing Max Server Memory While the Instance Runs
Learn says the two memory settings take effect immediately, without a restart.[1] I wanted to see how quickly, so on the 2019 instance I lowered max server memory from 2,048 MB to 1,024 MB, sampled sys.dm_os_sys_info about every two seconds, then set it back to 2,048 MB.
|
1 2 3 4 5 6 7 |
EXECUTE [sys].[sp_configure] N'max server memory (MB)', 1024; RECONFIGURE; /* sample, then restore */ EXECUTE [sys].[sp_configure] N'max server memory (MB)', 2048; RECONFIGURE; |
| Seconds after change | Setting | committed_kb | committed_target_kb |
|---|---|---|---|
| 0 (before) | 2,048 | 2,097,288 | 2,097,152 |
| 2.2 | 1,024 | 2,097,528 | 2,097,152 |
| 4.3 | 1,024 | 2,064,512 | 1,048,576 |
| 6.4 | 1,024 | 1,048,760 | 1,048,576 |
| 0.1 (after restore) | 2,048 | 1,050,728 | 1,048,576 |
| 8.5 | 2,048 | 1,048,840 | 1,048,576 |
| 16.5 | 2,048 | 1,050,520 | 2,097,152 |
| 21.6 | 2,048 | 1,976,288 | 2,097,152 |
| 26.7 | 2,048 | 2,165,832 | 2,097,152 |
| 31.8 | 2,048 | 2,097,328 | 2,097,152 |
On the way down, the target dropped between 2.2 and 4.3 seconds after RECONFIGURE, and committed memory reached the new target by the next sample. On the way up, the target stayed at 1 GB for at least 8.5 seconds and was back at 2 GB by 16.5 seconds. Committed memory then grew back to about 2 GB, briefly going 67 MB over the target. I didn’t run a workload during the test. Other test databases were on the instance, so I can’t say what asked for the memory.
Along with the two configuration change messages, the error log recorded three cachestore flush messages within 40 milliseconds of the first RECONFIGURE:
|
1 2 3 4 5 |
Configuration option 'max server memory (MB)' changed from 2048 to 1024. Run the RECONFIGURE statement to install. SQL Server has encountered 6 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. SQL Server has encountered 6 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. SQL Server has encountered 6 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. Configuration option 'max server memory (MB)' changed from 1024 to 2048. Run the RECONFIGURE statement to install. |
That is consistent with the documentation. The DBCC FREEPROCCACHE page lists max server memory and min server memory among the reconfigure operations that clear the procedure cache. It also says the flush message is logged every five minutes while flushes continue, which explains why the second change, 17 seconds later, added no flush message of its own.[7] If you correct a flagged instance during business hours, queries will compile new plans the next time they run. I changed this setting for Testing Trace Flag 2861 and Zero-Cost Plans on SQL Server 2019 and 2025 and didn’t mention the flush there.
If you track memory over time, Page Life Expectancy and Data Throughput and Performance Counters Over Time cover the counters to watch after a change. For comparing all of sys.configurations against a saved baseline, not only the memory settings, see configuration drift across instances.
Limits
- I tested on one workstation with 31 GB of RAM, two instances, and Developer editions. No virtual machine host, no cluster, and no Linux.
- I didn’t grant Lock Pages in Memory, restart either instance, or test trace flag 834.
- I made one change, on one instance, and sampled every two seconds. The timings above may differ on a busy server or with a bigger buffer pool.
- The flags find obvious mistakes. A value that passes all four can still be wrong for the workload.
If you’ve run a review like this, I’d like to know what turned up most often. Find me on Bluesky or LinkedIn.
References
- Server memory configuration options - Microsoft Learn. Defaults and the recommended max (75% of available memory not used by other processes), the initial-configuration method, the advice against equal min and max, how min server memory behaves, the LPIM guidance and checks, the multi-instance total, and that the settings take effect without a restart. ↩
- Server configuration options (SQL Server) - Microsoft Learn. States that a configured max server memory of 0 displays as 2147483647 in value_in_use. ↩
- Enable the Lock Pages in Memory Option (Windows) - Microsoft Learn. How to grant the user right to the service account or service SID, and that the instance must restart for it to take effect. ↩
- sys.dm_os_sys_info (Transact-SQL) - Microsoft Learn. Definitions of sql_memory_model_desc (CONVENTIONAL, LOCK_PAGES, LARGE_PAGES), committed_kb and committed_target_kb. ↩
- sys.dm_os_process_memory (Transact-SQL) - Microsoft Learn. Column definitions for locked and large page allocations, memory utilization and the low memory flags, and the required permissions. ↩
- sys.dm_os_sys_memory (Transact-SQL) - Microsoft Learn. Column definitions and how system_memory_state_desc maps to the Windows high and low memory signals. ↩
- DBCC FREEPROCCACHE (Transact-SQL) - Microsoft Learn. Lists the reconfigure operations that clear the procedure cache, including max and min server memory, and says the flush message is logged every five minutes. ↩