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 39472.3 185026.5 11841696 99.9 % Total Cache Misses 33.8 158.4 10139 0.1 % ----------------------- ------------ ------------ -------- Total Cache Searches 39506.1 185184.9 11851835 Cache Turnover Buffers Grabbed 177.0 829.6 53097 n/a Buffers Grabbed Dirty 0.0 0.0 0 0.0 % Cache Strategy Summary Cached (LRU) Buffers 35931.1 168427.1 10779332 98.6 % Discarded (MRU) Buffers 511.7 2398.4 153500 1.4 % Large I/O Usage Large I/Os Performed 46.9 219.9 14071 94.5 % Large I/Os Denied due to Pool < Prefetch Size 2.5 11.6 745 5.0 % Pages Requested Reside in Another Buffer Pool 0.3 1.3 80 0.5 % ----------------------- ------------ ------------ -------- Total Large I/O Requests 49.7 232.8 14896 Large I/O Effectiveness Pages by Lrg I/O Cached 171.3 803.1 51398 n/a Pages by Lrg I/O Used 567.2 2658.7 170157 331.1 % Asynchronous Prefetch Activity APFs Issued 9.6 45.0 2878 1.2 % APFs Denied Due To APF I/O Overloads 0.0 0.0 0 0.0 % APF Limit Overloads 0.0 0.0 0 0.0 % APF Reused Overloads 0.0 0.0 0 0.0 % APF Buffers Found in Cache With Spinlock Hel 1.7 7.8 501 0.2 % W/o Spinlock Held 787.1 3689.5 236128 98.6 % ----------------------- ------------ ------------ -------- Total APFs Requested 798.4 3742.3 239507 Other Asynchronous Prefetch Statistics APFs Used 9.4 43.9 2808 n/a APF Waits for I/O 6.8 31.8 2034 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 2.8 % Utilization n/a n/a n/a 82.9 % Cache Searches Cache Hits 32731.6 153429.6 9819492 100.0 % Found in Wash 288.4 1351.7 86508 0.9 % Cache Misses 10.9 50.9 3257 0.0 % ----------------------- ------------ ------------ ---------- Total Cache Searches 32742.5 153480.5 9822749 Pool Turnover 2 Kb Pool LRU Buffer Grab 123.0 576.7 36908 92.5 % Grabbed Dirty 0.0 0.0 0 0.0 % 16 Kb Pool LRU Buffer Grab 10.0 47.1 3012 7.5 % Grabbed Dirty 0.0 0.0 0 0.0 % ----------------------- ------------ ------------ ---------- Total Cache Turnover 133.1 623.8 39920 Buffer Wash Behavior Statistics Not Available - No Buffers Entered Wash Section Yet Cache Strategy Cached (LRU) Buffers 30012.1 140681.9 9003640 98.3 % Discarded (MRU) Buffers 511.7 2398.4 153500 1.7 % Large I/O Usage Large I/Os Performed 10.0 47.1 3012 80.2 % Large I/Os Denied due to Pool < Prefetch Size 0.8 3.9 247 6.6 % Pages Requested Reside in Another Buffer Pool 1.7 7.8 498 13.3 % ----------------------- ------------ ------------ ---------- Total Large I/O Requests 12.5 58.7 3757 Large I/O Detail 4 Kb Pool Pages Cached 0.0 0.0 0 n/a Pages Used 0.0 0.0 0 n/a 16 Kb Pool Pages Cached 80.3 376.5 24096 n/a Pages Used 76.1 356.5 22817 94.7 % Dirty Read Behavior Page Requests 0.0 0.0 0 n/a ----------------------------------------------------------------------------- Cache: pub_log_cache per sec per xact count % of total ---------- ----------- ---------- ------- Spinlock Contention n/a n/a n/a 0.2 % Utilization n/a n/a n/a 9.1 % Cache Searches Cache Hits 3591.9 16837.1 1077574 100.0 % Found in Wash 0.0 0.0 0 0.0 % Cache Misses 0.0 0.0 0 0.0 % ----------------------- ------------ ------------ ---------- Total Cache Searches 3591.9 16837.1 1077574 Pool Turnover 2 Kb Pool L RU Buffer Grab 0.3 1.3 80 0.8 % Grabbed Dirty 0.0 0.0 0 0.0 % 4 Kb Pool LRU Buffer Grab 33.9 158.9 10171 99.2 % Grabbed Dirty 0.0 0.0 0 0.0 % ----------------------- ------------ ------------ ---------- Total Cache Turnover 34.2 160.2 10251 Buffer Wash Behavior Statistics Not Available - No Buffers Entered Wash Section Yet Cache Strategy Cached (LRU) Buffers 2872.7 13465.9 861817 100.0 % Discarded (MRU) Buffers 0.0 0.0 0 0.0 % Large I/O Usage Large I/Os Performed 33.9 158.9 10171 99.2 % Large I/Os Denied due to Pool < Prefetch Size 0.0 0.0 0 0.0 % Pages Requested Reside in Another Buffer Pool 0.3 1.3 80 0.8 % ----------------------- ------------ ------------ ---------- Total Large I/O Requests 34.2 160.2 10251 Large I/O Detail 4 Kb Pool Pages Cached 67.8 317.8 20342 n/a Pages Used 67.8 317.8 20342 100.0 % Dirty Read Behavior Page Requests 0.0 0.0 0 n/a ----------------------------------------------------------------------------- Cache: tempdb_data_cache per sec per xact count % of total ---------- ----------- ---------- ------- Spinlock Contention n/a n/a n/a 0.0 % Utilization n/a n/a n/a 8.0 % Cache Searches Cache Hits 3148.8 14759.8 944630 99.3 % Found in Wash 7.9 37.0 2365 0.3 % Cache Misses 22.9 107.5 6882 0.7 % ----------------------- ------------ ------------ ---------- Total Cache Searches 3171.7 14867.4 951512 Pool Turnover 2 Kb Pool LRU Buffer Grab 6.8 31.8 2038 69.7 % Grabbed Dirty 0.0 0.0 0 0.0 % 4 Kb Pool LRU Buffer Grab 0.1 0.4 24 0.8 % Grabbed Dirty 0.0 0.0 0 0.0 % 16 Kb Pool LRU Buffer Grab 2.9 13.5 864 29.5 % Grabbed Dirty 0.0 0.0 0 0.0% ----------------------- ------------ ------------ ---------- Total Cache Turnover 9.8 45.7 2926 Buffer Wash Behavior Statistics Not Available - No Buffers Entered Wash Section Yet Cache Strategy Cached (LRU) Buffers 3046.3 14279.3 913875 100.0 % Discarded (MRU) Buffers 0.0 0.0 0 0.0 % Large I/O Usage Large I/Os Performed 3.0 13.9 888 100.0 % Large I/Os Denied due to Pool < Prefetch Size 0.0 0.0 0 0.0 % Pages Requested Reside in Another Buffer Pool 0.0 0.0 0 0.0 % ----------------------- ------------ ------------ ---------- Total Large I/O Requests 3.0 13.9 888 Large I/O Detail 4 Kb Pool Pages Cached 0.2 0.8 48 n/a Pages Used 0.2 0.7 47 97.9 % 16 Kb Pool Pages Cached 23.0 108.0 6912 n/a Pages Used 2.9 13.5 864 12.5 % Dirty Read Behavior Page Requests 0.0 0.0 0 n/a