How can I capture information about a deadlock on my Amazon RDS DB instance that runs on SQL Server?

7 minute read
0

I have an Amazon Relational Database Service (Amazon RDS) DB instance that runs Microsoft SQL Server. I want to get more details about a deadlock on my RDS DB instance.

Short description

Your database instance encounters a deadlock when two or more sessions, each with a resource locked, attempt to access the other session's locked resource. When a deadlock occurs, none of the sessions can continue to run until one of the sessions releases its locks.

To resolve a deadlock, the SQL Server deadlock detector uses either a resource-based or a cost-based mechanism to end one of the sessions. Then, the detector rolls back any modifications related to this session. By default, the SQL Server database engine chooses the session that runs the least expensive transaction to roll back as the deadlock victim. After one of the sessions ends, the locks held by the session are released and the other session is allowed to continue. For more information, see Deadlocks on the Microsoft website.

Resolution

To capture information about a deadlock event on your DB instance, you can use trace flags, a system_health session, or the xml_deadlock_report extended events session.

Activate trace flags

You can activate the deadlock trace flags (1204,1222). You use trace flags to customize SQL Server behavior to capture deadlock information in the SQL Server error logs.

Note: It's a best practice to avoid the use of these trace flags with high performance workload instances that experience deadlocks because this can cause performance issues. Create an extended event session instead to capture deadlock event information.

  • Trace flag 1204 provides deadlock information about each of the nodes involved in the deadlock.
  • Trace flag 1222 provides more detailed deadlock information than trace flag 1204 in XML format.

For more information, see Trace Flag 1204 and Trace Flag 1222 on the Microsoft website.

Activate both trace flags to get two different representations for each deadlock event through a custom parameter group in RDS for SQL Server. To set this up, see How can I receive a notification when a deadlock event occurs on my Amazon RDS for SQL Server DB instance? After you activate the trace flags, you can review the SQL Server error logs for more information on the deadlock event.

Use the system_health session

You can use extended events with Amazon RDS for SQL Server to collect data to monitor and troubleshoot SQL Server issues. The system_health extended events session is included in SQL Server and activated by default. The session starts automatically when your SQL Server database engine starts and collects basic server health information. You can use this information to troubleshoot performance issues and monitor deadlocks within your database engine. For more information, see Use the system_health session on the Microsoft website.

The system_health extended event session uses two targets, event file and ring buffer, to store the data. The ring buffer stores data on a first-in-first-out (FIFO) basis. The target memory of the ring buffer can't exceed 4 MB in Amazon RDS for SQL Server. Therefore, on a busy instance, the system_health session might rotate events.

By default, the size of each individual file is 5 MB and the number of maximum rollover files is 4. This adds up to 20 MB of system_health extended event data. For SQL Server 2016, 2017, and 2019, the size of individual files is 100 MB and the maximum number of files is increased to 10. This adds up to 1 GB of data.

Use the system_health session to retrieve information about a deadlock on your DB instance with either Microsoft SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL).

To use SSMS to retrieve deadlock information, complete the following steps:

  1. Open SSMS.
  2. On Object Explorer, choose Management, and then choose Extended Events.
  3. Choose Sessions.
  4. Find system_health session, and then choose (double-click) package0.event_file to open the extended event file.
  5. After the file contents load, on the SSMS menu, choose Extended Events.
  6. Choose Filters.
  7. In the Filter window, do the following:
    For Field, select name.
    For Operator, select Contains.
    For Value, select deadlock.
  8. Choose OK. You can view the events with deadlocks.
  9. Choose the event that you want to view, and then choose the Deadlock tab to view the graph.

To use T-SQL to retrieve deadlock information, complete the following steps:

  1. Run a query similar to the following to view the list of deadlocks:

    SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraphFROM (
        SELECT XEvent.query('.') AS XEvent
        FROM (
            SELECT CAST(target_data AS XML) AS TargetData
            FROM sys.dm_xe_session_targets st
            INNER 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 source;
  2. Choose the deadlock XML output to open the XML file in a new window.

  3. Use the .xdl file extension to save the XML. This action converts the XML into a graphical format.

  4. Navigate to the file location, and then open the .xdl file in SSMS to view the deadlock graph.

To retrieve deadlock information from ring_buffer, run the T-SQL query. The ring_buffer target holds event data in memory. This information is available only as long as the instance isn't rebooted. When you reboot, this information is purged.

Use an xml_deadlock_report extended events session

To create an extended event session, select the xml_deadlock_report event to capture deadlocks. To save the events to the file for analysis, select an event file as the target. To create an extended event session, you can use either SSMS or T-SQL.

To use SSMS to create an extended event session, complete the following steps:

  1. Open SSMS.
  2. On Object Explorer, choose Management, and then choose Extended Events.
  3. Choose (right-click) Sessions, and then choose New Session Wizard.
  4. For Session name, enter the name of your session, and then choose Next.
  5. On the Choose Template page, select Do not use a template.
  6. Choose Next to open the New Session Wizard page.
  7. From Event library, select xml_deadlock_report, and then choose Next.
  8. In the Capture Global Fields page, select values that are common to all events.
    Note: Select sql_text field to see the query that caused the deadlock.
  9. Choose Next.
  10. In the Set Session Event Filters page, create event filters to limit the data that you want to capture.
  11. Choose Next.
  12. In the Specify Session Data Storage page, select Save data to a file for later analysis and Work with only the most recent data.
  13. Choose Finish.

You can now see your new session in the Sessions folder in SSMS. Choose (right-click) the session, and then choose Start session.

To use T-SQL to create an extended event session, run a query similar to the following:

CREATE EVENT SESSION [Deadlock_detection] ON SERVER ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'D:\rdsdbdata\Log\Deadlock',max_file_size=(100))
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
-- Start the event session
ALTER EVENT SESSION Deadlock_detection ON SERVER
STATE = start;
GO

To use SSMS to retrieve the deadlock information, complete the following steps:

  1. Open SSMS.
  2. On Object Explorer, choose Management, and then choose Extended Events.
  3. Choose Sessions.
  4. Find the extended event session that you created earlier, and then choose (double-click) package0.event_file to open the extended event file.
  5. After the file contents load, choose the event that you want to view, and then choose the Deadlock tab to view the graph.

To use T-SQL to view the list of deadlocks, run a query similar to the following:

SELECT * FROM sys.fn_xe_file_target_read_file('d:\rdsdbdata\log\deadlock*.xel', null, null, null)

Related information

Deadlocks guide (on the Microsoft website)

KB4541132 - Improvement: Size and retention policy are increased in default XEvent trace system_health in SQL Server 2016, 2017 and 2019 (on the Microsoft website)

Monitor deadlocks in Amazon RDS for SQL Server and set notifications using Amazon CloudWatch