Saturday, March 19, 2011

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
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.

No comments:

Post a Comment

All suggestions are welcome