Simple query to check the recent blocking history
SQL Server 2005 keeps (some) history of the number and time that blocking occurred on indexes. This blog contains a query that queries this information to help identifying on which tables blocking happens most. The query shows accumulated numbers (no. of blocks, wait time, etc) since SQL Server was last restarted.
The query does have some known in-accuracies. Also, the result needs to be interpreted: It may not point directly at which indexes need changing or disabling. See below for details about this.
The query uses Dynamic Management Views, which means it will only work from SQL Server 2005 and later.
Make sure to run the query in the NAV database, otherwise you won’t see the table names.
So, here it is:
–Use [NAV-Database]
select
db_name(database_id) DB,
object_name
(object_id) Obj,
–row_lock_count, page_lock_count,
row_lock_count
+ page_lock_count No_Of_Locks,
–row_lock_wait_count, page_lock_wait_count,
row_lock_wait_count
+ page_lock_wait_count No_Of_Blocks,
–row_lock_wait_in_ms, page_lock_wait_in_ms,
row_lock_wait_in_ms
+ page_lock_wait_in_ms Block_Wait_Time,
index_id
from
sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL)
order
by Block_Wait_Time desc
–order by No_Of_Blocks desc
Inaccuracies:
No_Of_Blocks is recorded accurately, but the Block_wait_time is not. SQL Server only records Block_wait_time when the block is a clear pagelock or rowlock. It will not record wait_time in case of a rangelock, which is common in NAV. Also, Block_Wait_Time only gets recorded when a transaction completes. So if a transaction is aborted after the block (for example by Lock-Timeout), then Block_Wait_Time for that transaction will not be counted. This means that the real Block_Wait_Time is likely to be higher, and it may be distributed on different tables / indexes than the query shows. Anyway, I hope the query is still accurate enough to give a good idea about where blocking occurs.
How to interpret the result:
The query shows blocking per index. But you should not put too much importance in the individual index that shows blocking. Instead, look if there may be other indexes in that table which are not used. Remember, an update on Index X may require update on all other indexes in the table. So look at the whole table when deciding if the table is over-indexed. For this, use the query “Index Usage, which shows you the usage of each index in a table.
Blocking will happen on the first table in a process which is blocked. Maybe the process begins processing small tables, but the real blocking happens because processing of other tables takes a long time. In that case, the query will show the blocking on the first table, but not show blocking on the later tables, which may be where the real problem is.
I will be more than happy to receive any feedback on experiences with this query, and suggestions for how to improve it!
