SQL SERVER 查看阻塞信息

　经常会需要分析SQLSERVER的阻塞情况，尤其是某些SQL操作异常缓慢从而怀疑是有人在搞事情的情况下。网上有许多一模一样的帖子，是关于sp_who_lock这个存储过程的，然而，网上流传的这个是略有问题的（被阻塞的SQL输出有误），为此改造了一下实现，顺便优化了一下输出结构：

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

 

　　关于输出的SQL文本，我使用了sys.dm_exec_sql_text与DBCC INPUTBUFFER两种方式，这两种方式是的结果是略有差别的，在SQL批里有多条SQL语句的情形下，前者可以精确定位到当前阻塞/被阻塞是哪一条语句，然而输出的并非原始的SQL文本，而后者则输出的是原始SQL批，但并不能精确定位是哪一条。两者结合方可更快的排查问题。举例如下：

　　假设有如下两个链接的SQL语句：

　　链接一：　　

BEGIN TRAN
    UPDATE dbo.t_UserDataAccess SET ObjectValue = '' WHERE UserID = 1024

　　链接二：

BEGIN TRAN
    UPDATE dbo.t_UserDataAccess SET ObjectValue = '' WHERE UserID = 1023
    SELECT * FROM dbo.t_UserDataAccess AS tuda WHERE UserID = 1024

　　在链接一和链接二顺序执行的情形下，很显然，链接2的SELECT语句将会被阻塞，这时来看sys.dm_exec_sql_text和DBCC INPUTBUFFER的不同表现：

应该不需要解释了。

原文