Hello Geeks and welcome to the Day 24 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_ cache_counters. Today we are going to drill down more into the memory caches. Have you ever wondered when my plan for an object is going to be flushed? How much cost did it take to make an entry into the cache? How many users are using my cache entry at this time? How much space is my entry using up in the cache? Yes, we can check all these. Sys.dm_os_memory_cache_entries.
Let us run the below query and all your above questions will be self-answered. For better understanding, let us limit this to the plan cache. Most of the cases you will be interested in this specific cache. In addition to sys.dm_os_memory_cache_entries I will be using two more DMVs, sys.dm_exec_cached_plans and sys.dm_exec_sql_text to make it simpler and get sensible output. I will cover these DMVs in later blogs in this series.
SELECT TOP 10 OBJECT_NAME(est.objectid, EST.dbid) AS ObjectName, omce.name AS cacheName, omce.in_use_count, omce.is_dirty, omce.disk_ios_count, omce.context_switches_count, omce.original_cost, omce.current_cost, omce.pages_kb FROM sys.dm_exec_cached_plans ecp CROSS APPLY sys.dm_exec_sql_text(ecp.plan_handle) est INNER JOIN sys.dm_os_memory_cache_entries omce ON ecp.memory_object_address = omce.memory_object_address
From the above output we can tell the below details from sys.dm_os_memory_cache_entries for each object.
name (CacheName) – Name of the cache in which we have this entry
in_use_count – Current parallel usage of this cache entry
is_dirty – Will be flushed the next time when memory is needed form the cache
disk_ios_count – Number of IOs when this entry is created
context_switches_count – Number of context switches when this entry is created
original_cost – Total cost including IO, CPU, memory, etc. during entry. Higher the cost lower the chances of flushing it.
current_cost – current cost of cache entry. This is updated during entry purging. If the plan is reused before flushing it gets reset to original cost.
pages_kb – Amount of space consumed by entry. Till 2008 R2 this was pages_allocated_count which is the page count of allocations.
I should warn you before using the above query. It will be a heavy query if you do not add right filters. So narrow down what you want to check when using the above query. Add the right WHERE clause to target a single cache or plan or proc.
By using sys.dm_os_memory_cache_entries you will be able to answer all the questions asked in the beginning of this post. 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
