Lesson Learned #424:Detecting and Notifying Deadlocks in Azure SQL Managed Instance

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? - Microsoft Community Hub

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

Enjoy!

Continue reading...