Index Operations
1.Index data aggregated to database level
2.Index data aggregated to table level
3.Index data. If the index is partitioned then the data is aggregated to the index.
Grid Bands
there are 10 grid bands, see Purpose column to show information required.
Purpose |
Band |
Column |
Description |
|---|---|---|---|
Index |
Name |
Tree |
|
Index Type |
Index Type- at table level shows base index type |
||
Index Details |
Keys |
Index keys |
|
Filegroup name |
Filegroup name |
||
Filegroup Type |
Filegroup Type |
||
Primary key |
show |
||
Unique |
show |
||
Unique Constraint |
show |
||
Disabled |
show |
||
Fill Factor |
fill factor |
||
Leaf Operations |
Insert Count |
cumulative count of leaf level inserts |
|
Update Count |
cumulative count of leaf level updates |
||
Delete Count |
cumulative count of leaf level deletes. Deleted records ghosted first are not counted |
||
Non Leaf Operations |
Insert Count |
cumulative count of inserts above leaf level |
|
Update Count |
cumulative count of updates above leaf level |
||
Delete Count |
cumulative count of deletes above leaf level |
||
Row lock |
Locking Activity |
Row Lock Count |
Number of row locks requested |
Blocking per database object |
Row Lock Wait Count |
Number of times database engine waited on row lock |
|
Row Lock Wait MS |
Total milliseconds database engine waited on row lock |
||
Page lock |
Page lock Count |
Number of page locks requested |
|
Page Lock Wait Count |
Number of times database engine waited on page lock |
||
Page Lock Wait MS |
Total milliseconds database engine waited on row lock |
||
Promotion Attempt Count |
Number of times database engine tried to escalate locks |
||
Promotion Count |
Number of times database engine escalated locks |
||
Promotion Count Percent |
Percent success of escalated locks |
||
Page Splits |
Leaf Count |
Leaf level page allocations in index/heap. For indexes page allocation =page split |
|
Non leaf Count |
Page split above leaf level. Heap or columnstore=0 |
||
Access Activity |
Range Count |
Range and table scans on index/heap |
|
Singleton Count |
Single row retrieval on index/heap |
||
Forward Fetch Count |
Rows fetched through forwarding record. Indexes=0 |
||
sort descending For Row/page lock view Locking activity band For insert/update/delete view leaf operations band |
Latching Activity |
Page Latch Wait |
Number of times database engine waited on latch contention |
Page latch Wait MS |
Total milliseconds database engine waited latch contention |
||
sort descending For Row/page lock view Locking activity band For insert/update/delete view leaf operations band |
Page IO Latch Wait |
Number of times database engine waited I/O page latch |
|
Page IO Latch Wait MS |
Total milliseconds database engine waited I/O page latch |
||
Page Compression |
Compression Attempt Count |
Number of pages evaluated for Page level compression |
|
Compression Success Count |
Number of pages compressed using Page compression |
||
Size |
Data Size |
Data size |
|
Index Size |
Index Size |
||
Total Size |
Total size= data + index size |
||
Row Count |
Row Count |
