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
