SQL and Deadlocks – What, where and when are they exactly?

Working on for a customer where we were having some issues with some deadlocks. We couldn’t quite get the details on the events for a subset of reasons. Foremost, because the SQL Ring buffer events was recycled within one and a half hour during peak loads.
So we created a powershell script, which was set to run as a scheduled task, taking all deadlock events in the ring buffer and pairing it with relevant queries, one hour backwards.

Basically this script, run as a scheduled task every hour, will output all your deadlocks, for the past hour. If your system is not generating that much Ring buffer events, you can throttle it down or even just run the SQL query once in order to collect this data. Its just important to note, that these Ring buffer events will be overwritten by the good grace of the SQL server.

# Get todays date in order to stamp the output file correctly.
$Date = (Get-date).ToString("yyyy-MM-dd")

# The selected SQL instance to connect to
$SQLInstance = '<Servername>\<Instance>'
$Outputfile= "<Local folder location>"+$date+"Deadlocks.log"

# SQL Query to be performed
$SQLQuery = $("
  (SELECT xevent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph,
          CAST (xevent.value('(event/@timestamp)[1]', 'nvarchar(30)') AS DATETIME) AS FullDate
     (SELECT xevent.query('.') AS XEvent
        (SELECT Cast(target_data AS XML) AS TargetData
         FROM sys.dm_xe_session_targets st
         JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
         WHERE s.NAME = 'system_health'
           AND st.target_name = 'ring_buffer') AS DATA CROSS apply targetdata.nodes ('RingBufferTarget/event[@name=""xml_deadlock_report""]') AS XEventData (xevent)) 
              AS src
WHERE RESULT.FullDate >= Dateadd(HOUR, -1, Getdate());

# Collect deadlock data every hour, using MaxCharLenght to get full output back. 
$DeadlockDataFromSQL=Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $SQLInstance -MaxCharLength 10000 -QueryTimeout 0

# Output exception data to file using stream.
$stream = New-Object -TypeName System.IO.StreamWriter $OutPutFile, $true
foreach($line in $deadlockdataFROMSQL) 
      	$stream.WriteLine(($line.DeadlockGraph | Out-string))

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.