主页 / dba / 问题 / 187794
Accepted
Randy Minder
Asked: 2017-10-06 11:26:52 +0800 CST

为什么某些 spid 值没有出现在 sys.dm_exec_requests 中？

  • 772

我正在对 SQL Server 2014 执行以下 dmv 查询，以查找服务器上的所有活动和任何阻塞活动：

SELECT Distinct
   ...
FROM sys.dm_exec_requests er
    OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
    LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id
    LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
WHERE st.text IS NOT NULL And DB_Name(er.database_id) IN ('Vincent', 'Vincent_Audit')
ORDER BY BlockingSessionId Desc, SessionId

该查询的示例结果如下：

在此处输入图像描述

可以看到会话 120 被 112 阻止，112 被 69 阻止。但是，进程 69 没有出现，因为它在 sys.dm_exec_requests 中不存在。

因此，以下查询不返回任何结果：

SELECT *
FROM sys.dm_exec_requests
Where session_id = 69

有谁知道为什么会这样？

sql-server dmv

2 个回答

  1. Best Answer

    会话可以在没有活动请求的情况下存在，但仍会阻止其他会话。

    考虑一下您是否在 SSMS 中打开了一个窗口，您可以在其中运行它：

    BEGIN TRANSACTION
INSERT INTO dbo.SomeTable DEFAULT VALUES;

    然后在另一个窗口中运行：

    BEGIN TRANSACTION
SELECT * FROM dbo.SomeTable;

    第一个会话将保持锁定状态dbo.SomeTable，而不会出现在sys.dm_exec_requests. 您将在 中看到会话sys.dm_exec_sessions，并且您将能够在 中看到打开的事务sys.dm_tran_session_transactions。

    我推荐 Adam Machanic 的 sp_whoisactive，但是如果您不允许下载脚本以在您的服务器上运行，您可以考虑使用类似以下代码的代码，该代码可以在没有阻塞其他会话的活动请求的情况下识别打开的事务：

    /*
    This query shows sessions that are blocking other sessions, including sessions that are 
    not currently processing requests (for instance, they have an open, uncommitted transaction).

    By:  Max Vernon, 2017-03-20
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --reduce possible blocking by this query.

USE tempdb;

IF OBJECT_ID('tempdb..#dm_tran_session_transactions') IS NOT NULL
DROP TABLE #dm_tran_session_transactions;
SELECT *
INTO #dm_tran_session_transactions
FROM sys.dm_tran_session_transactions;

IF OBJECT_ID('tempdb..#dm_exec_connections') IS NOT NULL
DROP TABLE #dm_exec_connections;
SELECT *
INTO #dm_exec_connections
FROM sys.dm_exec_connections;

IF OBJECT_ID('tempdb..#dm_os_waiting_tasks') IS NOT NULL
DROP TABLE #dm_os_waiting_tasks;
SELECT *
INTO #dm_os_waiting_tasks
FROM sys.dm_os_waiting_tasks;

IF OBJECT_ID('tempdb..#dm_exec_sessions') IS NOT NULL
DROP TABLE #dm_exec_sessions;
SELECT *
INTO #dm_exec_sessions
FROM sys.dm_exec_sessions;

IF OBJECT_ID('tempdb..#dm_exec_requests') IS NOT NULL
DROP TABLE #dm_exec_requests;
SELECT *
INTO #dm_exec_requests
FROM sys.dm_exec_requests;

;WITH IsolationLevels AS 
(
    SELECT v.*
    FROM (VALUES 
              (0, 'Unspecified')
            , (1, 'Read Uncomitted')
            , (2, 'Read Committed')
            , (3, 'Repeatable')
            , (4, 'Serializable')
            , (5, 'Snapshot')
        ) v(Level, Description)
)
, trans AS 
(
    SELECT dtst.session_id
        , blocking_sesion_id = 0
        , Type = 'Transaction'
        , QueryText = dest.text
    FROM #dm_tran_session_transactions dtst 
        LEFT JOIN #dm_exec_connections dec ON dtst.session_id = dec.session_id
    OUTER APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
)
, tasks AS 
(
    SELECT dowt.session_id
        , dowt.blocking_session_id
        , Type = 'Waiting Task'
        , QueryText = dest.text
    FROM #dm_os_waiting_tasks dowt
        LEFT JOIN #dm_exec_connections dec ON dowt.session_id = dec.session_id
    OUTER APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
    WHERE dowt.blocking_session_id IS NOT NULL
)
, requests AS 
(
SELECT des.session_id
    , der.blocking_session_id
    , Type = 'Session Request'
    , QueryText = dest.text
FROM #dm_exec_sessions des
    INNER JOIN #dm_exec_requests der ON des.session_id = der.session_id
OUTER APPLY sys.dm_exec_sql_text(der.sql_handle) dest
WHERE der.blocking_session_id IS NOT NULL
    AND der.blocking_session_id > 0 
)
, Agg AS (
    SELECT SessionID = tr.session_id
        , ItemType = tr.Type
        , CountOfBlockedSessions = (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = tr.session_id)
        , BlockedBySessionID = tr.blocking_sesion_id
        , QueryText = tr.QueryText
    FROM trans tr
    WHERE EXISTS (
        SELECT 1
        FROM requests r
        WHERE r.blocking_session_id = tr.session_id
        )
    UNION ALL
    SELECT ta.session_id
        , ta.Type
        , CountOfBlockedSessions = (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = ta.session_id)
        , BlockedBySessionID = ta.blocking_session_id
        , ta.QueryText
    FROM tasks ta
    UNION ALL
    SELECT rq.session_id
        , rq.Type
        , CountOfBlockedSessions =  (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = rq.session_id)
        , BlockedBySessionID = rq.blocking_session_id
        , rq.QueryText
    FROM requests rq
)
SELECT agg.SessionID
    , ItemType = STUFF((SELECT ', ' + COALESCE(a.ItemType, '') FROM agg a WHERE a.SessionID = agg.SessionID ORDER BY a.ItemType FOR XML PATH ('')), 1, 2, '')
    , agg.BlockedBySessionID
    , agg.CountOfBlockedSessions
    , agg.QueryText
    , des.host_name
    , des.login_name
    , des.is_user_process
    , des.program_name
    , des.status
    , TransactionIsolationLevel = il.Description
FROM agg 
    LEFT JOIN #dm_exec_sessions des ON agg.SessionID = des.session_id
    LEFT JOIN IsolationLevels il ON des.transaction_isolation_level = il.Level
GROUP BY agg.SessionID
    , agg.BlockedBySessionID
    , agg.CountOfBlockedSessions
    , agg.QueryText
    , des.host_name
    , des.login_name
    , des.is_user_process
    , des.program_name
    , des.status
    , il.Description
ORDER BY 
    agg.BlockedBySessionID
    , agg.CountOfBlockedSessions
    , agg.SessionID;
    • 8

  2. 这实际上是非常有教育意义的。spid 69 没有出现在 sys.dm_exec_requests 中的原因是因为 sys.dm_exec_requests 只显示正在运行的进程，正如 Sean Gallardy 指出的那样。所以，我执行了 sp_who 并且 69 确实显示为一个暂停的进程。spid 69 正在执行插入操作，它正在等待释放资源。因此，它被暂停并且没有出现在 sys.dm_exec_requests 中。

    • 0

相关问题