Sql-server – How many deadlocks am i really getting? system health or deadlocks/sec

deadlocksql serversql-server-2012windows-server

I'm trying to look at deadlocks on a troublesome server, and when I look at the system_health xel file, I see 80 records for "xml_deadlock_report" in about 16 hours. I assume these are all different deadlocks?

Perfmon and system_health don't really seem to match up.

  1. which is more reliable, perfmon deadlocks/sec (hours * 60 * 60) * deadlocks/sec = @ 4000 or the 80 rows in the xml deadlock report? (I'm viewing the system_health xel via enterprise manager 2014)
  2. is my method for the math totally wrong? I realize it is a generalization, but, really…that's a lot.

SQL 2012 Standard Edition
Windows 2012

I'm also using the following from Jon Kehayias
http://www.sqlservercentral.com/articles/deadlock/65658/

but it doesn't give me the time, and although the post is old I imagine it is pulling from the same .xel file as the system_diag so is missing events (as he mentions, if I'm reading his posts correctly)

Best Answer

Presuming your system is actually experiencing a high number of deadlocks, you could use the following query to see exactly how many actual deadlocks your server is experiencing during the next "x" amount of time:

DECLARE @startval BIGINT;
DECLARE @endval BIGINT;

SELECT @startval = pc.cntr_value
FROM sys.dm_os_performance_counters pc
WHERE pc.counter_name LIKE '%deadlock%'
    AND pc.instance_name = '_total';

-- 00:01:00 is 1 minute
-- adjust this to whatever period of time you like
WAITFOR DELAY '00:01:00';

SELECT @endval = pc.cntr_value
FROM sys.dm_os_performance_counters pc
WHERE pc.counter_name LIKE '%deadlock%'
    AND pc.instance_name = '_total';


SELECT DeadlocksOverPeriod = @endval - @startval;
Related Question