How to configure and use deadlock monitoring (4218941)

Applies to

Summary

Description

The article will address the following questions:

• How does ApexSQL Monitor collects SQL Server deadlocks data?
• What is the difference between the ApexSQLMonitorDeadlock extended event and system_health extended event?
• How frequently is the deadlock information collected? (What is the sampling frequency?)
• What permissions are needed for using extended events?
• Does the application use the SQL Server Trace to collect the deadlocks data?
• What is the overhead that ApexSQlMonitorDeadlock extended event can cause on the monitored SQL Server?
• Can I see the script of the ApexSQlMonitorDeadlock?
• How can I set the deadlock monitoring and deadlock event alerts?
• Will ApexSQL Monitor configure ApexSQLMonitorDeadlock extended events automatically when deadlock monitoring is enabled?
• Where can I see the details of collected deadlocks?
• Can I see a collected deadlocks graph?
• What are the Deadlocks metric and does it behaves as any regular SQL Server metric?
• Will ApexSQlMonitorDeadlock extended events remain on the monitored instances when I uninstall the application?
• How can I start or stop ApexSQlMonitorDeadlock extended event manually if needed?

How does ApexSQL Monitor collects SQL Server deadlocks data?

ApexSQL Monitor SQL Server deadlock monitoring is entirely based on using SQL Server Extended Events. To meet the various user preferences, ApexSQL Monitor utilizes two different approaches in the SQL Server deadlocks monitoring: using the SQL Server native system_health extended event or using the ApexSQL Monitor’s dedicated ApexSQLDeadlock extended event. See more information on the ApexSQLDeadlock extended event below

What is the difference between the ApexSQLMonitorDeadlock extended event and system_health extended event?

• The system health extended event – default option

  The system_health extended event is SQL Server native extended event that, among other information, collects data about the SQL Server deadlocks. This is not the most reliable method for collecting the deadlocks, and it cannot always be guaranteed that all deadlocks are captured or that deadlocks event might be available with severe delay. To elaborate, the maximum memory assigned to the ring buffer is 4MB. Therefore, in the highly active servers it is possible that a large amount of events is 0.5MB in size which limits the available space in the system_health extended event ring_buffer for the deadlock events that are serialized to the XML format (serialized size can be over 4.5MB) so it doesn’t show up in the XEL (event file format) file up until it has been buffered out. The reason is that it either reaches the max_memory or max_events limit. The max_memory limit is set to 4Mb while the max_event limit is set to 5000 events for the system_health session
  
  While not being the most reliable due to the previously explained SQL Server limitations it cannot always guarantee the collected deadlock data precision; it can still provide enough information in most cases. However, if it is allowed by the company policy, it is highly recommended to use the dedicated ApexSQLDealock extended event
  

• The ApexSQLDeadlock extended event
  This is a customized extended event dedicated to deadlocks collection. When enabled, ApexSQL Monitor will create ApexSQLDeadlock extended event on the monitored SQL Server. As being a dedicated extended event explicitly designed for deadlocks monitoring, it doesn’t have any limitations that affect the system_health extended event. Therefore, when enabled, it allows capturing of all deadlocks with minimal delay, and it is not affected by the server activity level.

How frequently is the deadlock information collected? (What is the sampling period?)

ApexSQL Monitor reads data from the selected extended event every 60 seconds. This is a hardcoded setting that cannot be changed by the user. The time period is chosen as it grants the most optimal and precise reading, especially from the system_health extended event. Utilizing specific programming techniques, in combination with the predefined reading frequency, allows us to collect even the deadlocks that are significantly delayed as a consequence of the SQL Server system_health extended event limitations explained above and thus maximizing the precision

What permissions are needed for using extended events?

The user designated for monitoring of SQL Server instance must have the following permissions that allow creating, enabling, disabling and deleting ApexSQLMonitorDeadlock extended event:

• For SQL Server 2008 and 2008R2 - the CONTROL SERVER permission must be granted.
• For SQL Server 2012 and newer – the ALTER ANY EVENT SESSION permission must be granted.

Does the application use the SQL Server Trace to collect the deadlocks data?

No. ApexSQL Monitor doesn’t use SQL Server Trace for deadlocks monitoring. What’s more, the application doesn’t use SQL Server Trace at all, and therefore there's no overhead on monitored SQL Server that would be inevitable with use of SQL Server Trace

What is the overhead that ApexSQLDeadlock extended event can cause on the monitored SQL Server?

