ViewProcessBlockingLite

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[ViewProcessBlockingLite]    Script Date: 7/10/2013 4:21:54 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'ViewProcessBlockingLite' AND type = 'P')
    DROP PROC ViewProcessBlockingLite
GO

CREATE PROC [dbo].[ViewProcessBlockingLite]
AS
BEGIN
SET NOCOUNT ON;

SELECT
             s.spid AS SPID, BlockingSPID = s.blocked, DatabaseName = DB_NAME(s.dbid),
             s.program_name, s.loginame, s.hostname, s.login_time, s.last_batch, s.waittime, s.waitresource,
             ObjectName = OBJECT_NAME(objectid, s.dbid), Definition = CAST(text AS VARCHAR(MAX))
 INTO        #Processes
 FROM      sys.sysprocesses s
 CROSS APPLY sys.dm_exec_sql_text (sql_handle)
 WHERE
            s.spid > 50
;

WITH Blocking(SPID, BlockingSPID, DB, BlockingObject, [Blocking Statement / Definition], program, login, host, login_time, last_batch, waittime_ms, waitresource, RowNo, LevelRow)
 AS
 (
      SELECT
       s.SPID, s.BlockingSPID, s.DatabaseName, s.ObjectName, s.Definition,
       s.program_name, s.loginame, s.hostname, s.login_time, s.last_batch, s.waittime, s.waitresource,
       ROW_NUMBER() OVER(ORDER BY s.SPID),
       0 AS LevelRow
     FROM
       #Processes s
       JOIN #Processes s1 ON s.SPID = s1.BlockingSPID
     WHERE
       s.BlockingSPID = 0
     UNION ALL
     SELECT
       r.SPID,  r.BlockingSPID, r.DatabaseName, r.ObjectName, r.Definition,
       r.program_name, r.loginame, r.hostname, r.login_time, r.last_batch, r.waittime, r.waitresource,
       d.RowNo,
       d.LevelRow + 1
     FROM
       #Processes r
      JOIN Blocking d ON r.BlockingSPID = d.SPID
     WHERE
       r.BlockingSPID > 0
 )
 SELECT *
 INTO #BlockTree
 FROM Blocking
 ORDER BY RowNo, LevelRow

-- Top HEAD SPID
DECLARE @topHeadSPID int
SELECT TOP 1 @topHeadSPID = SPID
    FROM #BlockTree WHERE LevelRow = 0 ORDER BY last_batch

-- filter duplicated rows & trim statement column
SELECT DISTINCT
SPID, BlockingSPID, DB, BlockingObject, LEFT([Blocking Statement / Definition], 100) AS [Statement/Definition (first 100 chars)], program, [login], host, login_time, last_batch, waittime_ms, waitresource
INTO #temp1
FROM #BlockTree

-- insert top head into result
SELECT TOP 1 * INTO #result FROM #temp1 WHERE SPID = @topHeadSPID

-- insert top 10 victims of top head into result
INSERT #result SELECT TOP 10 * FROM #temp1 WHERE BlockingSPID = @topHeadSPID ORDER BY waittime_ms DESC

SELECT * FROM #result ORDER BY BlockingSPID, waittime_ms DESC

DROP TABLE #result
DROP TABLE #temp1
DROP TABLE #BlockTree
DROP TABLE #Processes

END

GO