SQLServer查询阻塞

--查阻塞--

 SELECT
    WT.session_id AS '等待的进程',
    DB_NAME(TL.resource_database_id) AS '数据库名',
    (select OBJECT_NAME(resource_associated_entity_id) --resource_associated_entity_id为Bigint类型，而Object_Name方法参数为int，有可能出现转换失败的情况
    from sys.dm_tran_locks 
    where request_session_id = WT.blocking_session_id 
    and resource_type = 'OBJECT') as '表名',
    WT.wait_duration_ms,
    TL.request_mode,
    (SELECT SUBSTRING(ST.text, (ER.statement_start_offset/2) + 1,
       ((CASE ER.statement_end_offset
          WHEN -1 THEN DATALENGTH(ST.text)
          ELSE ER.statement_end_offset
         END - ER.statement_start_offset)/2) + 1)
    FROM sys.dm_exec_requests AS ER
       CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST
    WHERE ER.session_id = TL.request_session_id)
       AS '等待执行的sql',
    WT.blocking_session_id as '阻塞的进程',
    WT.resource_description AS blocking_resource_description,
    CASE WHEN WT.blocking_session_id > 0 THEN
       (SELECT ST2.text FROM sys.sysprocesses AS SP
              CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS ST2
       WHERE SP.spid = WT.blocking_session_id)
       ELSE NULL
    END AS '阻塞进程执行的sql',
     SPP.hostname as '阻塞进程-hostname',
     SPP.nt_domain as '阻塞进程-nt_domain',
     SPP.nt_username as '阻塞进程-nt_username',
     SPP.loginame as '阻塞进程-loginame'
 FROM sys.dm_os_waiting_tasks AS WT
    JOIN sys.dm_tran_locks AS TL
    ON WT.resource_address = TL.lock_owner_address
    inner join sys.sysprocesses as SPP on WT.blocking_session_id = SPP.spid
 WHERE WT.wait_duration_ms > 5000--等待5s以上的
    AND WT.session_id > 50;

--查阻塞--
SELECT t1.resource_type AS [锁类型], DB_NAME(resource_database_id) AS [数据库名],
t1.resource_associated_entity_id AS [阻塞资源对象],t1.resource_description as [资源描述信息], t1.request_mode AS [请求的锁], 
t1.request_session_id AS [等待会话], t2.wait_duration_ms AS [等待时间],       
(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 [等待会话执行的批SQL],
(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 )/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 [等待会话执行的SQL],                    
t2.blocking_session_id AS [阻塞会话],                                        
(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 [阻塞会话执行的批SQL]
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);

--根据ID 查看堵塞信息
SELECT * FROM sys.sysprocesses WHERE spid =1204

--查看当时SPID执行的sql语句

DBCC inputbuffer(162);  

-- 查看系统所有临时表占用的总空间
select cast(sum(a.total_pages)*8/1024 as varchar)+' MB' total          
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
join sys.tables it on p.object_id = it.object_id
where it.name like 'TMP%'

--查阻塞--
SELECT TOP 100 t.hostname,
                t.loginame,
                percent_complete,
        [session_id] ,
        der.[request_id] ,
        [start_time] AS '开始时间' ,
        der.[status] AS '状态' ,
        [command] AS '命令' ,
        dest.[text] AS 'sql语句' ,
        DB_NAME([database_id]) AS '数据库名' ,
        [blocking_session_id] AS '正在阻塞其他会话的会话ID' ,
        [wait_type] AS '等待资源类型' ,
        [wait_time] AS '等待时间' ,
        [wait_resource] AS '等待的资源' ,
        [reads] AS '物理读次数' ,
        [writes] AS '写次数' ,
        [logical_reads] AS '逻辑读次数' ,
        [row_count] AS '返回结果行数',
                t.hostname,
                t.loginame
FROM    sys.[dm_exec_requests] AS der
        CROSS APPLY sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
                left JOIN sys.sysprocesses t ON t.spid=der.session_id
WHERE   [session_id] > 50
 AND DB_NAME(der.[database_id]) = 'AIS20210422085104'
ORDER BY [cpu_time] DESC