Sample output

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
    LRU 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