I have written a little CTE to get the total blocking time of a head blocker process, and I am unsure if I should first copy all of the processes that I want the CTE to run over into a temp table and then perform the query over this - i.e. I want to be sure that the data cannot change under my feet whilst the query runs and (worst case scenario), I end up with an infinite recursive loop!
This is my SQL including the temp table - I'd prefer not to have to use the table for performance reasons, and go directly to the sysprocesses dmv inside my CTE, but I'm not sure of the possible implications of this.
DECLARE @proc TABLE(
spid SMALLINT PRIMARY KEY,
blocked SMALLINT INDEX blocked_index,
waittime BIGINT)
INSERT INTO @proc
SELECT spid, blocked, waittime
FROM master..sysprocesses
;WITH block_cte AS
(
SELECT spid, CAST(blocked AS BIGINT) [wait_time], spid [root_spid]
FROM @proc
WHERE blocked = 0
UNION ALL
SELECT blocked.spid, blocked.waittime, block_cte.spid
FROM @proc AS blocked
INNER JOIN block_cte ON blocked.blocked = block_cte.spid
)
SELECT root_spid blocking_spid, SUM(wait_time) total_blocking_time
FROM block_cte
GROUP BY root_spid