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.

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.

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

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.

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)