How can I reliably determine if a given SQL Server instance has the "Lock Pages in Memory" right?

Question

The "Lock Pages in Memory" right can be granted to the service account used by SQL Server. This allows SQL Server to prevent memory being paged to disk.

I've noticed several of our SQL Server machines do not have the local policy configured to allow this right for the service account used by SQL Server. Since we have a large number of servers, it will be tedious at best to manually check each one using the "Local System Policy" management console.

Is there a T-SQL query, extended stored procedure, or some other method I can use to determine if the server in question has the right or not?

I would like to not use EXEC xp_readerrorlog 0, 1, 'lock memory privilege was not granted'; since it relies on checking the current SQL Server error log; and that error log may not contain the relevant entry assuming the log has been rolled over since the server was last restarted. I realize I could check older logs by modify the first parameter from 0 to 1, etc, however we only retain 10 error logs, and that may not be enough. I want a fail-safe way of confirming the setting.

Answer 1

If xp_cmdshell is an option, here is a script making use of whoami:

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
BEGIN
    INSERT INTO @Res
    EXEC xp_cmdshell 'WHOAMI /PRIV';

    IF EXISTS (SELECT *
        FROM @Res 
        WHERE [output] LIKE 'SeLockMemoryPrivilege%'
        )
        SET @LockPagesInMemory = 'ENABLED';
    ELSE
        SET @LockPagesInMemory = 'DISABLED';
END

SELECT LockPagesInMemoryEnabled = @LockPagesInMemory;

whoami.exe has the ability to report all the rights held by the account executing whoami. This, combined with the xp_cmdshell provides a reliable way to determine if the instance of SQL Server has the necessary right to lock pages in memory.

If xp_cmdshell is not enabled, this code returns UNKNOWN. Assuming xp_cmdshell is enabled, and the right is enabled for the SQL Server account, it returns ENABLED, otherwise it returns DISABLED.

Answer 2

There are other methods as well. Perhaps you can use two DMVs. Please note that both will only work for SQL Server 2008 and above.

A non zero value for locked_page_allocations_kb would tell you that SQL Server account has Locked pages in memory privilege.

select osn.node_id, 
osn.memory_node_id, 
osn.node_state_desc, 
omn.locked_page_allocations_kb
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'

...and:

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys.dm_os_process_memory

If column Locked_pages_allocation_KB shows a non zero value then also SQL Server service account has Locked pages in memory privilege.

...and:

The "Memory Manager" section of the DBCC MEMORYSTATUS output will show a nonzero value for the "AWE Allocated" or if SQL Server service account has Locked pages in memory privilege.

NOTE:

If SQL Server service account is running with Local System (NT Authority\System) account, by default SQL Server will have Locked pages in memory privilege.

Update:

Before SQL Server 2012 for Standard Edition we need to enable trace flag t -845 to take advantage of LPIM. Even if you are running SQL Server with account having LPIM privilege the SQL Server would not actually use LPIM advantage unless the trace flag is enabled.

From 2012 onward we don't need to enable trace flag in standard edition to take LPIM advantage.

Answer 3

Microsoft makes this easier in newer versions of the product if what you really want to know is if SQL Server is running with a memory model that takes advantage of LPIM (as opposed to having the right permission). This information is in sys.dm_os_sys_info depending on your version of SQL Server. The enhancement was announced in a blog post:

With SQL Server 2016 SP1 and SQL Server 2012 SP4, there are two new columns viz sql_memory_model and sql_memory_model_desc added to sys.dm_os_sys_info which can be used to identify if Lock Pages in Memory (LPIM) permissions are assigned to SQL Server service account.

One important thing to be aware of is, the new columns report the sql memory model in use since the startup of SQL Server instance which is the information desired. There are no checks performed at the OS level for LPIM permissions for the sql service account. If during the SQL Server startup, LPIM privilege is present in SQL server service account process token, SQL Server uses locked pages (non-pageable) to allocate sql memory. Further, if you are running Enterprise edition of SQL Server with LPIM privileges assigned to sql service account and trace flag 834 turned ON, SQL Server uses large pages to allocate sql memory.

To check if the Lock Pages in memory privilege is in effect for a given SQL Server instance, you can query sql_memory_model in sys.dm_os_sys_info and look for values greater than 1.

If LPIM permissions is missing in service account process token, conventional memory model is used and the same is reported by DMV (sql_memory_model = 1). If now, lock pages in memory privilege is assigned to SQL server but SQL service is not restarted, DMV will continue to report conventional memory model since that is the memory model in effect since startup. After restart, SQL Server uses locked pages in memory model and the same is reported by the sql_memory_model and sql_memory_model_desc in sys.dm_os_sys_info.

If I run the following query on one of my servers:

SELECT sql_memory_model, sql_memory_model_desc
FROM sys.dm_os_sys_info;

The memory model currently in use is CONVENTIONAL, which means that LPIM was not granted at the time of server startup. However, it would be possible to grant LPIM but not restart the SQL Server service, so this DMV may not be useful for you depending on the exact nature of your question.