Site icon

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

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

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 = '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 = '', @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.



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.




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




Exit mobile version