1

I am experiencing deadlocks in Sql Server 2014.

I am aware that we can get total number of deadlocks using performance counters in Sql Server Ref: Number of Deadlocks/sec from https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-locks-object

I would like to know if there is a way to get deadlock count per table?

  • You might be able to do something starting with this query mentioned here – SqlWorldWide Sep 20 '17 at 2:23
2

This is pretty easy to do using the xml_deadlock_report in the system_health Extended Event session.

IF OBJECT_ID('tempdb..[#DeadlockData]') IS NOT NULL
    DROP TABLE #DeadlockData;

WITH xml
AS ( SELECT CAST(event_data AS XML) AS XMLDATA
     FROM   sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL) )
SELECT xml.XMLDATA
INTO   #DeadlockData
FROM   xml
WHERE  xml.XMLDATA.value('(/event/@name)[1]', 'varchar(255)') = 'xml_deadlock_report';


WITH death_indeed
AS ( SELECT ca.x.value('@objectname', 'VARCHAR(256)') AS object_name
     FROM   #DeadlockData AS dd
     CROSS APPLY dd.XMLDATA.nodes('//resource-list/*') AS ca(x) )
SELECT   death_indeed.object_name, COUNT_BIG(*) AS deaths
FROM     death_indeed
GROUP BY death_indeed.object_name

After creating some different deadlocks, I get this back:

object_name                deaths
StackOverflow.dbo.Users    9
StackOverflow.dbo.Posts    9
|improve this answer|||||
  • we did receive count against table name, but there was one row with null object_name as well, why is it so? – Daimi Sep 21 '17 at 4:13
  • @Shifatullah I'm not sure, I've never seen that happen. You'd have to inspect the xml for that row. – Erik Darling Sep 21 '17 at 11:31
  • I'm not familiar with some of the calls your code is making. How far back in time is the code looking for deadlocks? – Randy Minder Sep 21 '17 at 12:30
  • @RandyMinder as far back as what's available in the System Health session. – Erik Darling Sep 21 '17 at 12:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.