问Server阻塞查询

EN

    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