sp_who3

I’ve long been a fan of sp_who2 for identifying blocks on SQL Server, but it’s a bit clunky and there’s a lot of stuff to wade through.

Which is why I’ve collated several scripts from around the place and combined them into sp_who3

I’m using a try_cast function which won’t work prior to SQL2012 I believe.

Install the stored proc with the code at the bottom. To run it, just type into SSMS:

sp_who3

This will return three result sets.

The first is filtered version of sp_who2 which will show only those SPIDs which are blocked, as well as the SPIDs which are blocking.

The second returns the standard sp_who2 list,

The third returns the exact SQL for each SPID, as well as detailed performance and blocking stats, c/- a query from msdn.

The output

USE [master]
GO

CREATE PROCEDURE [dbo].[sp_who3] as

DECLARE @Table TABLE (
SPID INT,
Status VARCHAR(MAX),
LOGIN VARCHAR(MAX),
HostName VARCHAR(MAX),
BlkBy VARCHAR(MAX),
DBName VARCHAR(MAX),
Command VARCHAR(MAX),
CPUTime INT,
DiskIO INT,
LastBatch VARCHAR(MAX),
ProgramName VARCHAR(MAX),
SPID_1 INT,
REQUESTID INT
);

INSERT INTO @Table EXEC sp_who2;

SELECT *
FROM @Table
where spid in (select distinct try_cast(blkby as int) from @table)
or BlkBy <> ' .'
order by spid;

SELECT *
FROM @Table
order by blkby desc, spid;

-- credit to Balmukund on msdn
-- https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f0abc3f1-d643-4d6c-be3b-0ada636a4da2/how-do-i-see-the-full-sqllquery-text-of-a-spid?forum=sqldatabaseengine

SELECT s.session_id, 
r.status, 
r.blocking_session_id 'Blk by', 
r.wait_type, 
wait_resource, 
r.wait_time / (1000.0) 'Wait Sec', 
r.cpu_time, 
r.logical_reads, 
r.reads, 
r.writes, 
r.total_elapsed_time / (1000.0) 'Elaps Sec', 
Substring(st.TEXT,(r.statement_start_offset / 2) + 1, 
((CASE r.statement_end_offset 
WHEN -1 
THEN Datalength(st.TEXT) 
ELSE r.statement_end_offset 
END - r.statement_start_offset) / 2) + 1) AS statement_text, 
Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid)) + N'.' + Quotename(Object_name(st.objectid,st.dbid)), 
'') AS command_text, 
r.command, 
s.login_name, 
s.host_name, 
s.program_name, 
s.last_request_end_time, 
s.login_time, 
r.open_transaction_count 
FROM sys.dm_exec_sessions AS s 
JOIN sys.dm_exec_requests AS r 
ON r.session_id = s.session_id 
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st 
WHERE r.session_id != @@SPID 
ORDER BY r.cpu_time desc, r.status, 
r.blocking_session_id, 
s.session_id

GO