Knowledge of which indexes have the most lock requests and lock waits can be useful when tuning database design and queries.
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 locks||The cumulative number of page locks requested|
|Row locks||The cumulative number of row locks requested|
|Page lock waits||The number of times the database engine had to wait on a page lock|
|Row lock waits||The number of times the database engine had to wait on a row lock. The number in brackets is the average wait in milliseconds|
|Average lock||This is the total amount of time spent waiting on page or row locks divided by the sum of all page and row locks, and is given in micro-seconds. Any large numbers here should ring alarm bells.|