sql server死锁查询命令

你可以使用以下命令来查找 SQL Server 中的死锁：

SELECT
[res].[request_session_id] AS [spid],
[sch].[name] AS [schema_name],
[obj].[name] AS [object_name],
[res].[request_mode] AS [lock_type],
[res].[request_status] AS [status],
[res].[request_owner_type] AS [lock_owner_type],
[req].[transaction_isolation_level] AS [isolation_level],
[req].[session_id] AS [session_id],
[req].[start_time] AS [request_start_time],
[req].[statement_text] AS [statement],
COALESCE([dbx].[database_name], DB_NAME([req].[database_id])) AS [db_name],
[req].[command] AS [command_type],
[req].[sql_handle],
[req].[plan_handle]
FROM
sys.dm_tran_locks AS [res]
JOIN sys.partitions AS [part] ON
[res].[resource_associated_entity_id] = [part].[hobt_id]
JOIN sys.objects AS [obj] ON
[part].[object_id] = [obj].[object_id]
JOIN sys.schemas AS [sch] ON
[obj].[schema_id] = [sch].[schema_id]
JOIN sys.dm_exec_sessions AS [req] ON
[res].[request_session_id] = [req].[session_id]
LEFT JOIN sys.dm_exec_requests AS [req2] ON
[req].[session_id] = [req2].[session_id]
LEFT JOIN (
SELECT
[dd].[database_id],
[dd].[request_session_id] AS [spid],
[dd].[login_time],
[dd].[cpu_time],
[dd].[logical_reads],
[dd].[memory_usage],
[dd].[is_user_process],
[dd].[program_name],
[dd].[host_name],
[dd].[nt_domain],
[dd].[nt_user_name],
[dd].[status],
[db].[name] AS [database_name]
FROM
sys.dm_exec_sessions AS [dd]
JOIN sys.databases AS [db] ON
[dd].[database_id] = [db].[database_id]
) AS [dbx] ON
[req].[session_id] = [dbx].[spid]
WHERE
[res].[lock_owner_address] <> CONVERT(BINARY(8), 0)
AND [res].[request_session_id] = @@SPID;

这个命令将会显示当前会话中的死锁信息。如果你想检查其他会话的死锁信息，你需要更改 WHERE 子句中的 [res].[request_session_id] 条件。