sys.dm_os_memory_pools – Day 22 – One DMV a Day

Hello Geeks and welcome to the Day 22 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Yesterday we have covered sys.dm_os_memory_objects. We will proceed further with more memory related DMVs. Today I will talk about sys.dm_os_memory_pools. Memory in SQL Server is designed on pool framework. Pool framework is similar to cache framework.

The memory clerks are internally memory pools. Pools are used in places where clerks are used. There are certain pools which are commonly used by multiple components. They are used to cache homogeneous and stateless types of data. Sys.dm_os_memory_pools provides a row each for each memory pool.

Sys.dm_os_memory_pools can be queried directly. The use case would be to determine if you have any problems with bad caching of memory. The important columns to notice are max_free_entries_count, free_entries_count and removed_in_all_rounds_count.

Max_free_entries_count – The max limit of free entries allowed in a pool
Free_entries_count – Current free entries in the pool
Removed_in_all_rounds_count – Number of entries removed since SQL Server started from the pool

The problems with memory caching can be identified by high number in removed_in_all_rounds_count and consistent low number in free_entries_count. As I mentioned before, sys.dm_os_memory_pools is more independent DMV.

SELECT type,
	 name,
	 max_free_entries_count,
	 free_entries_count,
	 removed_in_all_rounds_count
FROM sys.dm_os_memory_pools
ORDER BY removed_in_all_rounds_count DESC

sys.dm_os_memory_pools

In the above output we see OBJECTSTORE_LOCK_MANAGER is on top of list with more entries removed. But it would not concern me as I will be more interested if the free entries count is closer to 0. If I have problems with this cache I may receive an error like below.

The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

Sys.dm_os_memory_pools is very less used but useful DMV to give a glance at how your memory pools are loaded and caching problems with memory. Tomorrow I will be covering the next step in troubleshooting memory issues using DMVs. So, stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

3 Comments on “sys.dm_os_memory_pools – Day 22 – One DMV a Day”

    1. Hi Mehdy. Thanks for your interest in the series. It is easier and more available on the blogs. Currently we do not have any printable version of this. But point taken.

      Regards,
      Manu

Leave a Reply

Your email address will not be published. Required fields are marked *