1

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)

  • there are a lot of deadlock types collected in the perfmon counter object's instance list. perhaps correlating the deadlocks in system health with those types would help? – swasheck Oct 2 '15 at 21:12
  • ... and i dont think that the 2012 version of system_health suffers from the same awkwardness that the 2008 version(s) did. you can pick and choose from the queries that i have here. keep in mind that the timestamp is UTC gist.githubusercontent.com/swasheck/11379661/raw/… – swasheck Oct 2 '15 at 21:22
1

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;
  • Max - isn't that pretty much the same as using perfmon?? It would guarantee my math was accurate, I suppose. – rottengeek Oct 2 '15 at 21:11
  • It's precisely the same as what Perform shows you since that DMV uses the performance counters that Perfmon uses. However, this will show you precisely without any guessing exactly how many deadlocks just occurred in your system. If you think you are getting 4000 deadlocks / "x" time period, you'll be able to verify with this. – Max Vernon Oct 2 '15 at 21:17
1

From a reliability standpoint of deadlocks per second the perfmon counter will be more accurate. The system_health monitor is based on ring buffers which can overflow over time or miss events if there is enough activity within a brief period in order to facilitate reliability. There is a file that backs this which you can look at which will give you more history but if you are experiencing as many deadlocks as you are indicating there is a distinct possibility that you may not have full history within these. This would explain your discrepancy between the two measurements, you aren't doing anything wrong from what I can see.

The files will be located in your SQL Server install directory under %Program Files%\Microsoft SQL Server\MSSQL11.[Instance Name\MSSQLSERVER]\MSSQL\Log\system_health_*.xel.

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.