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.
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
1 Comment
Thanks for sharing, this is a fantastic blog. Really looking forward to read more. Want more. Selle Liam Pernick