SQL Server deadlock notifications – Part 2

In the previous part of this article, we’ve described a way of notifying a database administrator when a SQL Server deadlock occurs by using SQL Server Management Studio and SQL Server Agent. As it can be seen, setting the SQL Deadlock alerting in such way requires an additional level of knowledge as well as multiple actions performed. Even after the alerting is set, a database administrator will be required to perform additional actions in order to identify the deadlock and the victim.

In this part, we will present a solution that can alert on a deadlock and provides deadlock details in a user-friendly GUI with just a few mouse clicks

ApexSQL Monitor deadlock alert setup

ApexSQL Monitor is a SQL Server and system performance monitoring tool that provides a set of operating system, SQL Server, and database performance metrics in real time on multiple local and remote machines and SQL Servers and helps you identify performance issues and deadlocks.

Performance metrics are configurable. For each metric you can specify whether to trigger an alert when a critical value is reached, as well as three threshold values for low, medium, and high severity levels and alerting frequency. ApexSQL Monitor tracks query execution and query wait statistic and provides enough information to detect the ones that use the most resources or that causes the bottlenecks

Besides being able to track the deadlocks per second metric, ApexSQL Monitor allows a database administrator to receive notification about deadlocks, regardless of when they occur or how many. ApexSQL Monitor provides the deadlocked alert for encountered deadlock and additional details via the deadlock XML (.xdl) file

The deadlock per second metric is enabled by default in ApexSQL Monitor. ApexSQL Monitor features a built in deadlock alert action profile, that is designed to identify each and every deadlock occurred after the deadlock alert is triggered. The Deadlock info alert action profile will be executed on each Deadlocks per second metric alert, and will create a deadlock XML graph file for each detected deadlock in the C:\ProgramData\ApexSQL\ApexSQLMonitor\ActionResult folder

To set the Deadlock info alert action profile:

  1. Start ApexSQL Monitor
  2. Select Metrics from the Configure toolbar
  3. In the SQL Server instances pane, select the server instance where tracking and identifying deadlocks is required (to set for all instances at once, select All instances)
  4. In the Metrics tab, navigate to the SQL Server metricsDeadlocks per second
  5. Ensure that the Deadlocks per second metric is enabled as well as the Alerting checkbox is checked.
  6. From the Alert action profile dropdown menu, select the ApexSQL deadlock info profile
  7. Press Apply

All the requirements are now fulfilled for successful deadlock detection and identification. Now let’s see how this will work on the real world example with additional information on how the ApexSQL Deadlock info alert action can be additionally customized to suit the individual requirements

How it works

To demonstrate how this works, we will use a simple deadlock scenario. First we need to create the following tables:

USE TestDB
GO

 CREATE TABLE Employees (
    EmpId INT IDENTITY,
    EmpName VARCHAR(16),
    Phone VARCHAR(16)
)
GO

INSERT INTO Employees (EmpName, Phone)
VALUES ('Martha', '800-555-1212'), ('Jimmy', '619-555-8080')
GO

CREATE TABLE Suppliers(
    SupplierId INT IDENTITY,
    SupplierName VARCHAR(64),
    Fax VARCHAR(16)
)
GO

INSERT INTO Suppliers (SupplierName, Fax)
VALUES ('Acme', '877-555-6060'), ('Rockwell', '800-257-1234')
GO

After the tables are created, we need to execute following SQL statements by using two sessions. Open two SQL Server Management Studio Query tabs for the TestDB database and execute the queries in the following way:

In the query tab 1 execute the following script

BEGIN TRAN;                 

UPDATE Suppliers
SET Fax = N'676-1019'
WHERE supplierid = 1

In the query tab 2 execute 
BEGIN TRAN;                 

UPDATE Employees
SET EmpName = 'Brian'
WHERE empid = 1

Now again in the query tab 1 execute 
UPDATE Employees
       SET phone = N'555-9999'
       WHERE empid = 1
COMMIT

And in the query tab 2 execute 
UPDATE Suppliers
SET Fax = N'676-1019'
WHERE supplierid = 1

COMMIT

As a result we will face the following deadlock error:

Msg 1205, Level 13, State 45, Line 10
Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The ApexSQL Monitor Deadlocks per second metric is designed to display the number of deadlocks occurred between the two metrics reading. If a deadlock has occurred between the two readings, the alert will be triggered, which will execute the ApexSQL Deadlock Info action.

Deadlock info - Alert action

The core of this predefined action is the script wrapped in the batch file which, when executed, collects necessary information about each deadlock, processes this information and stores the deadlock XML graph in individual files for each deadlock

(for /f "tokens=2 delims==” %y in (‘wmic os get localdatetime /format:list’) do (set datetime=%y) && for /l %x in (1, 1, %AlertValue%) do (sqlcmd -S %ServerFullName% -h-1 -r1 -y 0 -Q "SET ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING ON; SET NOCOUNT ON ; SELECT TOP 1 M.DeadlockGraph FROM ( SELECT TOP %x XEvent.query(‘(event/data/value/deadlock)[1]’) AS DeadlockGraph, Creation_Date FROM ( SELECT XEvent.query(‘.’) AS XEvent, XEvent.value(‘@timestamp’,’datetime’) as Creation_Date FROM ( 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 ORDER BY Creation_Date DESC ) as M ORDER BY M.Creation_Date ASC;" -o %DefaultPath%\%Server%_Deadlocks_%x_!datetime:~0,4!-!datetime:~4,2!-!datetime:~6,2!_!datetime:~8,2!-!datetime:~10,2!-!datetime:~12,2!.XDL))

Note that for SQL Server 2008 version, a modified command should be used:

(for /f “tokens=2 delims==” %y in (‘wmic os get localdatetime /format:list’) do (set datetime=%y) && for /l %x in (1, 1, %AlertValue%) do (sqlcmd -S %ServerFullName% -h-1 -r1 -y 0 -Q “SET ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING ON; SET NOCOUNT ON ; SELECT TOP 1 M.DeadlockGraph FROM ( SELECT TOP %x CAST(src.XEvent.value(‘(event/data/value)[1]’,’varchar(max)’) as XML) AS DeadlockGraph, Creation_Date FROM ( SELECT XEvent.query(‘.’) AS XEvent, XEvent.value(‘@timestamp’,’datetime’) as Creation_Date FROM ( 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 ORDER BY Creation_Date DESC ) as M ORDER BY M.Creation_Date ASC;” -o %DefaultPath%\%Server%_Deadlocks_%x_!datetime:~0,4!-!datetime:~4,2!-!datetime:~6,2!_!datetime:~8,2!-!datetime:~10,2!-!datetime:~12,2!.XDL))

The script within the batch file relies on the SQL Server built in extended event system health event that can log the deadlock information for a target instance. The script utilizes the following built in tags, of which some can be changed or replaced to allow more flexibility:

%AlertValue% – This tag must be left as it is. At the moment of execution it will be replaced by the number of occurred deadlocks during the period set in the Metrics tab

%ServerFullName% – It will insert the fully qualified name of the server where the deadlock occurred

%DefaultPath% – The predefined default path where the deadlock graph xdl file will be saved. The default path is C:\ProgramData\ApexSQL\ApexSQLMonitor\ActionResult. This tag can be replaced with the adequate path to the folder where the deadlock graph will be saved

%Server% –The standard server name that will be the part of the xdl file name. It helps user to easily identify which deadlock file is related to which SQL Server instance.

The deadlock xml file title is constructed from the SQL Server name where deadlock is detected and the exact date and time the deadlock was triggered in form ServerName_ YYYY-MM-DD_HH:MM:SS.xdl

After opening the generated .xdl file, it is clearly visible what query was chosen by SQL Server to be the deadlock victim

This presents a graphical depiction of the tasks and resources involved in a deadlock. If more details are needed, simply change the file extension of the .xdl file generated by ApexSQL Monitor to .xml and open the XML document in SQL Server Management Studio. Even more convenient way to analyze the deadlock information stored by ApexSQL Monitor is by using the Microsoft XML Notepad, which doesn’t require a change to the file extension. It will display substantially more information needed for resolving complex deadlock issues, than the graphically conveyed view, so advanced users might prefer working with the XML presentation because of the additional information it provides.

The deadlock XML file contains the following information that is essential for deadlock troubleshooting:

  • The victim node contains the process ID of the query victim killed by SQL Server.
  • The process-list node contains at least two process sub-nodes. Each process involved in the deadlock will be presented in its own process node.
  • The resource-list node contains at least two keylock sub-nodes. Keylock sub-nodes covers the information about resources owned by the deadlocked processes.

Analyzing the extended information provided in XML form will give you enough data, in most cases, to troubleshoot the deadlock.

Even for advanced users, detecting and fixing deadlocks isn’t an easy job, but ApexSQL Monitor can make it easier

March 2, 2015