In my previous articles we see about index usage statistics and index physical statistics, have a look at missing indexes too
Now we have a clear picture on the below things,
Moving ahead, we would like to know what is going on behind the scene, with our indexes. Sometimes we may have healthy indexes, but still puzzled with performance. On such cases we may we get more details from the below DMV.
sys.dm_db_index_operational_stats.
This DMV will give us a crystal clear info about what is happening with our indexes on the operation. Even though the scope of this DMV is very wide, we will look into the below areas in this article.
- Capturing page io latch
- Capturing locking and blocking at the row level.
- Capturing latch waits
- Capturing lock escalation
- Indexes associated with contention
