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

Posted on March 19, 2011, in Locks, Performance Tuning, Sql Server 2005, Sql Server 2008. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: