如何獲取有關導致阻塞的睡眠程序的資訊？

我有一個程序可以讓我查看我的 sql server 中目前正在執行的內容。

此過程不使用 sys.sysprocess，因為它已被棄用。

但是，我正在努力尋找有關導致阻塞的睡眠程序的所需資訊。

這是因為 DMV sys.dm_exec_requests不保存任何休眠程序的請求。

我不想使用sp_whoisactive或任何監控工具。sp_whoisactive 使用 sys.sysprocesses。

我有我的第 81 次會議：

 BEGIN TRANSACTION T1

 SELECT @@TRANCOUNT

 update 
 [TableBackups].[dbo].[spstats]
 set execution_count  = 1000
 where dbname = 'master'

 select @@spid

我有我的第 51 次會議：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT TOP 1000 *
 FROM [TableBackups].[dbo].[spstats]
 WITH (HOLDLOCK)

上面這兩個會話同時執行，因此會話 81 阻塞了會話 51。

我在下面有這個選擇，它獲取有關會話 81 的資訊，但是，我無法獲得 dbid，並且我不確定 Open_transactions 和整個等待資訊。

SELECT es.session_id AS session_id
,COALESCE(es.original_login_name, 'No Info') AS login_name
,COALESCE(es.host_name,'No Info') AS hostname
,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch
,es.status
,COALESCE(er.blocking_session_id,0) AS blocked_by
,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype
,COALESCE(er.wait_time,0) AS waittime
,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype
,COALESCE(er.wait_resource,'') AS waitresource
,coalesce(db_name(er.database_id),'No Info') as dbid
,COALESCE(er.command,'AWAITING COMMAND') AS cmd
,sql_text=st.text
,transaction_isolation =
   CASE es.transaction_isolation_level
       WHEN 0 THEN 'Unspecified'
       WHEN 1 THEN 'Read Uncommitted'
       WHEN 2 THEN 'Read Committed'
       WHEN 3 THEN 'Repeatable'
       WHEN 4 THEN 'Serializable'
       WHEN 5 THEN 'Snapshot'
   END
,COALESCE(es.cpu_time,0) 
   + COALESCE(er.cpu_time,0) AS cpu
,COALESCE(es.reads,0) 
   + COALESCE(es.writes,0) 
   + COALESCE(er.reads,0) 
   + COALESCE(er.writes,0) AS physical_io
,COALESCE(er.open_transaction_count,-1) AS open_tran
,COALESCE(es.program_name,'') AS program_name
,es.login_time
FROM sys.dm_exec_sessions es
   INNER JOIN sys.dm_exec_requests ec2 ON es.session_id = ec2.blocking_session_id
   LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
   LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
   LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
   CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS st
where es.is_user_process = 1 
 and es.session_id <> @@spid

問題是，除了 sys.sysprocesses 之外，是否有任何 DMV 可用於獲取此資訊？

這是我用來監視導致阻塞問題的睡眠 SPID 的方法：

SELECT 
    s.session_id
   ,s.status
   ,s.login_time
   ,s.host_name
   ,s.program_name
   ,s.host_process_id
   ,s.original_login_name
   ,s.last_request_end_time
   ,CAST(t.text AS nvarchar(4000)) AS [text]
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS c
   ON s.session_id = c.session_id
CROSS APPLY (
   SELECT MAX(DB_NAME(dt.database_id)) AS database_name
   FROM sys.dm_tran_session_transactions AS st
   INNER JOIN sys.dm_tran_database_transactions AS dt
       ON st.transaction_id = dt.transaction_id
   WHERE is_user_transaction = 1
   GROUP BY st.session_id
   HAVING s.session_id = st.session_id 
) AS trans
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS t
WHERE s.session_id NOT IN (
       SELECT session_id
       FROM sys.dm_exec_requests
   )
   AND s.session_id IN (
       SELECT request_session_id
       FROM sys.dm_tran_locks
       WHERE request_status = 'GRANT'
   )
   AND STATUS = 'sleeping'
   AND is_user_process = 1;

希望這可以幫助。

要具體回答您的問題，請sys.dm_tran_database_transactions保存您要查找的資訊。

引用自：https://dba.stackexchange.com/questions/112237