We have some mild table locking issue during our peak hours so I figured the following query query taken from this site would be a good start. And boy, does it show me some seriously concerning statistics:

SELECT OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name
,i.name as index_name
,CAST(100. * row_lock_wait_count / NULLIF(row_lock_count,0) AS decimal(6,2)) AS row_block_pct
,CAST(1. * row_lock_wait_in_ms / NULLIF(row_lock_wait_count,0) AS decimal(12,2)) AS row_avg_lock_wait_ms
FROM sys.dm_db_index_operational_stats (7, NULL, NULL, NULL) ios
INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id
WHERE OBJECTPROPERTY(ios.object_id,'IsUserTable') = 1
ORDER BY row_lock_wait_count + page_lock_wait_count DESC, row_lock_count + page_lock_count DESC

enter image description here

Some of the indices in the database, particularly IX_SRCHGT_3 and IX_SRCHGT_4 seems to be causing huge rowlocks, but I have absolutely no clue what these big numbers mean. What are their values relative to? Is it statistics based on when the indices were created?

Should I be deathly worried about my potentially wrongly placed indices? How do I prevent this from happening?

  • Bit of info on sys.dm_db_index_operational_stats in BOL. These columns indicate whether there is lock contention on the index or heap, and the significance of the contention. I'd start by looking at some of the queries targeting those tables. Feb 16, 2015 at 13:49


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.