sql 2019查询表阻塞命令语句

以下是 SQL Server 2019 中查询表阻塞的命令语句：

1. 基础阻塞查询

查询当前所有阻塞

-- 方法1：使用 sys.dm_exec_requests
SELECT 
    blocking.session_id AS blocking_session,
    blocked.session_id AS blocked_session,
    blocking_text.text AS blocking_query,
    blocked_text.text AS blocked_query,
    DB_NAME(blocked.database_id) AS database_name,
    blocked.wait_type,
    blocked.wait_time / 1000 AS wait_time_seconds,
    blocked.blocking_session_id
FROM sys.dm_exec_requests blocked
LEFT JOIN sys.dm_exec_requests blocking 
    ON blocked.blocking_session_id = blocking.session_id
OUTER APPLY sys.dm_exec_sql_text(blocked.sql_handle) AS blocked_text
OUTER APPLY sys.dm_exec_sql_text(blocking.sql_handle) AS blocking_text
WHERE blocked.blocking_session_id > 0;

查看阻塞链（显示完整阻塞关系）

-- 方法2：使用 CTE 递归查询阻塞链
WITH BlockingChain AS (
    SELECT 
        session_id,
        blocking_session_id,
        1 AS level,
        CAST(session_id AS VARCHAR(MAX)) AS chain
    FROM sys.dm_exec_requests
    WHERE blocking_session_id = 0  -- 找到源头
    
    UNION ALL
    
    SELECT 
        r.session_id,
        r.blocking_session_id,
        bc.level + 1,
        bc.chain + ' -> ' + CAST(r.session_id AS VARCHAR(MAX))
    FROM sys.dm_exec_requests r
    INNER JOIN BlockingChain bc ON r.blocking_session_id = bc.session_id
    WHERE r.blocking_session_id > 0
)
SELECT 
    session_id AS [会话ID],
    blocking_session_id AS [阻塞源ID],
    level AS [阻塞层级],
    chain AS [阻塞链]
FROM BlockingChain
ORDER BY level;

2. 详细阻塞信息查询

获取完整阻塞详情

SELECT
    -- 阻塞会话信息
    blockings.session_id AS blocking_session_id,
    blockingt.text AS blocking_command,
    DB_NAME(blockings.database_id) AS blocking_database,
    blockingr.status AS blocking_status,
    
    -- 被阻塞会话信息
    blockeds.session_id AS blocked_session_id,
    blockedt.text AS blocked_command,
    DB_NAME(blockeds.database_id) AS blocked_database,
    blockeds.wait_type AS wait_type,
    blockeds.wait_time AS wait_time_ms,
    blockeds.last_wait_type AS last_wait_type,
    
    -- 锁信息
    blockedl.resource_type AS resource_type,
    blockedl.request_mode AS request_mode,
    blockedl.resource_description AS resource_description,
    
    -- 连接信息
    blockingc.client_net_address AS blocking_client_ip,
    blockingc.program_name AS blocking_program,
    blockedc.client_net_address AS blocked_client_ip,
    blockedc.program_name AS blocked_program,
    
    -- 事务信息
    blockingtr.transaction_id AS blocking_transaction_id,
    blockedtr.transaction_id AS blocked_transaction_id,
    GETDATE() AS query_time
FROM sys.dm_exec_requests blockeds
LEFT JOIN sys.dm_exec_requests blockings 
    ON blockeds.blocking_session_id = blockings.session_id
LEFT JOIN sys.dm_exec_connections blockingc 
    ON blockings.session_id = blockingc.most_recent_session_id
LEFT JOIN sys.dm_exec_connections blockedc 
    ON blockeds.session_id = blockedc.most_recent_session_id
LEFT JOIN sys.dm_tran_locks blockedl 
    ON blockeds.session_id = blockedl.request_session_id
LEFT JOIN sys.dm_tran_active_transactions blockingtr 
    ON blockings.session_id = blockingtr.transaction_session_id
LEFT JOIN sys.dm_tran_active_transactions blockedtr 
    ON blockeds.session_id = blockedtr.transaction_session_id
OUTER APPLY sys.dm_exec_sql_text(blockeds.sql_handle) AS blockedt
OUTER APPLY sys.dm_exec_sql_text(blockings.sql_handle) AS blockingt
WHERE blockeds.blocking_session_id > 0
ORDER BY blockeds.wait_time DESC;

