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

How Much Memory is in Locked Pages? - Interview Question of the Week #228 lockedpages-800x210

Solarwinds

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

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.

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

Solarwinds
, , ,
Previous Post
How to List All Memory Optimized Tables in SQL Server? – Interview Question of the Week #227
Next Post
How Dirty or Clean is SQL SERVER’s Memory? – Interview Question of the Week #229

Related Posts

Leave a Reply

Menu