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

http://www.cnblogs.com/changbluesky/archive/2010/04/15/1711733.html

http://support.microsoft.com/kb/307487/zh-cn

SQL Server has encountered NN occurrence(s) of I/O requests taking longer than 15 seconds

4,数据库优化

http://www.cnblogs.com/freedom831215/archive/2010/02/23/1672286.html

5,常用SQL

http://www.cnblogs.com/acis_/archive/2009/07/28/1532616.html

6,MSSQL LOCK以及事务

http://www.cnblogs.com/buro79xxd/archive/2010/04/06/1705113.html 

Other

1,连线数

http://www.pczpg.com/a/2010/0509/8008.html 

2,检测死锁

http://www.cublog.cn/u1/46888/showart_1995427.html 

3,SQL Server死锁总结

http://kb.cnblogs.com/page/48541/

4,SQL優化34條

http://kb.cnblogs.com/page/48520/