MS SQL发生死锁以及tempdb的优化资源总结

最新推荐文章于 2023-10-09 15:07:09 发布

weixin_30466953

最新推荐文章于 2023-10-09 15:07:09 发布

阅读量49

点赞数

原文链接：http://www.cnblogs.com/Athrun/archive/2010/05/12/1733557.html

版权

1,查看系统中是否有阻塞,以及造成阻塞的原因

代码

CREATE PROCEDURE [ DBO ] . [ SP_WHO_LOCK ]
AS
BEGIN
     DECLARE @SPID INT , @BL INT ,
     @INTTRANSACTIONCOUNTONENTRY INT ,
     @INTROWCOUNT INT , @INTCOUNTPROPERTIES INT , @INTCOUNTER INT

     CREATE TABLE #TMP_LOCK_WHO
    (
        ID INT IDENTITY ( 1 , 1 ),
        SPID SMALLINT ,
        BL SMALLINT
    )

     IF @@ERROR <> 0 RETURN @@ERROR

     INSERT INTO #TMP_LOCK_WHO(SPID,BL)
     SELECT 0 ,BLOCKED
     FROM ( SELECT * FROM SYSPROCESSES WHERE BLOCKED > 0 ) A
     WHERE NOT EXISTS ( SELECT * FROM ( SELECT * FROM SYSPROCESSES WHERE BLOCKED > 0 ) B
     WHERE A.BLOCKED = SPID)
     UNION SELECT SPID,BLOCKED FROM SYSPROCESSES WHERE BLOCKED > 0

     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 FROM #TMP_LOCK_WHO WHERE ID = @INTCOUNTER
         BEGIN
             IF @SPID = 0
                 SELECT ' 引起數據庫死鎖的是: ' + CAST ( @BL AS VARCHAR ( 10 )) + ' 進程號,其執行的SQL語法如下 '
             ELSE
                 SELECT ' 進程號SPID： ' + CAST ( @SPID AS VARCHAR ( 10 )) + ' 被 ' + ' 進程號SPID： ' + CAST ( @BL AS VARCHAR ( 10 )) + ' 阻塞,其當前進程執行的SQL語法如下 '
                 DBCC INPUTBUFFER ( @BL )
         END

          -- 循環指針下移
         SET @INTCOUNTER = @INTCOUNTER + 1
     END
     DROP TABLE #TMP_LOCK_WHO
     RETURN 0
END
GO

说明

DBCC INPUTBUFFER(SPID) 显示从客户端发送到 Microsoft SQL Server 实例的最后一个语句。

http://technet.microsoft.com/zh-cn/library/ms187730.aspx

2,SP_LOCK,SP_WHO说明

SP_LOCK 报告有关锁的信息。

http://msdn.microsoft.com/zh-cn/library/ms187749.aspx

SP_WHO 提供有关 Microsoft SQL Server 数据库引擎实例中的当前用户、会话和进程的信息。可以筛选信息以便只返回那些属于特定用户或特定会话的非空闲进程。

http://msdn.microsoft.com/zh-cn/library/ms174313.aspx

另外系统中还有一个SP_WHO2(未查证是否所有版本都有,目前看到的是SQL2000 SP2中有)

3,TEMPDB优化

http://msdn.microsoft.com/zh-cn/library/ms175527.aspx

http://msdn.microsoft.com/zh-cn/library/ms345368.aspx