SQL Server together with other RDBMS, are the most memory consuming applications on our servers, and this is because, RDBMs usually cache objects into the memory to take advantage of the speed that physical memory offers.
Sadly when windows feels that its physical memory is currently not enough for a driver and/or processes that is requesting some resources, it is forced to trim some of currently running application’s memory working set. Now that is bad news for SQL Server because windows will be forced to push the objects from the memory to the servers’ paging file. You can verify if windows is doing this to your SQL Server by investigating SQL Server Logs for this entries
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): 1086400, committed (KB), memory Utilization: 50%.
Currently there are 2 settings that we can play with to avoid or at least alleviate this situation
1. Properly set SQL Servers’ Max Memory settings, by setting aside enough memory for the OS and other running Processes like Antivirus and Server Monitoring Software.
2. Enable SQL Server Locked Page in Memory (LPIM)
NOTE: Enabling LPIM without fully Understanding how SQL Memory Works and without knowing how your DB Server Behaves specially if it’s in a Virtual Environment might give you negative effects.
For this Post we will be focusing on LPIM. When using LPIM Windows cannot simply touch the memory space used by SQL Servers’ Buffer Pool, it is locked and cannot be paged. SQL Server does this by using Address Windowing Extensions (AWE).
When LPIM is enabled you cannot simply view how much really SQL Server is using by viewing Task Manager. As you can see on the screen shot below, SQLSERVR.EXE is only using 49, 536Kb
You can however use RamMap (A free RAM tool from Sysinternals: www.sysinternals.com). To view how much memory AWE is using.
Or you can also use sys.dm_os_process_memory SQL Server Dynamic Management Views (DMV).
How to Enable LPIM
Use Windows Group Policy tool (gpedit.msc) to enable this policy for the account used by SQL Server. You must be a system administrator to change this policy.
1. On the Start menu, click Run. In the Open box, type gpedit.msc.
2. On the Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings.
3. Expand Security Settings, and then expand Local Policies.
4. Select the User Rights Assignment folder.
The policies will be displayed in the details pane.
5. In the pane, double-click Lock pages in memory.
6. In the Local Security Setting – Lock pages in memory dialog box, click Add User or Group.
7. In the Select Users, Service Accounts, or Groups dialog box, add an account with privileges to run sqlservr.exe.
8. Log out and then log back in for this change to take effect.
Note about SQL Server 2008 R2 Standard Edition (64-bit): Microsoft SQL Server 2008 R2 Standard Edition (64-bit, all versions RTM and later) also requires trace flag 845 to be added as a startup parameter so that SQL Server can use locked pages for the Buffer Pool when the SQL Server service account is granted the Lock Pages in Memory security privilege
Note about SQL Server 2012 Standard Edition (64-bit): Microsoft SQL Server 2012 Standard Edition (64-bit) does not require you to enable any trace flag to allow SQL Server use locked pages for the Buffer pool when the SQL Server service account is granted the Lock Pages in Memory security privilege.
How to determine if LPIM is enabled
Option 1 (Tested SQL 2005 )
DECLARE @LockPagesInMemory VARCHAR(255);
SET @LockPagesInMemory = 'UNKNOWN';
DECLARE @Res TABLE
[output] NVARCHAR(255) NULL
IF (SELECT value_in_use
FROM sys.configurations c
WHERE c.name = 'xp_cmdshell'
) = 1
INSERT INTO @Res
EXEC xp_cmdshell 'WHOAMI /PRIV';
IF EXISTS (SELECT *
WHERE [output] LIKE 'SeLockMemoryPrivilege%'
SET @LockPagesInMemory = 'ENABLED';
SET @LockPagesInMemory = 'DISABLED';
SELECT LockPagesInMemoryEnabled = @LockPagesInMemory;
Option 2 (Tested SQL 2008)
from sys.dm_os_memory_nodes omn
inner join sys.dm_os_nodes osn on (omn.memory_node_id = osn.memory_node_id)
where osn.node_state_desc <> 'ONLINE DAC'
NOTE: A non zero value for locked pages allocation means Locked pages in memory is enabled
Option 3 (TestedSQL 2008)
from sys. dm_os_process_memory
NOTE: A non zero
Locked_pages_allocation_KB means Locked pages in memory is enabled.
Using Exec xp
xp_readerrorlog 0, 1, 'locked pages'
xp_readerrorlog 0, 1, 'lock pages in memory'
Technical References/Further Reading:
Support for Locked Pages on SQL Server Standard Edition (64-bit) systems