How Much Memory is in Locked Pages? – Interview Question of the Week #228

Question: How Much Memory is in Locked Pages?

Answer: Before we see the answer to this question, I suggest you read the blog post here first which I had written last year. What is Lock Pages In Memory? – Interview Question of the Week #186

When enabled, locked pages allows accounts to keep data in physical memory, instead of paging it to virtual memory on disk. In simple words, Lock Pages in Memory can help improve your system’s performance in most cases.

Here is the script which can help you to identify if the locked pages are enabled or not for your database and if enabled how much memory it uses.

SELECT
(physical_memory_in_use_kb/1024) Physical_Memory_Used_MB,
(locked_page_allocations_kb/1024) Locked_Pages_Used_MB,
page_fault_count Page_Fault_Counts,
memory_utilization_percentage Memory_Utilization_Percentage
FROM sys.dm_os_process_memory

If you see locked_page_allocations_kb value to zero, in that case, this setting is not enabled. You can enable this setting following this blog post What is Lock Pages In Memory? – Interview Question of the Week #186.

Here are a few additional blog posts related to SQL Server Memory

• SQL SERVER – Script to Identify Memory Used By Each Database
• SQL SERVER – Difference TempTable and Table Variable – TempTable in Memory a Myth
• PowerShell – Get Maximum Memory of Server with dbatools
• SQL SERVER – Faster Application Performance with In-Memory OLTP
• SQL SEVER – Finding Memory Pressure – External and Internal

If you have any better script to identify memory used in the query, please write them in the comments section and I will publish with due credit to you.

Here are a few recent interview questions and answers which you may find it interesting as well.

• How to List All Memory-Optimized Tables in SQL Server? – Interview Question of the Week #227
• When was Table Accessed Last By User? – Interview Question of the Week #226
• When Was SQL Server Last Restarted? – Interview Question of the Week #225
• How to Execute Query Without Using F5 or Query Toolbar in SSMS? – Interview Question of the Week #224

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