LLI Wiki — My Digital Knowledge Base

Check the Lock Pages in Memory privilege

Check from T-SQL whether the SQL Server service account holds the SeLockMemoryPrivilege right, using xp_cmdshell.

Lock Pages in Memory (LPIM) lets the SQL Server process keep its buffer pool in physical memory. The Windows account that runs the SQL Server service needs the SeLockMemoryPrivilege right. The query below runs WHOAMI /PRIV through xp_cmdshell and reports ENABLED, DISABLED or UNKNOWN.

Enable xp_cmdshell

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

Check the privilege

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;

Disable xp_cmdshell after the check

EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

Notes

Mastodon LLIMastodon SocialFosstodon