Knowledge of which indexes have the most page latch requests and latch waits can be useful when tuning database design and queries, and can also help with identifying I/O latency and the tables most affected.
Warning: This uses sys.dm_db_index_operational_stats, which gets its data from the cached metadata for each object. This is lost when the service restarts, and also at other times - for example if there is memory pressure or an index is rebuilt. Heavily used indexes are likely to remain cached, and so the information in this report should be accurate for the recent past for these indexes.
|What It Shows||Comments|
|Page I/O latches||The cumulative number of page I/O latches requested|
|Page latches||The cumulative number of page latches requested|
|Average page I/O latch wait||A high value here may indicate I/O issues|
|Average page latch wait||A high value here may suggest that a table or group of pages is a hot-spot, with high contention for those pages in the buffer cache|