Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question
    
Have you made sure your stats are up to date and indexes well defragged? –  SqlACID Jul 8 '11 at 23:50
    
Each server has ~250 database on it and I run maintenance on databases every week. –  David George Jul 20 '11 at 18:56
add comment

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
    
Thanks for the reply Denny! I do have Adam's procedure on that box, I'll have to try and run that next time this occurs. However I have been running my own scripts to check activity and waits using exec_requests and last wait types. The majority of the time all exec_requests get a status of 'suspended' and the wait is usually PAGELATCH_SH or PAGELATCH_EX; with shared being more common...any thoughts? –  David George Jul 11 '11 at 12:17
    
I just had this issue today, the worst one so far.. I ran sp_whoisactive and I would get different results each time. Nothing really helpful, using perfmon I could see write/B/sec was the highest for tempdb's log file, but it wasn't as high as my other server which was operating just fine...of all the tasks that were showing up as suspended in exec_request I'd say about 80% were PAGEIOLATCH_EX, and 20% PAGEIOLATCH_SH... I have no idea what caused it this morning or why I couldn't fix it. This was the first time I actually had to restart the SQL server process. –  David George Jul 11 '11 at 15:57
    
Also, I see blocking constantly throughout these nightmares, but everytime I run a check to see the SPID is something new causing the blocks, often some object executing against tempdb... –  David George Jul 11 '11 at 16:28
    
OK, you are having IO problems. First thing to look at is the writes per second and reads per second. Also look at the seconds per read and seconds per write. If the seconds per read/write numbers are 0.050 or higher then the IO is responding to slow. Either you've got a problem at the IO layer (failing disk, controller, cable, etc) or you are simply pushing to much data to the disk. How many reads per sec and writes per sec do you see? What's the disk config behind the drive in question? –  mrdenny Jul 11 '11 at 20:09
    
Thank you for the response @MrDenny A local RAID10 (8 - 146GB 15K drives) array make up volumes: C: (windows) H: (tempdb) I: (templog) K: (programs) All the rest of SQL is stored on separate LUNs (Data, Audit, Logs, and Indexes) H: (Read/sec = Average 7) (Writes/sec = Average 3.7) I: (Read/sec = Average 0) (Writes/sec = Average 15) This is an HP server and the array reports healthy via the ACU. However, this configuration has been deployed on this server for 1.5 years with no issues till recently though. –  David George Jul 12 '11 at 18:22
show 16 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.