The following sample shows sp_sysmon output for the “Index Management” categories.
Index Management
----------------
Nonclustered Maintenance per sec per xact count % of total
------------------------- --------- --------- ------- ----------
Ins/Upd Requiring Maint 20.4 1.2 12269 n/a
# of NC Ndx Maint 5.9 0.4 3535 n/a
Avg NC Ndx Maint / Op n/a n/a 0.28812 n/a
Deletes Requiring Maint 20.4 1.2 12259 n/a
# of NC Ndx Maint 5.9 0.4 3514 n/a
Avg NC Ndx Maint / Op n/a n/a 0.28665 n/a
RID Upd from Clust Split 0.0 0.0 0 n/a
# of NC Ndx Maint 0.0 0.0 0 n/a
Upd/Del DOL Req Maint 7.3 0.4 4351 n/a
# of DOL Ndx Maint 4.7 0.3 2812 n/a
Avg DOL Ndx Maint / Op n/a n/a 0.64629 n/a
Page Splits 0.3 0.0 207 n/a
Retries 0.0 0.0 1 0.5 %
Deadlocks 0.0 0.0 0 0.0 %
Add Index Level 0.0 0.0 0 0.0 %
Page Shrinks 0.0 0.0 0 n/a
Index Scans per sec per xact count % of total
------------------------- --------- --------- ------- ----------
Ascending Scans 717.1 43.6 430258 90.6 %
DOL Ascending Scans 74.3 4.5 44551 9.4 %
Descending Scans 0.1 0.0 85 0.0 %
DOL Descending Scans 0.0 0.0 6 0.0 %
--------- --------- -------
Total Scans 791.5 48.1 474900
