View source for 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"

[[Category:ASE]]
[[Category:MDA tables]]