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 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
Enjoy!