侦测 Deadlocking 或阻塞问题

dba 发布于 2023-12-26 阅读(40)
SELECT t1.resource_type AS [lock type]
    ,DB_NAME(resource_database_id) AS [database]
    ,t1.resource_associated_entity_id AS [blk object]
    ,t1.request_mode AS [lock req]
    ,t1.request_session_id AS [waiter sid]
    ,t2.wait_duration_ms AS [wait time]
    ,(
        SELECT [text]
        FROM sys.dm_exec_requests AS r WITH (NOLOCK)
        CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle])
        WHERE r.session_id = t1.request_session_id
        ) AS [waiter_batch]
    ,(
        SELECT SUBSTRING(qt.[text], r.statement_start_offset / 2, (
                    CASE 
                        WHEN r.statement_end_offset = - 1
                            THEN LEN(CONVERT(NVARCHAR(max), qt.[text])) * 2
                        ELSE r.statement_end_offset
                        END - r.statement_start_offset
                    ) / 2)
        FROM sys.dm_exec_requests AS r WITH (NOLOCK)
        CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt
        WHERE r.session_id = t1.request_session_id
        ) AS [waiter_stmt]
    ,t2.blocking_session_id AS [blocker sid]
    ,(
        SELECT [text]
        FROM sys.sysprocesses AS p
        CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle])
        WHERE p.spid = t2.blocking_session_id
        ) AS [blocker_stmt]
FROM sys.dm_tran_locks AS t1 WITH (NOLOCK)
INNER JOIN sys.dm_os_waiting_tasks AS t2 WITH (NOLOCK) ON t1.lock_owner_address = t2.resource_address
OPTION (RECOMPILE);
SELECT L.request_session_id AS SPID
    ,DB_NAME(L.resource_database_id) AS DatabaseName
    ,O.NAME AS LockedObjectName
    ,P.object_id AS LockedObjectId
    ,L.resource_type AS LockedResource
    ,L.request_mode AS LockType
    ,ST.TEXT AS SqlStatementText
    ,ES.login_name AS LoginName
    ,ES.host_name AS HostName
    ,TST.is_user_transaction AS IsUserTransaction
    ,AT.[name] AS TransactionName
    ,CN.auth_scheme AS AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id