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

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