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
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
