This post is for me. It’s a script I find useful so I’m putting it in a place where I know to go look for it, my blog. You may find it useful too.
The script below extends the DMV sys.dm_db_index_operational_stats by focusing on lock waits and supplying index and table names. If you want to know about blocking by index, these queries can help.
If you want something more comprehensive, I’d suggest Kendra Little’s http://www.brentozar.com/blitzindex/
Blocking Wait Stats
-- Get index blocking wait stats select t.name as tableName, i.name as indexName, ios.row_lock_wait_count, ios.row_lock_wait_in_ms, ios.page_lock_wait_count, ios.page_lock_wait_in_ms from sys.dm_db_index_operational_stats(db_id(), null, null, null) ios join sys.indexes i on i.object_id = ios.object_id and i.index_id = ios.index_id join sys.tables t on ios.object_id = t.object_id where ios.row_lock_wait_in_ms + ios.page_lock_wait_in_ms > 0 order by ios.row_lock_wait_in_ms + ios.page_lock_wait_in_ms desc
Create Snapshot Of Stats
begin try drop table #IndexBlockingWaitStats end try begin catch -- swallow error end catch select [object_id], index_id, row_lock_wait_count, row_lock_wait_in_ms, page_lock_wait_count, page_lock_wait_in_ms into #IndexBlockingWaitStats from sys.dm_db_index_operational_stats(db_id(), null, null, null)
Get Waits Since Last Snapshot
-- Get delta results select t.name as tableName, i.name as indexName, ios.row_lock_wait_count - iossnapshot.row_lock_wait_count as row_lock_wait_count, ios.row_lock_wait_in_ms - iossnapshot.row_lock_wait_in_ms as row_lock_wait_in_ms, ios.page_lock_wait_count - iossnapshot.page_lock_wait_count as page_lock_wait_count, ios.page_lock_wait_in_ms - iossnapshot.page_lock_wait_in_ms as page_lock_wait_in_ms from sys.dm_db_index_operational_stats(db_id(), null, null, null) ios join #IndexBlockingWaitStats iossnapshot on iossnapshot.[object_id] = ios.[object_id] and iossnapshot.index_id = ios.index_id join sys.indexes i on i.[object_id] = ios.[object_id] and i.index_id = ios.index_id join sys.tables t on ios.[object_id] = t.[object_id] cross apply ( select ( ios.row_lock_wait_in_ms + ios.page_lock_wait_in_ms ) - ( iossnapshot.row_lock_wait_in_ms + iossnapshot.page_lock_wait_in_ms ) ) as calc(totalwaittime) where totalwaittime > 0 order by totalwaittime desc
- There are many kinds of lock waits, this script focuses on waits on pages or rows. Other kinds of waits not shown here include objects (i.e. locks on tables), latches and IO latches.
- This is only one small focused tool in a troubleshooting tool belt. Don’t depend on it too much
- If you’re keen, you’ll notice I didn’t give info on schemas or on partitions, sounds like a fun exercise doesn’t it?
- No illustration? Nope, or at least not yet. If I continue to find this script useful, then I plan on adding an illustration, because I use Browse By Illustration as my main navigation tool