SQL SERVER – sys.dm_os_sys_info and Lock Pages in Memory

Yesterday I got a call from my client whom I have consulted earlier for Comprehensive Database Performance Health Check and we had fixed their performance issues by SQL SERVER – Enable Lock Pages in Memory LPIM. This time DBA called me as he wanted to know if how to confirm that lock pages in memory are enabled for the SQL Server instance. Let us learn about it today.

SQL SERVER - sys.dm_os_sys_info and Lock Pages in Memory LPIN0-800x192

Here is the T-SQL Script which you can run and figure out if the LPIM is enabled for your SQL Server or not.

SELECT sql_memory_model, sql_memory_model_desc
FROM sys.dm_os_sys_info

Here is the result you will get if you have disabled the LPIM.

SQL SERVER - sys.dm_os_sys_info and Lock Pages in Memory LPIN1

If you have enabled LPIM for your system, you will get the following result.

SQL SERVER - sys.dm_os_sys_info and Lock Pages in Memory LPIN2

Well, it is that simple. If you are running SQL Server on Windows, you may want to check if this setting is enabled or not. In general, I have noticed that when LPIM is enabled for the instance, it overall performs better than other instances. However, in my career, I have also seen a few cases when this particular setting also gives poor performance. The ration of good vs bad is just very high, hence, I am still recommending my clients about LPIM.

Setting up LPIM is very easy. You can easily do that by going to the local group policy editor and enabling the Lock Pages for the same username which is actually running SQL Server. You can learn more about it by going to here: SQL SERVER – Enable Lock Pages in Memory LPIM.

SQL SERVER - sys.dm_os_sys_info and Lock Pages in Memory LPIN3

Lock Pages in Memory can help improve your system’s performance in most cases. There is a small catch as well – If you enable this feature, there are chances that it may also negatively impact your performance if not set up properly by reducing the amount of available RAM for your system.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Memory, SQL Server, SQL Server Security, Starting SQL
Previous Post
SQL SERVER – Queries Waiting for Memory Grant – Performance Tuning
Next Post
SQL SERVER – Single Table Scan for Multiple Aggregated Operators

Related Posts

Leave a Reply