查出當前CPU吃最多的查詢語法語blockingchain

Source:2025-11-19

查出當前CPU吃最多的查詢語法語blocking chain

--目前正在消耗最多 CPU 的查詢
SELECT TOP 10 
    s.session_id,
    r.status,
    r.cpu_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.total_elapsed_time / (1000 * 60) AS 'Elaps M',
    SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
    ((CASE r.statement_end_offset
        WHEN -1 THEN DATALENGTH(st.TEXT)
        ELSE r.statement_end_offset
    END - r.statement_start_offset) / 2) + 1) AS statement_text,
    r.command,
    s.login_name,
    s.host_name,
    s.program_name
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id 
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC


-- 先找當前 CPU 使用量前五名的 session_id
DECLARE @TopCpu TABLE (session_id INT);

INSERT INTO @TopCpu(session_id)
SELECT TOP 5 r.session_id
FROM sys.dm_exec_requests r
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC;

-- 用 CTE 查出每個 session 的 blocking chain
WITH BlockChain AS (
    SELECT
        r.session_id,
        r.blocking_session_id,
        r.status,
        r.cpu_time,
        r.wait_type,
        r.wait_time,
        r.logical_reads,
        r.reads,
        r.writes,
        r.total_elapsed_time / (1000 * 60) AS 'Elaps_M',
        SUBSTRING(st.text, (r.statement_start_offset/2)+1,
            ((CASE r.statement_end_offset
                WHEN -1 THEN DATALENGTH(st.text)
                ELSE r.statement_end_offset
            END - r.statement_start_offset)/2)+1) AS statement_text,
        r.command,
        s.login_name,
        s.host_name,
        s.program_name,
        1 AS chain_level,
        r.session_id AS root_session_id
    FROM sys.dm_exec_sessions AS s
    JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
    JOIN @TopCpu t ON r.session_id = t.session_id

    UNION ALL

    SELECT
        r2.session_id,
        r2.blocking_session_id,
        r2.status,
        r2.cpu_time,
        r2.wait_type,
        r2.wait_time,
        r2.logical_reads,
        r2.reads,
        r2.writes,
        r2.total_elapsed_time / (1000 * 60) AS 'Elaps_M',
        SUBSTRING(st2.text, (r2.statement_start_offset/2)+1,
            ((CASE r2.statement_end_offset
                WHEN -1 THEN DATALENGTH(st2.text)
                ELSE r2.statement_end_offset
            END - r2.statement_start_offset)/2)+1) AS statement_text,
        r2.command,
        s2.login_name,
        s2.host_name,
        s2.program_name,
        BlockChain.chain_level + 1,
        BlockChain.root_session_id
    FROM sys.dm_exec_sessions AS s2
    JOIN sys.dm_exec_requests AS r2 ON r2.session_id = s2.session_id
    CROSS APPLY sys.dm_exec_sql_text(r2.sql_handle) AS st2
    JOIN BlockChain ON r2.session_id = BlockChain.blocking_session_id
    WHERE r2.blocking_session_id <> 0
)
SELECT *
FROM BlockChain
ORDER BY root_session_id, chain_level;