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

去獲取一份 sp_whoisactive 並使用它。那應該為您提供一些好處。還要查看 wait_stats DMV 以了解阻塞器上等待的原因。如果您看到 PAGELATCH_IO，那麼您可能遇到了某種儲存問題。使用 perfmon 查找響應緩慢的 IO。

引用自：https://serverfault.com/questions/288434