Here is another version that also gives you the lead blocker when blocking occurs. This is designed to be run in SSMS for SS 2005 thru 2008 r2.
SET NOCOUNT ON
GO
IF OBJECT_ID('tempdb..#curSQL') IS NOT NULL
DROP TABLE #curSQL
GO
CREATE TABLE #curSQL
(spID SMALLINT,
blocked VARCHAR(25),
hostName VARCHAR(2000),
dbName VARCHAR(100),
cmd VARCHAR(100),
sqlText NTEXT,
sqlcmd NTEXT,
phyIO BIGINT,
Status VARCHAR(100),
programName VARCHAR(1000),
loginTime DATETIME,
lastBatch DATETIME
)
DECLARE @spID smallint,
@Blocked VARCHAR(25),
@sqltext VARCHAR(max),
@sqlcmd VARCHAR(max),
@physio BIGINT,
@hostName VARCHAR(2000),
@desSPID SMALLINT,
@dbName VARCHAR(100),
@Cmd VARCHAR(100),
@status VARCHAR(100),
@programName VARCHAR(1000),
@loginTime DATETIME,
@lastBatch DATETIME
-- Set desired SPID. If NULL, it will return all non-system SPID's.
-- SET @desSPID = 4704
BEGIN TRY
IF @desSPID IS NOT NULL
DECLARE spID_cursor CURSOR
FORWARD_ONLY READ_ONLY
FOR SELECT spid, CASE blocked WHEN 0 THEN 'NO' ELSE 'YES BY SPID: ' + CAST(blocked AS VARCHAR) END blocked, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
FROM sys.sysprocesses
WHERE spid = @desSPID
GROUP BY spid, blocked, physical_io, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
ORDER BY spid
ELSE
DECLARE spID_cursor CURSOR
FORWARD_ONLY READ_ONLY
FOR SELECT spid, CASE blocked WHEN 0 THEN 'NO' ELSE 'YES BY SPID: ' + CAST(blocked AS VARCHAR) END blocked, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
FROM sys.sysprocesses
WHERE spid > 50 and spid <> @@SPID and status IN ('running', 'runnable', 'suspended')
GROUP BY spid, blocked, physical_io, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
ORDER BY spid
OPEN spID_cursor
FETCH NEXT
FROM spID_cursor
INTO @spID, @Blocked, @hostName, @dbName, @cmd, @physio, @status, @programName, @loginTime, @lastBatch
WHILE @@FETCH_STATUS = 0
BEGIN
/*
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = @spID
*/
SELECT
@sqltext = A.text,
@sqlcmd = SUBSTRING(A.text, sp.stmt_start / 2,
(CASE WHEN sp.stmt_end = -1 THEN DATALENGTH(A.text)ELSE sp.stmt_end END - sp.stmt_start )/2)
FROM
sys.sysprocesses sp
OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) as A
WHERE
spid = @spID
INSERT INTO #curSQL
(spID, blocked, hostName, dbName, cmd, sqlText, sqlcmd, phyIO, Status, programName, loginTime, lastBatch)
VALUES(@spID, @Blocked, @hostName, @dbName, @cmd, @sqltext, @sqlcmd, @physio, @status, @programName, @loginTime, @lastBatch)
-- SELECT @spID, @Blocked, @hostName, @dbName, @cmd, @sqltext, @sqlcmd, @physio, @status, @programName, @loginTime, @lastBatch
--FROM ::fn_get_sql(@sqltext)
FETCH NEXT
FROM spID_cursor
INTO @spID, @Blocked, @hostName, @dbName, @cmd, @physio, @status, @programName, @loginTime, @lastBatch
END
CLOSE spID_cursor
DEALLOCATE spID_cursor
SELECT * FROM #curSQL ORDER BY blocked desc, spid
-- If there is blocking, print out blocking info.
IF (SELECT sum(blocked) from sys.sysprocesses WHERE spid > 50) > 0
--IF (SELECT TOP (1) COUNT(*) FROM #curSQL WHERE blocked = 'YES') > 0
BEGIN
/*SELECT x.session_id AS 'Blocked Session ID',
(
-- Query gets XML text for the sql query for the session_id
SELECT text AS [text()]
FROM sys.dm_exec_sql_text(x.sql_handle)
FOR XML PATH(''), TYPE
)AS 'Blocked SQL Text',
x.host_name,
x.login_name,
x.start_time,
x.totalReads,
x.totalWrites,
x.totalCPU,
x.writes_in_tempdb,
COALESCE(x.blocking_session_id, 0) AS 'Blocking Session ID',
(
SELECT p.text
FROM
(
-- Query gets the corresponding sql_handle info to find the XML text in the next query
SELECT MIN(sql_handle) AS sql_handle
FROM sys.dm_exec_requests r2
WHERE r2.session_id = x.blocking_session_id
) AS r_blocking
CROSS APPLY
(
-- Query will pull back the XML text for a blocking session if there is any from the sql_haldle
SELECT text AS [text()]
FROM sys.dm_exec_sql_text(r_blocking.sql_handle)
FOR XML PATH(''), TYPE
) p (text)
) AS 'Blocking Text'
FROM
(
-- Query returns active session_id and metadata about the session for resource, blocking, and sql_handle
SELECT r.session_id,
s.host_name,
s.login_name,
r.start_time,
r.sql_handle,
r.blocking_session_id,
SUM(r.reads) AS totalReads,
SUM(r.writes) AS totalWrites,
SUM(r.cpu_time) AS totalCPU,
SUM(tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
JOIN sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id
--JOIN #curSQL cs ON r.session_id = cs.spID
WHERE r.status IN ('running', 'runnable', 'suspended') AND
r.blocking_session_id <> 0 AND
r.session_id <> @@SPID
GROUP BY r.session_id,
s.host_name,
s.login_name,
r.start_time,
r.sql_handle,
r.blocking_session_id
) x */
select
'LEAD BLOCKER: ' + CAST(spid as varchar(20)), loginame, cpu, memusage, physical_io, *
from
master..sysprocesses a
where
exists ( select b.*
from master..sysprocesses b
where b.blocked > 0 and
b.blocked = a.spid ) and not
exists ( select b.*
from master..sysprocesses b
where b.blocked > 0 and
b.spid = a.spid )
order by spid
END-- If there is blocking, print out blocking info.
END TRY
BEGIN CATCH
CLOSE spID_cursor
DEALLOCATE spID_cursor
SELECT ERROR_NUMBER() ErrorNBR, ERROR_SEVERITY() Severity, ERROR_LINE() ErrorLine, ERROR_MESSAGE() Msg
PRINT 'There was an error in the script.'
END CATCH