sys.dm_os_memory_cache_hash_tables – Day 25 – One DMV a Day

Hello Geeks and welcome to the Day 25 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.

Till now we have covered most of the memory related DMVs. since yesterday I am talking about very less know or less used DMVs. This is a keep it light on Monday morning DMV. 🙂 Today I am going to cover sys.dm_os_memory_cache_hash_tables. We know we have memory caches. But what is the physical structure of my cache?

Memory caches are saved in the form of hash tables. Concept of hash tables is simple. A value is searched in a data structure by examining elements in data structure through different algorithms. But hash table saves values by hashing them with a hash function. So when searching a value you just need to apply the hash function to get to that index. Hash tables are the best implementation for data that needs to be searched in memory. It is fast and efficient. You can read more about hashing concept here.

Sys.dm_os_memory_cache_hash_tables gives out one row for each active cache in SQL Server. This is again an independent DMV. The output is again straight forward.

SELECT name, 
	 buckets_count, 
	 buckets_in_use_count, 
	 buckets_avg_length,
	 hits_count,
	 misses_count
FROM sys.dm_os_memory_cache_hash_tables
WHERE type = 'CACHESTORE_SQLCP' OR type = 'CACHESTORE_OBJCP'
ORDER BY buckets_count DESC

sys.dm_os_memory_cache_hash_tables

I have filtered only two types of cache, CACHESTORE_SQLCP and CACHESTORE_OBJCP. You can run sys.dm_os_memory_cache_hash_tables without filters and get more information. The more significant columns are few in sys.dm_os_memory_cache_hash_tables. One use case is to determine reason for bad performance. Your buckets_avg_length is very large and buckets_in_use_count is very less. This means you need to scan more to get less. This is a performance hit for SQL Server engine while executing.

So you can now easily say which cache can create performance problems using sys.dm_os_memory_cache_hash_tables. Tomorrow I will be covering another memory related DMV. 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 →

Leave a Reply

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