☰

SQL Server 阻止查询

tag-icon • tag-icon windows-server-2008 sql-server-2005 database-performance
SQL Server 阻止查询

在过去的一个月中，我正常运行的 SQL Server 2005 SP3 服务器开始遇到随机阻塞问题。每次发生这种情况时，我都会运行阻塞脚本来查看是什么阻塞了什么（请参阅下面的代码块）。我看到了导致阻塞的 SPID，但它们通常是 TEMPDB 活动，每次我执行阻塞脚本时，它找到的导致阻塞的对象都会更改为其他对象。这使得很难找出真正导致延迟的原因。如果我在 exec_requests DMV 上运行一些查询，我会看到挂起进程的 PAGELATCH 等待类型。我已采取所有最佳实践步骤来提高 tempdb 的性能；tempdb 数据和日志文件拥有自己的卷，tempdb 是简单的恢复模型，它有 8 个数据文件，我甚至打开了跟踪标志 TF1118。我有一个用于创建新数据库的脚本，我可以使用该脚本在服务器上重现阻塞（大多数情况下）。但是我已经在这个服务器上使用创建数据库脚本超过一年了，到目前为止没有任何问题...请问有什么建议，告诉我应该寻找什么或者如何找出真正导致这种情况的原因？

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON

    DECLARE @Processes TABLE (SPID INT, Blocked INT, DBID INT, ProgramName VARCHAR(100), HostName VARCHAR(50), CMD VARCHAR(50), 
                                CPU INT, PhysicalIO INT, Status VARCHAR(50), ECID INT)
    INSERT @Processes (SPID, Blocked, DBID, ProgramName, HostName, CMD, CPU, PhysicalIO, Status, ECID)
    SELECT spid, blocked, dbid, [program_name], hostname, cmd, cpu, physical_io, status, ecid
    FROM sys.sysprocesses (NOLOCK)
    WHERE spid <> blocked

    DECLARE @BlockingIDs TABLE (ID INT)
    INSERT @BlockingIDs (ID)
    SELECT Blocked FROM @Processes WHERE Blocked IS NOT NULL AND Blocked <> 0

   -- If there are blocked processes...
    IF (SELECT COUNT(ID) FROM @BlockingIDs) > 0
        BEGIN
            DECLARE @BlockerData TABLE (RowID INT IDENTITY(1,1), BlockingSPID INT, SqlText NVARCHAR(4000), ObjectID INT, ObjectName VARCHAR(400), 
                                        DatabaseName VARCHAR(100), ProgramName VARCHAR(100), HostName VARCHAR(50), CMD VARCHAR(50))

            CREATE TABLE #ON (Name VARCHAR(400))
            INSERT @BlockerData (BlockingSPID, SqlText, ObjectID, ObjectName, DatabaseName, ProgramName, HostName, CMD)
            SELECT DISTINCT spid, master.dbo.DBA_GetSQLTextForSPID(spid), 
            master.dbo.DBA_GetSQLObjectIDForSPID(spid), '', DB_NAME([dbid]), ProgramName, HostName, CMD
            FROM @Processes
            WHERE SPID IN (SELECT ID FROM @BlockingIDs)
            ORDER BY SPID

            DECLARE @RowIndex INT,
                    @RowCount INT,
                    @ObjectID INT,
                    @ObjectName VARCHAR(200),
                    @DB VARCHAR(50),
                    @Sql NVARCHAR(300)

            SELECT @RowCount = COUNT(RowID) FROM @BlockerData
            SET @RowIndex = 1

            WHILE @RowIndex <= @RowCount
                BEGIN
                    SELECT @ObjectID = ObjectID, @DB = DatabaseName FROM @BlockerData WHERE RowID = @RowIndex
                    SET @Sql = 'SELECT Name FROM ' + @DB + '..sysObjects WHERE ID = ' + CONVERT(VARCHAR(50), @ObjectID)
                    DELETE #ON
                    INSERT #ON (Name) EXEC sp_ExecuteSql @Sql
                    UPDATE @BlockerData SET ObjectName = (SELECT Name FROM #ON) WHERE RowID = @RowIndex
                    SET @RowIndex = @RowIndex + 1
                END
            DROP TABLE #ON

            SELECT BlockingSPID, SqlText, ObjectID, ObjectName, DatabaseName, ProgramName, HostName, CMD FROM @BlockerData

            -- Identify the spids being blocked.
            SELECT t2.spid AS 'Blocked spid', t2.blocked AS 'Blocked By', 
            master.dbo.DBA_GetSQLTextForSPID(t2.spid) AS 'SQL Text', 
            t2.CPU, t2.PhysicalIO, DatabaseName = DB_NAME(t2.[dbid]), t2.ProgramName, t2.HostName, t2.Status, t2.CMD, t2.ECID
            FROM @Processes t1, @Processes t2 
            WHERE t1.spid = t2.blocked
            AND t1.ecid = t2.ecid
            AND t2.Blocked IN (SELECT ID FROM @BlockingIDs)
            ORDER BY t2.blocked, t2.spid, t2.ecid
        END
    ELSE -- No blocked processes.
        BEGIN
            SELECT 'No processes blocked.' 
        END

答案1

获取 sp_whoisactive 的副本并使用它。这应该会为您提供一些有用的信息。另外，查看 wait_stats DMV 以查看阻止程序等待的原因。如果您看到 PAGELATCH_IO，那么您可能遇到了某种存储问题。使用 perfmon 查找响应缓慢的 IO。

相关内容