Number of deadlocks since last SQL Server restart

26 November,2013 by Jack Vamvas

Question: How can I view the amount of deadlocks , which occurred on a SQL Server instance , since the last SQL Server restart? I’m looking to use this number as a sign to dig deeper into code running and identify details of the deadlock.

I’ve read SQL Server - How to detect and troubleshoot blocking and deadlocks , which outlines the two main scenarios of blocking – i.e Blocking and permanent blocks aka deadlocks.

In the article , there are some methods of troubleshooting the deadlock scenario and considerations for the application and how to manage the sql code to minimise blocking , but there aren’t any details about the numbers of deadlocks since the restart , particularly if there hasn’t been a trace running

Answer: There is a Windows Performance Monitor , Object = “SQLServer:Locks” and Counter=” Number of Deadlocks/sec”.

This counter presents the number of lock requests escalating as deadlocks. Based on your question I assume , you’ll use this figure as part of Health Report to  trigger some deeper analysis.

Just remember – it doesn’t mean there is a problem right at this point , it is possible those deadlocks occurred at an earlier time since the last SQL Server restart.

To  gain some more clues , use the default trace – and search on the category_id = 4 and tra ce_event_id = 59. Read more on the default trace on default trace faq

The default trace is useful  - but most servers are set to rollover the files and so the information can become unavailable quickly . To drill into the deadlocks and identify the queries which are part of the deadlock  scenario, use the methods in How to detect and troubleshoot blocking and deadlocks  to trap the deadlock information.

Once you have the query details , work with the developers on how to identify the relevant transaction. In 99.9% of the deadlock cases I’ve investigated , the underlying problems relate to either the order of the transactions, long running transactions, or very loose transaction boundaries – as identified by BEGIN TRANS and COMMIT TRANS.

This query returns the number of  locks which turned into deadlocks. It is for the _Total amount. The _Total amount is made up of a number of categories , for example, it may be : Key, RID, Database,AllocUnit,HoBT,Metadata,Application,Extent,Page,Object,File

 

--Total amount
SELECT cntr_value AS Number_of_deadlocks
  FROM sys.dm_os_performance_counters
 WHERE object_name = 'SQLServer:Locks'
   AND counter_name = 'Number of Deadlocks/sec'
   AND instance_name = '_Total'

--all categories 
SELECT cntr_value AS Number_of_deadlocks
  FROM sys.dm_os_performance_counters
 WHERE object_name = 'SQLServer:Locks'
   AND counter_name = 'Number of Deadlocks/sec'


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Number of deadlocks since last SQL Server restart


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer