mssql 2008r2环境下sql语句阻塞的跟踪查询，死锁的跟踪查询，改善方案

/*

功能说明: 数据库死锁与阻塞语句查询

修改说明：Modfiy BY LY 2013-11-22

*/

DECLARE @spid INT

DECLARE @blk INT

DECLARE @count INT

DECLARE @index INT

DECLARE @LOCK TINYINT



SET @LOCK=0



CREATE TABLE #temp_who_lock

(

 id   INT IDENTITY(1, 1),

 spid INT,

 blk  INT

)



--if @@error<>0 return @@error    

INSERT INTO #temp_who_lock

        (spid,

         blk)

SELECT 0,

   blocked

FROM   (SELECT *

    FROM   master..sysprocesses

    WHERE  blocked > 0)a

WHERE  NOT EXISTS(SELECT *

              FROM   master..sysprocesses

              WHERE  a.blocked = spid

                     AND blocked > 0)

UNION

SELECT spid,

   blocked

FROM   master..sysprocesses

WHERE  blocked > 0



--if @@error<>0 return @@error    

SELECT @count = Count(*),

   @index = 1

FROM   #temp_who_lock



--select @count,@index



--if @@error<>0 return @@error    

IF @count = 0

BEGIN

  SELECT '没有阻塞和死锁信息' 

--return 0    

END



WHILE @index <= @count

BEGIN

  IF EXISTS(SELECT 1

            FROM   #temp_who_lock a

            WHERE  id > @index

                   AND EXISTS(SELECT 1

                              FROM   #temp_who_lock

                              WHERE  id <= @index

                                     AND a.blk = spid))

    BEGIN

        SET @LOCK=1



        SELECT @spid = spid,

               @blk = blk

        FROM   #temp_who_lock

        WHERE  id = @index



        SELECT  '引起数据库死锁的是: ' + Cast(@spid AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' ;



        SELECT @spid,

               @blk







        DBCC inputbuffer(@spid)





        DBCC inputbuffer(@blk)

    END



  SET @index=@index + 1

END



IF @LOCK = 0

BEGIN

  SET @index=1



  WHILE @index <= @count

    BEGIN

        SELECT @spid = spid,

               @blk = blk

        FROM   #temp_who_lock

        WHERE  id = @index



        IF @spid = 0

          SELECT '引起阻塞的是:' + Cast(@blk AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' 

        ELSE

          SELECT '进程号SPID：' + Cast(@spid AS VARCHAR(10)) + '被' + '进程号SPID：' + Cast(@blk AS VARCHAR(10)) + '阻塞,其当前进程执行的SQL语法如下'



        PRINT ( LTRIM(@spid) + ''+ LTRIM(@blk));

        if(@spid <> 0)

        BEGIN

           DBCC inputbuffer(@spid)   --

         END



        DBCC inputbuffer(@blk)   --引起阻塞语句



        SET @index=@index + 1

    END

END



DROP TABLE #temp_who_lock



--return 0    

--KILL 54