3. 按对象查看阻塞

查看哪些表被阻塞

SELECT
    t.blocked_session_count AS 被阻塞会话数,
    t.blocking_session_count AS 阻塞会话数,
    OBJECT_NAME(p.object_id) AS 表名,
    DB_NAME(p.database_id) AS 数据库名,
    p.index_id,
    i.name AS 索引名,
    t.blocking_duration_ms AS 阻塞持续时间毫秒,
    t.last_blocked_time AS 最近阻塞时间
FROM (
    SELECT
        resource_associated_entity_id,
        COUNT(DISTINCT blocking_session_id) AS blocking_session_count,
        COUNT(DISTINCT session_id) AS blocked_session_count,
        MAX(wait_time) AS blocking_duration_ms,
        MAX(start_time) AS last_blocked_time
    FROM sys.dm_tran_locks l
    JOIN sys.dm_exec_requests r ON l.request_session_id = r.session_id
    WHERE r.blocking_session_id > 0
    GROUP BY resource_associated_entity_id
) t
JOIN sys.partitions p ON t.resource_associated_entity_id = p.hobt_id
LEFT JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE p.object_id > 0
ORDER BY t.blocked_session_count DESC;

4. 锁信息查询

查看当前所有锁

SELECT
    tl.resource_type AS 资源类型,
    CASE tl.resource_type
        WHEN 'OBJECT' THEN OBJECT_NAME(tl.resource_associated_entity_id)
        WHEN 'DATABASE' THEN DB_NAME(tl.resource_database_id)
        ELSE CAST(tl.resource_associated_entity_id AS VARCHAR)
    END AS 对象名,
    tl.request_mode AS 锁模式,
    tl.request_status AS 状态,
    tl.request_session_id AS 会话ID,
    DB_NAME(tl.resource_database_id) AS 数据库名,
    es.host_name AS 主机名,
    es.program_name AS 程序名,
    est.text AS 执行的SQL,
    er.blocking_session_id AS 阻塞会话ID,
    er.wait_type AS 等待类型,
    er.wait_time AS 等待时间
FROM sys.dm_tran_locks tl
LEFT JOIN sys.dm_exec_sessions es ON tl.request_session_id = es.session_id
LEFT JOIN sys.dm_exec_requests er ON tl.request_session_id = er.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) est
WHERE tl.resource_database_id = DB_ID()
ORDER BY tl.request_session_id, tl.resource_type;

5. 快速诊断脚本

阻塞诊断报告

-- 生成阻塞诊断报告
DECLARE @BlockingReport TABLE (
    blocking_session_id INT,
    blocked_session_id INT,
    wait_type NVARCHAR(60),
    wait_time_ms INT,
    blocking_query NVARCHAR(MAX),
    blocked_query NVARCHAR(MAX),
    blocking_program NVARCHAR(128),
    blocked_program NVARCHAR(128),
    capture_time DATETIME
);

INSERT INTO @BlockingReport
SELECT
    r.blocking_session_id,
    r.session_id AS blocked_session_id,
    r.wait_type,
    r.wait_time,
    blocking_text.text AS blocking_query,
    blocked_text.text AS blocked_query,
    blocking_s.program_name AS blocking_program,
    blocked_s.program_name AS blocked_program,
    GETDATE()
FROM sys.dm_exec_requests r
LEFT JOIN sys.dm_exec_requests blocking_r 
    ON r.blocking_session_id = blocking_r.session_id
LEFT JOIN sys.dm_exec_sessions blocking_s 
    ON r.blocking_session_id = blocking_s.session_id
LEFT JOIN sys.dm_exec_sessions blocked_s 
    ON r.session_id = blocked_s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS blocked_text
OUTER APPLY sys.dm_exec_sql_text(blocking_r.sql_handle) AS blocking_text
WHERE r.blocking_session_id > 0;

-- 显示报告
SELECT * FROM @BlockingReport
ORDER BY wait_time_ms DESC;

6. 实用监控存储过程

创建阻塞监控存储过程

