The last few months I’ve been taking a number of online & in person SQL Server training classes & meetups, living in New England there are several options within a couple of hours drive to attend SQL Saturdays.

Going over the training and some recent patching of my MySQL environment has reaffirmed a that there is overlap between SQL Server & MySQL, even if the tools may vary.  Capturing Deadlocks for example!

MySQL

On the MySQL side, the most recent deadlock can be viewed using SHOW ENGINE INNODB STATUS.  Downside is that depending on the volume of your deadlocks, that gives you no history.  To address this, I use a combination of tools from Percona:

1. The Deadlock Logger from the Percona Toolkit to capture deadlocks on a regular basis and log them to a table within my database.
   • Install the toolkit using your standard process
   • For the initial setup, I create a new tools database & tools user for this setup
   • The below block will create the percona.deadlocks table if it does not exist, and run the logger in daemon mode, checking every 60 seconds.

     pt-deadlock-logger h=<hostname>,u=perconaadm --ask-pass --dest D=percona,t=deadlocks  --create-dest-table --daemonize --interval 60s

     • Additional options notes
       • –ask-pass will cause a prompt for the password to be entered twice, password will not be visible in process list.
       • –run-time 5m – This will run the tool in Daemon mode for 5 minutes, then exit.
       • –create-dest-table is optional (if you’ve already created the table!)
   • I have scripted this out to check the daemon is running.
2. The pmp-check-mysql-deadlocks Nagios plugin from Percona Monitoring Plugins to send the team a Nagios alert if a deadlock occurs.
   • Install the plugins using your standard process
   • We have an additional user created with permissions to read the deadlocks table
   • Configure check in Nagios

SQL Server

On the SQL Server side, deadlocks (xml_deadlock_report) may be captured in the Extended Events system_health session(2012+), but depending on the volume of events, these could be overwritten.  An extended event specific to capture xml_deadlock_report specifically can be created to have a longer history.

View Deadlocks under system_health:

1. In SQL Server Management Studio, expand Management –> Extended Events –> Sessions –> system_health.
2. Right click on package0.event_file, choose “View Target Data…”
3. Click “Filters” on the Extended Events toolbar, and filter for:
   1. Field: Name, Value: xml_deadlock_report
   2. Click OK to apply filter
   3. 
4. The deadlock graph is viewable under the Deadlock tab: 

A SQL Server Agent alert could be considered to enabled alerts when deadlock thresholds are met (@performance_condition=N’Locks|Number of Deadlocks/sec|Database|>|0′), but we use an outside monitoring tool for our notifications.