The following sample shows sp_sysmon output for the “Data Cache Management” categories. The first block of data, “Cache Statistics Summary,” includes information for all caches. sp_sysmon reports a separate block of data for each cache. These blocks are identified by the cache name. The sample output shown here includes only the default data cache, although there were more caches configured during the interval.
Data Cache Management
---------------------
Cache Statistics Summary (All Caches)
-------------------------------------
per sec per xact count % of total
--------- --------- ------- ----------
Cache Search Summary
Total Cache Hits 7520.5 524.7 1804925 99.3 %
Total Cache Misses 55.9 3.9 13411 0.7 %
------------------------- --------- --------- -------
Total Cache Searches 7576.4 528.6 1818336
Cache Turnover
Buffers Grabbed 47.1 3.3 11310 n/a
Buffers Grabbed Dirty 0.0 0.0 0 0.0 %
Cache Strategy Summary
Cached (LRU) Buffers 6056.0 422.5 1453437 99.8 %
Discarded (MRU) Buffers 11.4 0.8 2734 0.2 %
Large I/O Usage
Large I/Os Performed 7.3 0.5 1752 49.1 %
Large I/Os Denied 7.6 0.5 1819 50.9 %
------------------------- --------- --------- -------
Total Large I/O Requests 14.9 1.0 3571
Large I/O Effectiveness
Pages by Lrg I/O Cached 55.9 3.9 13424 n/a
Pages by Lrg I/O Used 43.6 3.0 10475 78.0 %
Asynchronous Prefetch Activity
APFs Issued 9.3 0.6 2224 30.1 %
APFs Denied Due To
APF I/O Overloads 0.2 0.0 36 0.5 %
APF Limit Overloads 0.7 0.0 158 2.1 %
APF Reused Overloads 0.4 0.0 100 1.4 %
APF Buffers Found in Cache
With Spinlock Held 0.0 0.0 1 0.0 %
W/o Spinlock Held 20.3 1.4 4865 65.9 %
------------------------- --------- --------- -------
Total APFs Requested 30.8 2.1 7384
Other Asynchronous Prefetch Statistics
APFs Used 8.7 0.6 1819 n/a
APF Waits for I/O 4.0 0.3 965 n/a
APF Discards 0.0 0.0 0 n/a
Dirty Read Behavior
Page Requests 0.0 0.0 0 n/a
----------------------------------------------------------------------
Cache: default data cache
per sec per xact count % of total
------------------------- --------- --------- ------- ----------
Spinlock Contention n/a n/a n/a 24.0 %
Utilization n/a n/a n/a 93.4 %
Cache Searches
Cache Hits 7034.6 490.8 1688312 99.4 %
Found in Wash 2.4 0.2 583 0.0 %
Cache Misses 42.7 3.0 10250 0.6 %
------------------------- --------- --------- -------
Total Cache Searches 7077.3 493.8 1698562
Pool Turnover
2 Kb Pool
LRU Buffer Grab 30.7 2.1 7371 82.0 %
Grabbed Dirty 0.0 0.0 0 0.0 %
16 Kb Pool
LRU Buffer Grab 6.7 0.5 1616 18.0 %
Grabbed Dirty 0.0 0.0 0 0.0 %
------------------------- --------- --------- -------
Total Cache Turnover 37.4 2.6 8987
Buffer Wash Behavior
Buffers Passed Clean 0.3 0.0 64 100.0 %
Buffers Already in I/O 0.0 0.0 0 0.0 %
Buffers Washed Dirty 0.0 0.0 0 0.0 %
Cache Strategy
Cached (LRU) Buffers 5571.9 388.7 1337248 99.8 %
Discarded (MRU) Buffers 11.4 0.8 2732 0.2 %
Large I/O Usage
Large I/Os Performed 6.7 0.5 1614 47.1 %
Large I/Os Denied 7.6 0.5 1814 52.9 %
------------------------- --------- --------- -------
Total Large I/O Requests 14.3 1.0 3428
Large I/O Detail
16 Kb Pool
Pages Cached 53.9 3.8 12928 n/a
Pages Used 42.4 3.0 10173 78.7 %
Dirty Read Behavior
Page Requests 0.0 0.0 0 n/a
