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

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