Sql-Server
如何找到阻塞其他程序的睡眠會話的 T-SQL？
我有一個名為 sp_radhe 的儲存過程，我把它放在我的伺服器上，它一直在幫助我“看到”內部發生的事情。
這是這個儲存過程的程式碼：
USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --DROP PROCEDURE dbo.sp_radhe GO CREATE PROCEDURE dbo.sp_radhe AS /* ======================================================================= Script : SP_RADHE Author : Marcelo Miorelli Date : 04 MAR 2013 Wednesday Desc : shows the current processes Usage : sp_radhe -- same as sp_who2 ======================================================================= History Date Action User Desc ----------------------------------------------------------------------- 27-oct-2014 changed Marcelo Miorelli commented out the line --and es.status = 'running' so the procedure returns any es.status ======================================================================= */ --====================================== -- describe primary blocks of processing --====================================== ------------------------------------------------ -- describe action of logical groups of commands ------------------------------------------------ -- describe individual actions within a command set BEGIN SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 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 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 LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st where es.is_user_process = 1 and es.session_id <> @@spid --and es.status = 'running' GO exec sys.sp_MS_marksystemobject 'sp_radhe' GO但是，當一個程序被其他一些不活動的程序阻塞時，我正在努力找出阻塞程式碼的 T-SQL。
例如：
您可以在上圖中看到的會話 92 是一個選擇，會話 75 是我讓事務處於打開狀態的更新。
第92屆會議
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SELECT TOP 1000 [accountID] ,[accountCreateKeyID] ,[totalAccountCreditValueLocal] ,[accountCreateDate] ,[createdDate] ,[createdBy] ,[modifiedDate] ,[modifiedBy] FROM [TableBackups].[dbo].[_AO20150806_crm_build_account_DoNotDelete] WITH (HOLDLOCK)會議 75
BEGIN TRANSACTION T1 SELECT @@TRANCOUNT update [TableBackups].[dbo].[_AO20150806_crm_build_account_DoNotDelete] set [totalAccountCreditValueLocal] = 1000 where accountID = 1如何找到阻塞會話的程式碼，在這種情況下，會話狀態為“睡眠”時的會話 75？
新版本 這個新版本也顯示了阻塞會話，但是，我無法找到如何從睡眠會話中獲取數據庫名稱和其他數據。
SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 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 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 LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st where es.is_user_process = 1 and es.session_id <> @@spid UNION 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 LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address 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
如何找到阻塞會話的程式碼，在這種情況下，會話狀態為“睡眠”時的會話 75？
新版本這個新版本也顯示了阻塞會話，但是，我找不到如何從睡眠會話中獲取數據庫名稱和其他數據。
您可以使用
SELECT db_name(S.database_id) AS DatabaseName, ST.text FROM sys.dm_exec_connections AS C JOIN sys.dm_exec_sessions AS S ON S.session_id = C.session_id OUTER APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST WHERE C.session_id = 75;