SDU Tools: Check if Lock Pages in Memory is Enabled on SQL Server

In our free SDU Tools for developers and DBAs, we have a lot of procedures and functions for checking system and database configuration details. One that we were asked for a function for, was the ability to check if the LockPagesInMemory (LPIM) setting was enabled, so we added that.

The function is called IsLockPagesInMemoryEnabled.

It takes no parameters, and returns a bit to indicate if the value is enabled or not.

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

https://youtu.be/QRgAwqSJysg

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

CREATE OR ALTER Function SDU_Tools.IsLockPagesInMemoryEnabled()
RETURNS bit
AS
BEGIN
/* 

-- Function:      IsLockPagesInMemoryEnabled
-- Parameters:    Nil
-- Action:        Checks if LPIM is enabled
-- Return:        bit
-- Refer to this video: https://youtu.be/QRgAwqSJysg
--
-- Test examples: 

SELECT SDU_Tools.IsLockPagesInMemoryEnabled();

*/
    RETURN CASE WHEN EXISTS (SELECT 1 
                             FROM sys.dm_os_process_memory WITH (NOLOCK)
                             WHERE locked_page_allocations_kb > 0)
                THEN CAST(1 AS bit)
                ELSE CAST(0 AS bit)
           END;
END;
GO

2021-02-05