MDA tables - queries for data caches

There are three MDA tables available that you can use to monitor the behaviour of the data caches:

• monDataCache - provides statistics for data caches
• monCachePool - provides statistics for the pool defined for a data cache
• monCachedObject - provides statistics for objects and indexes currently present in a data cache.

Here are some sample queries to gather information about the behaviour of your caches.

Show cache hit ratio and writes for all defined caches

This query will show the name of cache, the hit ratio (the percentage of requested pages that were already in cache) and how many modified pages were written out of the cache to disk.

select CacheName,
       convert(numeric(4,1),100-((convert(numeric(12,2),PhysicalReads)/convert(numeric(12,2),CacheSearches))*100)) as "Hit %",
       PhysicalWrites as "Writes"
       from monDataCache
       where   PhysicalReads <= CacheSearches
       order   by CacheName

Sample output

CacheName                      Hit %  Writes     
------------------------------ ------ -----------
default data cache               96.4      266340
summitdb                         84.2     1995251
summitdb syslogs                 98.5      193560
tempdb cache                     99.2     1080319
tempdb2 cache                    98.3      864662

The data returned is for the time the server booted until now. It's much better to run this type of query during a specified interval. Below is a sample stored procedure for this.

Show cache hit ratio and writes for all defined caches during a specified interval

create proc sp_mda_cache @interval char(10)
as

select *
       into #cache_begin
       from master..monDataCache

waitfor delay @interval

select *
       into #cache_end
       from master..monDataCache

select e.CacheName,
       convert(numeric(4,1),100-((convert(numeric(12,2),e.PhysicalReads-b.PhysicalReads)/
         convert(numeric(12,2),e.CacheSearches-b.CacheSearches))*100)) as "Hit %",
       e.PhysicalWrites - b.PhysicalWrites as "Writes"
       from  #cache_end e,
             #cache_begin b
       where e.CacheID = b.CacheID
       and   e.CacheSearches - b.CacheSearches != 0
       order by e.CacheName
go

Sample output:

1> sp_mda_cache "00:10:00"
2> go
 CacheName                      Hit %   Writes     
 ------------------------------ ------- -----------
 STKPoC                           100.0           0
 default data cache               100.0           0
 summitdb                         100.0           0
 tempdb cache                      99.9           0
 tempdb2 cache                     99.9           0

(5 rows affected)
(return status = 0)

Show the utilization of pools within a cache and the number disk reads

This query shows for each pool the effectiveness as a percentage, the number of disk reads and some other statistical info.

select CacheName,
       convert(char(3),IOBufferSize / 1024) + "Kb" as "IO Size",
       convert(numeric(4,1),(((PagesTouched * @@maxpagesize)/1024.) / AllocatedKB) * 100) as "Usage %",
       PhysicalReads,
       Stalls as "Dirty Reads",
       BuffersToMRU,
       BuffersToLRU
       from    master..monCachePool
       order   by CacheName, IOBufferSize

Sample output

CacheName                      IO Size Usage % PhysicalReads Dirty Reads BuffersToMRU BuffersToLRU 
------------------------------ ------- ------- ------------- ----------- ------------ ------------ 
default data cache             2  Kb      85.6         86492           0        84136         2356 
default data cache             16 Kb      76.9        188015           0      1446024        58096
  
(2 rows affected)

The data returned is for the time the server booted until now. It's much better to run this type of query during a specified interval. Below is a sample stored procedure for this.

Show the performance of pools within a cache during a specified interval

create proc sp_mda_cachepool @interval char(10)
as

select *
       into #cache_begin
       from master..monCachePool

waitfor delay @interval

select *
       into #cache_end
       from master..monCachePool

select e.CacheName,
       convert(char(3),e.IOBufferSize / 1024) + "Kb" as "IO Size",
       e.PhysicalReads - b.PhysicalReads as PhysicalReads,
       e.Stalls - b.Stalls as "Dirty Reads",
       e.BuffersToMRU - b.BuffersToMRU as BuffersToMRU,
       e.BuffersToLRU - b.BuffersToLRU as BuffersToLRU
       from    #cache_end e,
               #cache_begin b
       where   e.CacheID       = b.CacheID
       and     e.IOBufferSize  = b.IOBufferSize
       order   by e.CacheName, e.IOBufferSize
go

Sample output

1> sp_mda_cachepool "00:05:00"
2> go
CacheName                                                    IO Size    PhysicalReads Dirty Reads BuffersToMRU BuffersToLRU 
------------------------------------------------------------ ---------- ------------- ----------- ------------ ------------ 
default data cache                                           2  Kb                211           0          211            0 
default data cache                                           16 Kb                  0           0            0            0 

(2 rows affected)
(return status = 0)

What objects and indexes are in cache and how much space do they take

select CacheName,                                                                                                                       
       case when IndexID = 0 then
         convert(char(60),DBName + "." + isnull(OwnerName,"dbo") + "." + ObjectName)
       else
         convert(char(60),DBName + "." + isnull(OwnerName,"dbo") + "." + object_name(ObjectID,DBID) + "." + ObjectName)
       end as "Table / Index",
       CachedKB
       from    master..monCachedObject
       order   by CacheName,2

Sample output:

CacheName                  Table / Index                             CachedKB 
-------------------------- ----------------------------------------- -----------
default data cache         analyzer.dbo.AccessLog                             16
default data cache         analyzer.dbo.AccessLog.i1                          32
default data cache         analyzer.dbo.AccessLog.i2                           2
default data cache         analyzer.dbo.ProcessException                       6
default data cache         analyzer.dbo.ProcessException.i1                    2
default data cache         analyzer.dbo.sysattributes.csysattributes           4

The cache wizard of sp_sysmon

The cache wizard of sp_sysmon also uses the MDA tables. This tool can be used to see if your caches are performing well and which objects are present in cache. Sample invocation:

sp_sysmon "00:10:00","cache wizard"