sys.dm_os_memory_cache_entries – Day 24 – One DMV a Day

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, AS cacheName, 
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,

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


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.