SQLSERVER——查看阻塞信息

 --转自：https://www.cnblogs.com/sdlfx/p/8876406.html

CREATE  PROCEDURE [dbo].[sp_who_lock]
AS
    BEGIN
        DECLARE @spid INT ,
            @bl INT ,
            @intTransactionCountOnEntry INT ,
            @intRowcount INT ,
            @intCountProperties INT ,
            @intCounter INT,
            @sql_handle VARBINARY(64)
        DECLARE @tmp_lock_who TABLE (id INT IDENTITY(1, 1) , spid SMALLINT ,bl SMALLINT,sql_handle VARBINARY(64))
        IF @@ERROR <> 0
            RETURN @@ERROR        ;
        WITH tb_blocked AS (SELECT spid, blocked, sql_handle FROM master..sysprocesses WHERE blocked > 0)
        INSERT  INTO @tmp_lock_who( spid ,bl, sql_handle)
        SELECT DISTINCT  blocked,0, p_bl.sql_handle
        FROM   tb_blocked
            CROSS APPLY (SELECT p_bl.sql_handle FROM master..sysprocesses p_bl WHERE p_bl.spid = tb_blocked.blocked) p_bl
        WHERE   NOT EXISTS ( SELECT * FROM   tb_blocked a WHERE  tb_blocked.blocked = a.spid )
        UNION ALL
        SELECT spid, blocked, sql_handle FROM tb_blocked

        IF @@ERROR <> 0
            RETURN @@ERROR
 
-- 找到临时表的记录数
        SELECT  @intCountProperties = COUNT(*),
                @intCounter = 1
        FROM    @tmp_lock_who
 
        IF @@ERROR <> 0
            RETURN @@ERROR
 
        IF @intCountProperties = 0
            SELECT  '现在没有阻塞和死锁信息' AS message
-- 循环开始
        WHILE @intCounter <= @intCountProperties
            BEGIN
-- 取第一条记录
                SELECT  @spid = spid, @bl = bl,    @sql_handle = sql_handle
                FROM    @tmp_lock_who
                WHERE   id = @intCounter
                BEGIN
                    IF @bl = 0
                    BEGIN
                        SELECT '阻塞根源' + CAST(@spid AS VARCHAR(10)) AS [description], text AS [sql_text] FROM sys.dm_exec_sql_text(@sql_handle) AS dest
                    END
                    ELSE
                    BEGIN
                        SELECT CAST(@spid AS VARCHAR(10)) + '被' + CAST(@bl AS VARCHAR(10)) + '阻塞' AS [description], text AS [sql_text] FROM sys.dm_exec_sql_text(@sql_handle) AS dest
                    END
                    DBCC INPUTBUFFER(@spid)
                END
-- 循环指针下移
                SET @intCounter = @intCounter + 1
            END

        RETURN 0
    END
GO

--执行
exec [sp_who_lock]