SQL Server blocking queries

database-performancesql-server-2005windows-server-2008

My normal running SQL Server 2005 SP3 servers started experiencing random blocking issues over the past month. Each time it happens I run my blocking script to see whats blocking what (see code block below). I see SPIDs that are causing blocks, but they generally tend to be TEMPDB activities and each time I execute my blocking script the objects it finds causing blocks changes to something else. This makes it really hard to find out what is really causing the hold up. If I run some queries on the exec_requests DMV I see wait types of PAGELATCH for the suspended processes. I've taken all the best practices steps to give tempdb a performance boost; tempdb data and log files are own their own volumes, tempdb is recovery model simple, it has 8 data files, and I even turned on the trace flag TF1118. I have a script that I use to create new databases and I can use that script to reproduce the blocking on the server (most of the time). But I've used my create database script for over a year on this server with no issues till now… Please any advice on what to look for or how I can find out what is truly causing this?

    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

Best Answer

Go grab a copy of sp_whoisactive and use that. That should provide you with some good into. Also look at the wait_stats DMV to see what the cause of the waiting is on the blocker. If you are seeing PAGELATCH_IO then you've probably got some sort of storage issue going on. Use perfmon to look for slowly responding IO.

Related Topic