SQL Server巡检

        use master;
        WITH CTE_SID ( BSID, SID, sql_handle,name )        
            AS ( SELECT a.[Blocking_Session_ID],
                        a.[Session_ID] ,
                                a.sql_handle,
                                bb.name
                        FROM     sys.dm_exec_requests a inner join sys.sysdatabases bb on a.database_id = bb.dbid
                        WHERE    a.[Blocking_Session_ID] <> 0
                        UNION ALL
                        SELECT   A.[Blocking_Session_ID] ,
                                A.[Session_ID] ,
                                A.sql_handle,
                                bb.name
                        FROM     sys.dm_exec_requests A inner join sys.sysdatabases bb on a.database_id = bb.dbid
                                JOIN CTE_SID B ON A.[Session_ID] = B.BSID
                )
            SELECT C.BSID,
                    C.SID ,
                    S.login_name ,
                    S.host_name ,
                    S.status ,
                    S.cpu_time ,
                    S.memory_usage ,
                    S.last_request_start_time ,
                    S.last_request_end_time ,
                    S.logical_reads ,
                    S.row_count ,
                    q.text,
                    c.name
            FROM CTE_SID C
                    JOIN sys.dm_exec_sessions S ON C.sid = s.[Session_ID]
                    CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q
            ORDER BY sid ;
            
            
一些列含义的说明：
BSID  # 持有锁的sessionID
SID    # 等待锁的sessionID
login_name   # 被阻塞的用户名
host_name  # 被阻塞的用户地址
text =  # 具体的被阻塞的SQL
dbname =  # 阻塞发生的数据库           

select CN.*,ST.text 
from sys.dm_exec_connections CN 
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST 
where CN.session_id = ${上一步查出来的BSID}

SELECT  
        SS.session_id,
        SS.login_time,
        SS.program_name,
        ST.transaction_id AS TransactionID ,  
                DB_NAME(DT.database_id) AS DatabaseName ,  
                AT.transaction_begin_time AS TransactionStartTime ,  
                DATEDIFF(minute, AT.transaction_begin_time, GETDATE()) AS Tran_run_minutes ,  
                CASE AT.transaction_type  
                    WHEN 1 THEN 'Read/Write Transaction'  
                    WHEN 2 THEN 'Read-Only Transaction'  
                    WHEN 3 THEN 'System Transaction'  
                    WHEN 4 THEN 'Distributed Transaction'  
                END AS TransactionType ,  
                CASE AT.transaction_state  
                    WHEN 0 THEN 'Transaction Not Initialized'  
                    WHEN 1 THEN 'Transaction Initialized & Not Started'  
                    WHEN 2 THEN 'Active Transaction'  
                    WHEN 3 THEN 'Transaction Ended'  
                    WHEN 4 THEN 'Distributed Transaction Initiated Commit Process'  
                    WHEN 5 THEN 'Transaction in Prepared State & Waiting Resolution'  
                    WHEN 6 THEN 'Transaction Committed'  
                    WHEN 7 THEN 'Transaction Rolling Back'  
                    WHEN 8 THEN 'Transaction Rolled Back'  
                END AS TransactionState  
        FROM    sys.dm_tran_session_transactions AS ST  
                INNER JOIN sys.dm_tran_active_transactions AS AT ON ST.transaction_id = AT.transaction_id  
                INNER JOIN sys.dm_tran_database_transactions AS DT ON ST.transaction_id = DT.transaction_id  
                INNER join sys.dm_exec_sessions AS SS on SS.session_id = ST.session_id
        WHERE DATEDIFF(minute, AT.transaction_begin_time, GETDATE())> 5 -- 找出运行时间大于5分钟的事务
        ORDER BY TransactionStartTime