As being a dedicated extended event designed to collecting only SQL Server deadlocks, it doesn’t cause any measurable overhead. What’s more, it even provides lower footprint comparing to system_health extended event as queries used by the ApexSQL monitor to retrieve data can be simpler and faster since there is no need to filter out unneeded or not relevant data from the system_health extended event. Reading data from the ApexSQLMonitoDeadlock extended event is straight and without any filtering as the extended event collects only the data needed for deadlock monitoring

Can I see the script of the ApexSQLMonitorDeadlock extended event?

Yes. ApexSQLMonitorDeadlock extended event can be easily scripted from SSMS. For those interested in details about the ApexSQLMonitorDeadlock extended event, the script is provided below

CREATE EVENT SESSION [ApexSQLDeadlock] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(sqlserver.database_id))
ADD TARGET package0.event_file(SET filename=N'ApexSQLDeadlock.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

How can I set the deadlock monitoring and deadlock event alerts?

ApexSQL Monitor deadlock monitoring and alerting are enabled by default using the system_health extended event.

To access the deadlock monitoring configuration page

1. Select the SQL Server, group or All instances in the server explorer in the left pane, depending on what you want to configure for deadlock monitoring
2. Select the Configuration link in the main menu and then select the Deadlocks tab
   
   
   
   

   Use Enable monitoring checkbox to enable or disable deadlocks monitoring

   Select the radio button to switch the deadlock monitoring method between the system_health and ApexSQLDeadlock

   Use the Enable alerting checkbox to turn on or off alerting for collected deadlocks

Will ApexSQL Monitor configure ApexSQLMonitorDeadlock extended events automatically when deadlock monitoring is enabled?

Yes. ApexSQL Monitor handles the ApexSQLMonitordeadlock extended event on its own. It can create, remove, disable or enable ApexSQLMonitorDeadlock according to the actions performed by the user within the application

• Selecting the ApexSQLMonitorDeadlock extended event in the configuration page will create an extended event in one or multiple monitored SQL Servers automatically (depending on whether the configuration is performed over the single server or group of servers)
• Switching from the ApexSQLMonitorDeadlock extended event to system_health extended event will remove the ApexSQLMonitorDeadlock extended event from one or multiple monitored SQL Servers automatically (depending on whether the configuration is performed over the single server or group of servers)
• Disable/enable monitoring of SQL Server will automatically disable/enable the ApexSQLMonitorDeadlock extended event on that SQL Server

Where can I see the details of collected deadlocks?

To access the deadlocks page;

• Open the Instance dashboard for the SQL Server that should be inspected for deadlocks
• Select the Deadlocks(xx) link in the SQL Server section. A number between the parenthesis indicates the number of deadlocks occurred on that SQL Server
  
  
  
  

The deadlocks page is now displayed

By default, deadlocks are sorted according to the date and time when they occur, in descending order

Can I see a collected deadlocks graph?

Yes. The application collects and stores the deadlock information in the repository database as XDL files. The XDL file for each deadlock can be downloaded using the Download link for the desired deadlock in the Deadlocks column where deadlocks are listed.



The XDL file default naming schema is <Servername>_<databasename>_<Deadlock_Date_Time>.xdl

The saved XDL file can be opened using the SSMS

What is the Deadlocks metric and does it behaves as any regular SQL Server metric?

ApexSQL Monitor displays the number of monitored deadlocks over the time in the form of the metrics chart in the SQL Server metrics page. The Deadlocks chart shows the number of deadlocks collected for the sampling period. It can be used and configured as any other metric

Will ApexSQLMonitorDeadlock extended events remain on the monitored instances when I uninstall the application?

No. Uninstalling the application will remove ApexSQLMonitorDeadlock extended event from all monitored SQL Server instances

How can I stop and start ApexSQLMonitorDeadlock extended event manually if needed?

That can be done using the SSMS

To stop ApexSQLMonitorDeadlock extended event session

• In the SSMS Object Explorer, navigate to Management -> Sessions folder and expand it
• Locate the ApexSQLMonitorDeadlock
• Right click on it and select the Stop Session in the context menu
  
  

Use the same method to start the previously stopped session

To do the same using the T-SQL, use the following scripts:

• To stop the session:
  
  

  ALTER EVENT SESSION [ApexSQLDeadlock] ON SERVER 
  WITH (STARTUP_STATE=OFF)
  GO
  

• To start the session:
  
  

  ALTER EVENT SESSION [ApexSQLDeadlock] ON SERVER 
  WITH (STARTUP_STATE=ON)
  GO