CREATE PROCEDURE sp_MonitorBlocking
    @DurationThreshold INT = 5000,  -- 默认5秒以上的阻塞才显示
    @MaxRows INT = 100
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT TOP(@MaxRows)
        '阻塞警报' AS 警报级别,
        CASE 
            WHEN r.wait_time > 30000 THEN '严重'
            WHEN r.wait_time > 10000 THEN '警告'
            ELSE '一般'
        END AS 严重程度,
        r.session_id AS 被阻塞会话ID,
        r.blocking_session_id AS 阻塞会话ID,
        CONVERT(VARCHAR(8), DATEADD(ms, r.wait_time, 0), 108) AS 等待时间,
        DB_NAME(r.database_id) AS 数据库名,
        r.wait_type AS 等待类型,
        r.status AS 状态,
        blocking_s.login_name AS 阻塞者登录名,
        blocking_s.host_name AS 阻塞者主机名,
        blocking_s.program_name AS 阻塞者程序,
        blocked_s.login_name AS 被阻塞登录名,
        blocked_s.host_name AS 被阻塞主机名,
        blocked_s.program_name AS 被阻塞程序,
        blocking_t.text AS 阻塞查询语句,
        blocked_t.text AS 被阻塞查询语句,
        GETDATE() AS 检测时间
    FROM sys.dm_exec_requests r
    LEFT JOIN sys.dm_exec_sessions blocking_s 
        ON r.blocking_session_id = blocking_s.session_id
    LEFT JOIN sys.dm_exec_sessions blocked_s 
        ON r.session_id = blocked_s.session_id
    OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS blocked_t
    OUTER APPLY sys.dm_exec_sql_text(
        CASE 
            WHEN r.blocking_session_id > 0 
            THEN (SELECT sql_handle FROM sys.dm_exec_requests 
                  WHERE session_id = r.blocking_session_id)
            ELSE NULL 
        END
    ) AS blocking_t
    WHERE r.blocking_session_id > 0
        AND r.wait_time > @DurationThreshold
    ORDER BY r.wait_time DESC;
END
GO

-- 使用示例
EXEC sp_MonitorBlocking @DurationThreshold = 1000;

7. 解决阻塞的常用命令

查看并终止阻塞进程

-- 1. 先查看阻塞
SELECT 
    session_id,
    blocking_session_id,
    wait_time,
    wait_type,
    last_wait_type
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;

-- 2. 查看具体进程信息
EXEC sp_who2;

-- 3. 查看进程的详细SQL
DBCC INPUTBUFFER(session_id);

-- 4. 终止阻塞进程（谨慎使用！）
KILL session_id;

-- 5. 批量终止长时间阻塞的进程（超过10秒）
DECLARE @kill_sql NVARCHAR(MAX) = '';
SELECT @kill_sql = @kill_sql + 'KILL ' + CAST(session_id AS NVARCHAR(10)) + ';'
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0 
    AND wait_time > 10000;

EXEC sp_executesql @kill_sql;

8. 实时监控阻塞

创建实时监控视图

-- 创建阻塞监控视图
CREATE VIEW vw_BlockingMonitor AS
SELECT
    er.session_id,
    er.blocking_session_id,
    er.start_time,
    er.status,
    er.command,
    er.wait_type,
    er.wait_time,
    er.wait_resource,
    er.transaction_id,
    er.cpu_time,
    er.total_elapsed_time,
    er.reads,
    er.writes,
    er.logical_reads,
    es.login_name,
    es.host_name,
    es.program_name,
    est.text AS sql_text,
    DB_NAME(er.database_id) AS database_name,
    GETDATE() AS monitor_time
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions es 
    ON er.session_id = es.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) est
WHERE er.blocking_session_id > 0
    OR er.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id > 0);

-- 查询视图
SELECT * FROM vw_BlockingMonitor
ORDER BY wait_time DESC;

使用建议：

1. 定期检查：设置作业定期运行阻塞查询
2. 设置告警：当阻塞时间超过阈值时发送告警
3. 分析原因：记录阻塞历史，分析常见阻塞模式
4. 优化方案：根据阻塞原因优化索引、事务或查询逻辑

快速诊断命令：

-- 最常用的阻塞查询
SELECT 
    session_id, 
    blocking_session_id, 
    wait_type, 
    wait_time, 
    wait_resource,
    DB_NAME(database_id) AS db_name
FROM sys.dm_exec_requests 
WHERE blocking_session_id > 0;

这些命令可以帮助你快速识别和解决 SQL Server 2019 中的阻塞问题。