显示SQL SERVER当前实例的阻塞信息

IF OBJECT_ID('p_blockinfo','P') IS NOT NULL
	DROP PROC [p_blockinfo]
GO
-- ========================================================
-- 功能：显示当前实例的阻塞信息（包括被阻塞进程和阻塞进程）
--       可在实例的任一数据库连接中执行
--       欢迎转载，但请保留本注释信息，谢谢！
-- 作者：coleling
-- 日期：2010-12-17
-- 版本：SQL Server 2005/2008
-- ========================================================
CREATE PROC [dbo].[p_blockinfo]
AS
BEGIN
	--取得当前所有被阻塞的request,和当前正在request的阻塞者
	;WITH cte AS
	(
		SELECT a.session_id,a.blocking_session_id,a.database_id,a.user_id,
			a.cpu_time,a.open_transaction_count,a.status, spid = a.session_id, type = 0,
			(SELECT SUBSTRING(t.text, a.statement_start_offset / 2, ((CASE WHEN a.statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),t.text)) * 2) ELSE a.statement_end_offset END) - a.statement_start_offset) / 2)) AS sql_statement
		FROM sys.dm_exec_requests a
			CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) t
		WHERE a.blocking_session_id <> 0
		UNION ALL
		SELECT a.session_id,a.blocking_session_id,a.database_id,a.user_id,
			a.cpu_time,a.open_transaction_count,a.status, spid = a.blocking_session_id, type = 1,
			(SELECT SUBSTRING(t.text, a.statement_start_offset / 2, ((CASE WHEN a.statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),t.text)) * 2) ELSE a.statement_end_offset END) - a.statement_start_offset) / 2)) AS sql_statement
		FROM sys.dm_exec_requests a
			JOIN cte b ON a.session_id = b.blocking_session_id
			CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) t
		--WHERE a.blocking_session_id = 0 --注：如果存在死锁，则这里会无限递归
	)
	SELECT 标志=CASE a.type WHEN 0 THEN '被阻塞者' ELSE '阻塞者' END,进程ID=a.session_id,阻塞进程ID=a.blocking_session_id,
		数据库ID=a.database_id, 数据库名=db_name(a.database_id),用户ID=a.user_id,用户名=b.login_name,
		累计CPU时间=a.cpu_time, 登陆时间=b.login_time, 打开事务数 = a.open_transaction_count, 进程状态=a.status,
		工作站名=b.host_name,应用程序名=b.program_name,工作站进程ID=b.host_process_id,
		域名=b.nt_domain,IP地址=c.client_net_address,a.spid,a.type,进程的SQL语句 = sql_statement
	INTO #t 
	FROM cte a
	JOIN sys.dm_exec_sessions b ON a.session_id = b.session_id
	JOIN sys.dm_exec_connections c ON a.session_id = c.session_id
	--判断是否存在当前无request的阻塞进程ID
	IF EXISTS(SELECT 1 FROM #t WHERE 阻塞进程ID not in (SELECT 进程ID FROM #t))
	BEGIN
		--建立临时表，存放当前没有request的SQL语句(可能不够精确)
		CREATE TABLE #t1(id INT identity(1,1),a varchar(8000),b Int null,EventInfo varchar(8000) null)
		DECLARE @spid INT
		--建立游标，依次取得SQL
		DECLARE curspid CURSOR FOR
			SELECT 阻塞进程ID FROM #t WHERE 阻塞进程ID not in (SELECT 进程ID FROM #t) ORDER BY 阻塞进程ID
		OPEN curspid
		FETCH NEXT FROM curspid INTO @spid
		WHILE @@FETCH_STATUS = 0
		BEGIN
			INSERT #t1 EXEC('dbcc inputbuffer('+@spid+')')
			FETCH NEXT FROM curspid INTO @spid
		END
		CLOSE curspid
		DEALLOCATE curspid
		--插入#t
		INSERT #t 
		SELECT '阻塞者',b.session_id,0,a.数据库ID, db_name(a.数据库ID),USER_ID(b.login_name),b.login_name,
			b.cpu_time, b.login_time, ISNULL(e.cn,0), b.status,
			b.host_name,b.program_name,b.host_process_id,b.nt_domain,c.client_net_address,a.进程ID,1,f.EventInfo
		FROM #t a
			JOIN sys.dm_exec_sessions b ON a.阻塞进程ID = b.session_id
			JOIN sys.dm_exec_connections c ON a.阻塞进程ID = c.session_id
			JOIN (SELECT id = ROW_NUMBER() OVER(ORDER BY 阻塞进程ID), 阻塞进程ID FROM #t WHERE 阻塞进程ID not in (SELECT 进程ID FROM #t)) d ON a.阻塞进程ID = d.阻塞进程ID
			LEFT JOIN (SELECT session_id,cn=COUNT(*) FROM sys.dm_tran_session_transactions GROUP BY session_id) e ON a.阻塞进程ID = e.session_id
			JOIN #t1 f ON d.id = f.id
	END 
	SELECT * FROM #t ORDER BY spid,type
	DROP TABLE #t
	DROP TABLE #t1
END
GO