-- 1. 系统健康会话（SQL Server 2012+）
SELECT * FROM sys.dm_xe_session_targets xst
INNER JOIN sys.dm_xe_sessions xs ON xs.address = xst.event_session_address
WHERE xs.name = 'system_health'
-- 2. 查看当前死锁信息
SELECT * FROM sys.dm_tran_locks
WHERE request_status = 'CONVERT'
-- 3. 查看阻塞链
SELECT
blocking.session_id AS blocking_session_id,
blocked.session_id AS blocked_session_id,
blocking_text = blocking.text,
blocked_text = blocked.text
FROM sys.dm_exec_requests blocked
INNER JOIN sys.dm_exec_requests blocking
ON blocking.session_id = blocked.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blocking-- 创建死锁监控扩展事件
CREATE EVENT SESSION [Deadlock_Monitor] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(
SET filename=N'D:\Logs\Deadlock_Monitor.xel',
max_file_size=(50),
max_rollover_files=(10))
WITH (STARTUP_STATE=ON)
GO
-- 启动事件会话
ALTER EVENT SESSION [Deadlock_Monitor] ON SERVER STATE = START-- 启用死锁跟踪标志（不推荐生产环境长期使用）
DBCC TRACEON(1204, -1) -- 基本死锁信息
DBCC TRACEON(1222, -1) -- 更详细的XML格式信息
-- 查看错误日志中的死锁信息
EXEC xp_readerrorlog 0, 1, 'deadlock'-- 解析system_health中的死锁信息
SELECT
DeadlockEvent = XEvent.value('(data/value)[1]', 'varchar(max)'),
EventTime = XEvent.value('(@timestamp)[1]', 'datetime2')
FROM (
SELECT CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE s.name = 'system_health'
AND st.target_name = 'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
WHERE XEvent.value('(@name)[1]', 'varchar(4000)') = 'xml_deadlock_report'
ORDER BY EventTime DESC<!-- 死锁XML结构示例 -->
<deadlock>
<victim-list>
<victimProcess id="processId"/>
</victim-list>
<process-list>
<process id="processId" taskpriority="0" logused="0" waitresource="KEY: ..."
waittime="ms" ownerId="transactionId" transactionname="user_transaction"
lasttranstarted="datetime" XDES="0x..." lockMode="X"
schedulerid="1" kpid="pid" status="suspended" spid="spid"
sbid="0" ecid="0" priority="0" trancount="2"
lastbatchstarted="datetime" lastbatchcompleted="datetime"
clientapp="appName" hostname="hostname" hostpid="pid"
loginname="login" isolationlevel="read committed (2)"
xactid="transactionId" currentdb="dbId" lockTimeout="milliseconds"
clientoption1="value" clientoption2="value">
<executionStack>
<frame line="lineNumber" stmtstart="startOffset"
stmtend="endOffset" sqlhandle="handle"/>
</executionStack>
<inputbuf>
SQL语句内容
</inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="objectId" dbid="dbId" objectname="objectName"
indexname="indexName" id="lockId" mode="X" associatedObjectId="objectId">
<owner-list>
<owner id="processId" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="processId" mode="X" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>-- 解析死锁XML获取关键信息
WITH DeadlockData AS (
SELECT
CAST(event_data AS XML) AS DeadlockGraph,
event_date
FROM sys.fn_xe_file_target_read_file(
'D:\Logs\Deadlock_Monitor*.xel',
NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report'
)
SELECT
event_date,
DeadlockGraph.value('(deadlock/victim-list/victimProcess/@id)[1]', 'varchar(50)') AS VictimProcessId,
DeadlockGraph.query('deadlock/process-list/process/inputbuf') AS InputBuffer,
DeadlockGraph.query('deadlock/resource-list') AS Resources
FROM DeadlockData场景: 两个事务以不同顺序访问相同资源
-- 事务1
BEGIN TRANSACTION
UPDATE TableA SET Column1 = 'Value' WHERE ID = 1
UPDATE TableB SET Column1 = 'Value' WHERE ID = 1
COMMIT
-- 事务2（反顺序）
BEGIN TRANSACTION
UPDATE TableB SET Column1 = 'Value' WHERE ID = 1
UPDATE TableA SET Column1 = 'Value' WHERE ID = 1
COMMIT解决方案:
场景: 表扫描导致大量锁升级
-- 查看缺失索引
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [IX_' + OBJECT_NAME(mid.object_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,''),', ','_'),'[',''),']','') + ']' +
ISNULL('_' + REPLACE(REPLACE(REPLACE(ISNULL(mid.inequality_columns,''),', ','_'),'[',''),']',''), '') +
' ON ' + mid.statement +
' (' + ISNULL (mid.equality_columns,'') +
CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END +
ISNULL (mid.inequality_columns, '') + ')' +
ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY improvement_measure DESC场景: 外键约束检查导致的锁争用
解决方案:
场景: INSERT操作导致页拆分
-- 监控页拆分
SELECT
OBJECT_NAME(object_id) AS TableName,
index_id,
leaf_allocation_count,
nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL)
WHERE leaf_allocation_count > 0
ORDER BY leaf_allocation_count DESC
-- 解决方法：调整填充因子
ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR = 90)-- 1. 检查索引碎片
SELECT
OBJECT_NAME(ips.object_id) AS TableName,
i.name AS IndexName,
ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(
DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON i.object_id = ips.object_id
AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent > 30
-- 2. 创建覆盖索引减少键锁
CREATE INDEX IX_Covering ON Orders(CustomerID, OrderDate)
INCLUDE (TotalAmount, Status)-- 1. 缩短事务时间
BEGIN TRANSACTION
-- 尽量减少事务内的业务逻辑
-- 只包含必要的数据库操作
COMMIT TRANSACTION
-- 2. 使用行版本控制（READ_COMMITTED_SNAPSHOT）
ALTER DATABASE YourDatabase
SET READ_COMMITTED_SNAPSHOT ON-- 1. 使用UPDLOCK避免死锁
SELECT * FROM Table WITH (UPDLOCK, ROWLOCK) WHERE ID = 1
-- 2. 使用NOLOCK（只读查询，注意脏读风险）
SELECT * FROM Table WITH (NOLOCK) WHERE Status = 1
-- 3. 使用READPAST跳过锁定的行
SELECT * FROM Table WITH (READPAST) WHERE Status = 1# PowerShell脚本监控死锁
$server = "YourServer"
$database = "YourDatabase"
$query = @"
SELECT
DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), xed.event_date) AS event_date,
xed.event_data.value('(deadlock/victim-list/victimProcess/@id)[1]', 'varchar(50)') AS victim_id,
xed.event_data.query('deadlock/process-list/process/inputbuf') AS input_buffer
FROM (
SELECT CAST(target_data AS XML) AS target_data
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE s.name = 'system_health'
AND st.target_name = 'ring_buffer'
) AS data
CROSS APPLY target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS xed(event_data)
"@
Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query-- 1. 查看当前死锁
EXEC sp_who2
EXEC sp_lock
-- 2. 终止阻塞进程（谨慎使用）
KILL [SPID]
-- 3. 查看死锁链
SELECT
wt.blocking_session_id,
wt.wait_type,
wt.wait_duration_ms,
wt.resource_description,
t.text AS [SQL Text]
FROM sys.dm_os_waiting_tasks wt
INNER JOIN sys.dm_exec_requests er ON wt.session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) t
WHERE wt.blocking_session_id IS NOT NULL-- 创建死锁监控表
CREATE TABLE DeadlockHistory (
ID INT IDENTITY(1,1) PRIMARY KEY,
EventTime DATETIME2,
DeadlockGraph XML,
Processed BIT DEFAULT 0
)
-- 创建监控作业
CREATE PROCEDURE MonitorDeadlocks
AS
BEGIN
INSERT INTO DeadlockHistory (EventTime, DeadlockGraph)
SELECT
DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), xed.event_date) AS event_date,
xed.event_data
FROM (
SELECT CAST(target_data AS XML) AS target_data
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE s.name = 'system_health'
AND st.target_name = 'ring_buffer'
) AS data
CROSS APPLY target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS xed(event_data)
WHERE xed.event_data IS NOT NULL
END通过以上指南，您可以系统性地排查和解决SQL Server死锁问题。关键是建立预防机制、快速响应流程和持续优化策略。
