Lesson Learned #424:Detecting and Notifying Deadlocks in Azure SQL Managed Instance
Published Sep 08 2023 10:28 AM 2,544 Views

Deadlocks in any database system can be a performance nightmare, leading to transactions getting blocked and ultimately terminated. Azure SQL Managed Instance is no different. Thankfully, with Extended Events and Database Mail, we can monitor and promptly react to such occurrences.

 

Today, we got a new service request that our customer request to have an example how to detect a deadlock and receive an email with the details. I would like to share an example, please, feel free to customize this code.

 

1. Setting up Extended Events to Capture Deadlocks

Extended Events is a lightweight performance monitoring system that allows us to gather detailed information about specific events occurring within SQL Server.

Here's how to set up a session to capture deadlocks:

 

 

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='Deadlock_capture')
DROP EVENT SESSION Deadlock_capture ON SERVER;
GO

CREATE EVENT SESSION Deadlock_capture ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
(
    ACTION(
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_name,
        sqlserver.sql_text,
        sqlserver.username
    )
)
ADD TARGET package0.ring_buffer
(
    SET max_events_limit=1000
)
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=OFF
)
GO

 

 

2. Storing the Last Check Timestamp

To ensure we only capture new deadlock events, we'll keep track of the last time we checked for deadlocks:

 

 

CREATE TABLE dbo.LastDeadlockCheck (
    LastCheck DATETIME2
);
INSERT INTO dbo.LastDeadlockCheck VALUES (SYSDATETIME());

 

 

3. Script to Fetch and Email New Deadlocks

The script below fetches details of any new deadlocks since the last check and sends them via email:

 

 

DECLARE @lastCheck DATETIME2;
DECLARE @mailBody NVARCHAR(MAX) = ''
SELECT @lastCheck = LastCheck FROM dbo.LastDeadlockCheck;

WITH Deadlocks AS (
    SELECT 
        event_data.value('(@timestamp)[1]', 'datetime2') as Timestamp,
        event_data.query('.') as DeadlockGraph
    FROM (
        SELECT 
            CAST(target_data AS xml) as TargetData
        FROM sys.dm_xe_sessions AS s 
        JOIN sys.dm_xe_session_targets AS t 
            ON s.address = t.event_session_address
        WHERE s.name = 'Deadlock_capture' AND t.target_name = 'ring_buffer'
    ) as Data
    CROSS APPLY TargetData.nodes('RingBufferTarget/event') AS XE(event_data)
    WHERE event_data.value('@name', 'varchar(4000)') = 'xml_deadlock_report'
    AND event_data.value('(@timestamp)[1]', 'datetime2') > @lastCheck
)
SELECT @mailBody = @mailBody + 'Timestamp: ' + CONVERT(NVARCHAR(50), Timestamp) + CHAR(13) + CHAR(10) + CONVERT(NVARCHAR(MAX), DeadlockGraph) + CHAR(13) + CHAR(10)
FROM Deadlocks;


IF (@mailBody <> '') 
BEGIN
EXEC msdb.dbo.sp_send_dbmail
     = 'AzureManagedInstance_dbmail_profile', -- Reemplaza con tu perfil de Database Mail
    @recipients = 'XXXX@YourDomain.com', 
    @subject = 'Deadlock Alert',
    @body =  @mailBody;
END

UPDATE dbo.LastDeadlockCheck SET LastCheck = SYSDATETIME();

 

 

4. Automating the Check

Use SQL Server Agent or a similar scheduler to run the above script every 5 minutes.

 

Conclusion

Monitoring deadlocks is crucial to maintain the health and performance of a database. With Azure SQL Managed Instance, Extended Events, and Database Mail, we're equipped with the tools to detect and act upon deadlock occurrences efficiently.

 

Disclaimer

 

As always, this script shared it is an example and it's recommended to test scripts in a controlled environment before implementing them in production. We shall not be liable for any direct, indirect, incidental, or consequential damages arising out of the use or inability to use the provided scripts and methods.

 

Articles related

Lesson Learned #98: Is possible to create an extended event on the server in Azure Managed Instance?...

Lesson Learned #19: How to obtain the deadlocks of your Azure SQL Database or Managed Instance? - Mi...

 

Enjoy!

 

Version history
Last update:
‎Sep 08 2023 10:36 AM
Updated by: