NOTE: If you do not know what deadlocks are or how to fix them you probably won't find this useful.
Yes. It is possible to use QIE to monitor for deadlocks on MSSql Server. However, there are some things to consider first.
1. Security. To monitor deadlocks on MSSql server you need a login that has permissions to query the Extended Events. You should create a zone with limited access to put the database connection and channel.
2. Production versus Test. It is always a good idea to have a test environment that can be used to find deadlocks before they end up in production. Consider how monitoring a production system might affect performance etc.
3. Finding deadlocks with Extended Events may have some quirks. For example I have struggled with the following: The ring buffer XML may be truncated, differences between 2008/2008R2 and newer versions, bugs in 2008/2008R2 (my advice: don't waste time apply all updates or upgrade).
4. You should really do your own research and talk to your DBA. To keep this short I will not go into MS Sql Server related details.
Okay, let's do it.
First, create the database connection to the server to be monitored. Remember the name of the connection to be used in the script below. In this example it is called "TestDb"
Next create a channel with a mapping node with this custom script:
------------------- Begin Script -------------------
//This code will send an email with a list of deadlocks that occured since the last time this code was executed.
//Get the "To" email address from a channel cache
var sendToEmail = channelCache.getValue('sendToEmail', 'test@test.com');
//Get the date of the last time we ran this check
var dateLastCheckedForDeadLocks = channelCache.getValue('DateLastCheckedForDeadLocks', '20180101000000000');
//Build the query
var deadlockQuery = "" +
"--========================================\n" +
"-- DEAD LOCK QUERY for EXTENDED EVENTS \n" +
"-- For 2008(R2) - ServicePack 2 or greater\n" +
"--========================================\n" +
"SELECT\n" +
" EventTime,\n" +
" DeadLockGraphXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@hostname)[1]', 'nvarchar(max)') AS HostName,\n" +
" DeadLockGraphXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@clientapp)[1]', 'nvarchar(max)') AS ClientApp,\n" +
" DB_NAME(DeadLockGraphXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@currentdb)[1]', 'nvarchar(max)')) AS DatabaseName,\n" +
" DeadLockGraphXML.value('(//process[@id=//victim-list/victimProcess/@id]/@spid)[1]', 'nvarchar(max)') AS ProcessId,\n" +
" DeadLockGraphXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@transactionname)[1]', 'nvarchar(max)') AS VictimTransactionName,\n" +
" DeadLockGraphXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@isolationlevel)[1]', 'nvarchar(max)') AS IsolationLevel,\n" +
" DeadLockGraphXML,\n" +
" DeadlockEventXML\n" +
"FROM\n" +
"(\n" +
" SELECT\n" +
" DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), XEvent.value('@timestamp', 'datetime2')) AS EventTime,\n" +
" XEvent.query('.') AS DeadlockEventXML,\n" +
" CAST(XEvent.value('(data/value)[1]', 'varchar(max)') as XML) as DeadLockGraphXML\n" +
" FROM\n" +
" (\n" +
" SELECT CAST(target_data AS XML) AS TargetData\n" +
" FROM sys.dm_xe_session_targets AS xst\n" +
" INNER JOIN sys.dm_xe_sessions AS xs\n" +
" ON xs.address = xst.event_session_address\n" +
" WHERE xs.name = N'system_health'\n" +
" AND xst.target_name = N'ring_buffer'\n" +
" ) AS XML_Data\n" +
" CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name=\"xml_deadlock_report\"]') AS XEventData(XEvent)\n" +
") AS DeadlockInfo\n" +
"WHERE EventTime >= '" + qie.formatDate('yyyy-MM-dd HH:mm:ss.SSS', dateLastCheckedForDeadLocks) + "'\n" +
"ORDER By EventTime DESC";
qie.info(deadlockQuery);
//Run the query
var queryResult = qie.doQuery("TestDb", deadlockQuery);
//Start the email body. We will add deadlocks to it in the loop below
var body = "The deadlocks below occured since the last time we checked on: ." + dateLastCheckedForDeadLocks + "<br /><br />";
//Create an attachmentMap to hold the deadlock graph XML.
var attachmentMap = qie.newEmailAttachmentMap();
//Loop over the results
for (var i=0 ; i < queryResult.getRowCount() ; i++) {
//Grab the pieces we are interested in from the results
var eventTime = queryResult.getNode("EventTime", (i + 1));
var hostName = queryResult.getNode("HostName", (i + 1));
var clientApp = queryResult.getNode("ClientApp", (i + 1));
var databaseName = queryResult.getNode("DatabaseName", (i + 1));
var processId = queryResult.getNode("ProcessId", (i + 1));
var deadLockGraphXml = queryResult.getNode("DeadLockGraphXML", (i + 1));
var deadLockFileName = 'DeadLockGraph' + qie.formatDate('yyyyMMdd_') + StringUtils.leftPad((i + 1), 3, '0') + '.xml';
//Add the deadlock details to the body
body += 'Event Time: ' + eventTime + '<br />';
body += 'Host Name: ' + hostName + '<br />';
body += 'Client App: ' + clientApp + '<br />';
body += 'Database Name: ' + databaseName + '<br />';
body += 'Process Id: ' + processId + '<br />';
body += 'See attached deadlock graph for the queries responsible: ' + deadLockFileName + '<br />';
body += '<br /><br />';
//Create an XMLMessage so we can format the deadlock XML for better readability
message = qie.createXMLMessage(deadLockGraphXml);
message.formatXML(4);
var formatedDeadLockGraphXml = message.getNode('/');
//Add formatted deadlock graph XML to the attachmentMap
qie.addEmailAttachment(attachmentMap, deadLockFileName, formatedDeadLockGraphXml.getBytes());
}
//Send the email
qie.sendEmail(sendToEmail, "Deadlocks detected", body, attachmentMap);
//Update the date this check was completed.
channelCache.setValue('DateLastCheckedForDeadLocks', qie.getSystemDate());
------------------- End Script -------------------
You well need to setup your source node, destination, and chanel caches to finish this but, I hope this is enough for you to evaluate if this is an approach that will work for you.
