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
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!
Posted by