System Statistical Functions, Stored Procedures, and the DBCC SQLPERF Command - Microsoft SQL Server 2012 Performance Tuning - page 66

,status AS ProcessStatus
,loginame AS LoginName
,hostName AS HostName
,blk AS BlockedBy
,dbname AS DatabaseName
,cmd AS CommandType
,request_id AS RequestID
FROM dbo.#tbl_SPWho
WHERE blk > 0
--Looking at only suspended processes.
SELECT
SPID AS SessionID
,Status AS ProcessStatus
,CPUTime
,DiskIO
,ProgramName
,Login AS LoginName
,HostName AS HostName
,BlkBy AS BlockedBy
,DBName AS DatabaseName
,Command AS CommandType
,REQUESTID AS RequestID
FROM dbo.#tbl_SPWho2
WHERE status = 'suspended'
How it works...
In this example, we created a temporary table named #tbl_SPWho . We first checked if the
table exists, with the IF condition. If one exists, we drop the table first. Note that the table
structure is identical to the set of columns that sp_who returns.
Next, the stored procedure sp_who is executed and its output is collected in the #tbl_SPWho
table by the INSERT…EXECUTE statements.
We also created another table called #tbl_SPWho2 and inserted the output of sp_who2 in
the same way that we did for sp_who .