SQL Server Max Memory Setting with Lock Pages in Memory

By: Simon Liew | Updated: 2020-11-30 | Comments (2) | Related: > SQL Server Configurations

Problem

I set SQL Server maximum memory and granted Lock Pages in Memory (LPIM) to the SQL Server service account. When the SQL Server service is restarted for LPIM to take effect, does this mean SQL Server will immediately be able to lock its entire buffer pool up to the configured maximum memory from being paged-out to virtual memory on disk?

Solution

A process which runs under an account granted LPIM privilege allows it to lock physical memory and prevents the Operating System (OS) from paging the locked memory to virtual memory on disk.

It is a misconception that SQL Server locks its entire buffer pool up to its maximum configured memory upon start up with LPIM privilege. When SQL Server starts up, it acquires memory as needed to support the workload until it reaches its maximum configured memory allocation target, or the OS indicates there is no longer an excess of free memory.

Hence, the locked memory here is the memory that the SQL Server Buffer Pool Manager has acquired and allocated to its buffer pool. Note that non-buffer pool portion of SQL Server such as thread stacks, linked servers, extended procedures, and any multi-page allocations by SQL Server that occur outside of the buffer pool can still get paged out.

To demonstrate and prove this behavior, this tip will present results collected on five actions performed in a test environment.

Test Environment

Our test environment is a virtual machine with 4 GB of physical memory hosting SQL Server 2016 Developer Edition (SP2) on Microsoft Windows Server 2012 R2 Data Center Edition. Performance monitor is started and running in the background every second to capture the four performance counters below.

SQL Server: Memory Manager\Target Server Memory (KB)
SQL Server: Memory Manager\Total Server Memory (KB)
Memory: AvailableMbytes
Paging File: % Usage:C:\pagefile.sys

LPIM privilege is granted to the SQL Server service account. We are not capturing performance counter Process: Private Bytes and Process: Working Set here as SQL Server will use the AWE API to allocate memory instead of the OS Virtual Memory Manager when LPIM is granted. So, we will be relying on performance counter Total Server Memory (KB) which behavior is unchanged regardless of LPIM to reflect amount of memory the SQL Server has committed.

Test Result Output

There are five actions undertaken representing five scenarios outlined in this tip. We will go through each scenario as marked by the numbered red circle as per the screenshot below.

Perform Line Graph Output This perform line graph has five point in time scenarios, each marked with the red circle

Point in time 1 – Set SQL Server Maximum Memory

SQL Server maximum memory is set to 3 GB and SQL Server service is restarted prior to our test. This is the sudden dip observed in point-in-time 1.

EXEC sys.sp_configure N'show advanced options', N'1'  
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'3072'
GO

When SQL Server starts-up, it writes a lot of useful messages to the SQL Server Log. One way to confirm LPIM is enable on your SQL Server instance is to look for the message highlighted in the red box as below.

Point in time 2 – Simulate Memory Leak on Windows OS

We will be using a tool called TestLimit from Sysinternals to simulate a memory leak on the Windows OS. We begin the test by having this tool consume 2 GB of memory from the virtual machine with the command below.

Simulate Memory Leak using Testlimi Launch a command prompt and run as Administrator. Then execute the command Testlimit64.exe -d -c 2048

We can immediately see that the Windows OS available memory dropped by 2 GB in the perfmon line graph as indicated by the red circle point-in-time 2. SQL Server Target Server Memory (KB) counter also reflects the "missing" 2 GB memory by dropping its value from 2.8 GB to 788 MB, but the Total Server Memory (KB) counter which shows memory that SQL Server has already acquired did not change much.

The first test here confirms to us that an application or process in Windows OS can easily acquire physical memory that SQL Server has yet to allocate to its buffer pool.

Point in time 3 – Start SQL Server Workload

We will start a SQL Server workload which populates SQL Server buffer pool using the same script as per section Point-in-time 3: Executing SQL Workload in tip SQL Server Perfmon Counters - Target vs Total Memory and Max Memory.

As soon as the workload was started at point-in-time 3, the Target Server Memory (KB) counter dips again. This is due to the SQL Server memory manager reflecting the actual memory that SQL Server can acquire under the current condition. In the next few seconds, the Target Server Memory (KB) and Total Server Memory (KB) counter intersects with each other. This behavior explained in the tip SQL Server Perfmon Counters - Target vs Total Memory and Max Memory.

The perfmon line graph indicate that SQL Server will still responds to external memory pressure, but it does try to prevent its physical memory from being paged out into virtual memory on disk.

Point in time 4 – Memory Leak Beyond Server Physical Memory

So far, the virtual machine has enough physical memory to cater for our test scenarios and the Paging File: % Usage:C:\pagefile.sys counter is at zero.

We will launch a new command prompt and use Testlimit to consume another 1 GB memory in the virtual machine.

Simulate Memory Leak using Testlimit Launch a command prompt and run as Administrator. Then execute the command Testlimit64.exe -d -c 1024

At point-in-time 4, we see that the Windows OS page file usage shot up by 4%. SQL Server Total Server Memory (KB) counter did not drop drastically as LPIM is trying to prevent SQL Server buffer pool from being paged out to virtual memory on disk. This shows some of the memory that Testlimit has acquire is taken from virtual memory on disk.

Continue Pushing Memory Usage

A continued test using Testlimit to keep consuming memory on the virtual machine ended up crashing the virtual server with an unexpected shutdown.

Conclusion

This tip provides an unusual, but possible scenario where an application memory leak on the OS could happen way before SQL Server reaches its configured maximum memory allocation target. It shows that LPIM does not lock memory that SQL Server has not committed to its buffer pool and hence causes severe memory pressure when an intensive workload is started.

The demonstration shows a race condition on acquiring physical memory in the virtual server, and LPIM might not be effective if a memory leak on Windows OS or an application consumes the server physical memory way before SQL Server does. An intensive memory pressure on the OS can crash the server hosting SQL Server regardless of the LPIM setting.

Next Steps

About the author

Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2020-11-30

Monday, November 30, 2020 - 8:12:01 PM - Simon Liew	Back To Top (87865)
Thanks for the comment Vesa. It also gave me an idea for my future tip. LPIM with TF834 means the buffer pool allocation need to be contiguous or it has to back off the size and attempt again. If I could reproduce this behaviour in my test environment, I'll write a tip on this.

Monday, November 30, 2020 - 5:13:46 AM - Vesa Juvonen	Back To Top (87862)
You can ensure that your SQL Server service allocates all max memory at startup by enabling trace flag 834 (use large pages)