SQL Server: How to Analyze Blocking and Deadlocking

In response to an early post Difference Between Locking, Blocking and Dead Locking , I have received few mails where I was asked “How to analyze this blocking and dead locking phenomenon”.

I like to use performance monitor counters to check the frequency of blocking and dead locking. You can find these counters by selecting SQL Server: Locks. Three counters under this group are very useful.

1. Lock Timeouts/sec
2. Lock Wait Time (ms)
3. Deadlocks/sec

Out of these three counters first two are used to analyze blocking. Value for “Lock Timeouts/sec” should be zero (0) and “Lock Wait Time (ms)” must also be very low. If you are observing nonzero value for “Lock Timeouts/sec” and continuous high value for “Lock Wait Time (ms)”, then there is excessive blocking occurring. Your long running queries can cause this blocking. Use profiler or sys.dm_exec_query_stats to identify such culprit queries.

None zero values “Deadlocks/sec” counter is an indication of deadlocks. Value for this counter must always be zero.

We can also use following query

SELECT *

FROM    sys.dm_os_performance_counters

WHERE object_name = 'SQLServer:Locks' 

AND instance_name = '_Total'

AND counter_name IN ('Lock Waits/sec','Lock Wait Time (ms)','Number of Deadlocks/sec')

Do you have any better idea ? Please do share with us.