Azure SQL数据仓库死锁排查：如何获取死锁图分析进程？

Hey there, let's dig into this deadlock issue you're hitting in Azure SQL Data Warehouse—even though you're sure no other external processes were accessing your test table, deadlocks can sometimes come from unexpected places, so getting the deadlock graph will definitely help you get to the bottom of it. Here's how to retrieve and analyze those graphs:

1. Use Azure Monitor Logs (Quickest for Cloud-Based Monitoring) #

Head over to your Azure SQL Data Warehouse resource in the Azure Portal, then navigate to Monitoring > Logs under the Insights section. Run this Kusto query to pull up deadlock events:

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
| where Category == "SQLDeadlocks"
| project TimeGenerated, deadlockGraph = parse_json(DeadlockGraph)

The deadlockGraph column will hold the XML-formatted deadlock data. You can expand this to see every detail: which processes were involved, what locks they held, and which resources triggered the deadlock.

2. Query System Views Directly via T-SQL #

Azure SQL Data Warehouse stores deadlock details in system views. Run this query to fetch recent deadlock events:

SELECT 
    deadlock_xml,
    event_time
FROM 
    sys.dm_pdw_errors
WHERE 
    error_code = 1205 -- This is the deadlock error code
ORDER BY 
    event_time DESC;

Copy the deadlock_xml content, then open SQL Server Management Studio (SSMS). Create a new query window, paste the XML, and go to Query > Display Estimated Execution Plan > Deadlock Graph to visualize it in a more readable format.

3. Set Up Extended Events for Continuous Monitoring #

If you need to track deadlocks over time, set up an extended events session to capture them automatically:

CREATE EVENT SESSION [DeadlockMonitor] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'deadlock_events.xel')
WITH (STARTUP_STATE=ON);
GO
ALTER EVENT SESSION [DeadlockMonitor] ON SERVER STATE=START;
GO

To read the captured events later, use this query:

SELECT 
    CAST(event_data AS XML) AS deadlock_graph,
    timestamp_utc
FROM 
    sys.fn_xe_file_target_read_file('deadlock_events*.xel', NULL, NULL, NULL);

Don't be surprised if the deadlock graph points to an internal Azure SQL Data Warehouse process! Background operations like:

• Automatic statistics updates
• Index maintenance tasks
• Data movement between distributions (a core part of how DW handles large tables)
  could be conflicting with your query/delete operation, even if no external users are accessing the table.

Once you have the graph, look for:

• The victim process (your session, marked clearly in the graph)
• The other process holding the conflicting lock (check the processid and hostname fields to see if it's an internal system process)
• The resource type (row locks, page locks, etc.) that caused the deadlock
• The exact SQL statements each process was running

This will give you a clear picture of what's clashing, and help you adjust your query (like adding hints, breaking up large deletes, or scheduling operations around maintenance windows) to avoid future deadlocks.

内容的提问来源于stack exchange，提问作者KBR