A Better sp_who2 using DMVs (sp_who3)

The following code generates the same information found in sp_who2, along with some additional troubleshooting information. It also contains the SQL Statement being run, so instead of having to execute a separate DBCC INPUTBUFFER, the statement being executed is shown in the results.

Unlike sp_who2, this custom sp_who3 only shows sessions that have a current executing request.

What is also shown is the reads and writes for the current command, along with the number of reads and writes for the entire SPID. It also shows the protocol being used (TCP, NamedPipes, or Shared Memory).

The lead blocker below will show in the BlkBy column as -1.

[cc lang=”sql”]

USE [master]
GO
CREATE PROCEDURE [dbo].[sp_who3]

AS
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT
SPID = er.session_id
,BlkBy = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END
,ElapsedMS = er.total_elapsed_time
,CPU = er.cpu_time
,IOReads = er.logical_reads + er.reads
,IOWrites = er.writes
,Executions = ec.execution_count
,CommandType = er.command
,LastWaitType = er.last_wait_type
,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid)
,SQLStatement =
SUBSTRING
(
qt.text,
er.statement_start_offset/2,
CASE WHEN
(
CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END – er.statement_start_offset / 2
) < 0 THEN 0 ELSE CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset / 2 END ) ,STATUS = ses.STATUS ,[Login] = ses.login_name ,Host = ses.host_name ,DBName = DB_Name(er.database_id) ,StartTime = er.start_time ,Protocol = con.net_transport ,transaction_isolation = CASE ses.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END ,ConnectionWrites = con.num_writes ,ConnectionReads = con.num_reads ,ClientAddress = con.client_net_address ,Authentication = con.auth_scheme ,DatetimeSnapshot = GETDATE() ,plan_handle = er.plan_handle FROM sys.dm_exec_requests er LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt OUTER APPLY ( SELECT execution_count = MAX(cp.usecounts) FROM sys.dm_exec_cached_plans cp WHERE cp.plan_handle = er.plan_handle ) ec OUTER APPLY ( SELECT lead_blocker = 1 FROM master.dbo.sysprocesses sp WHERE sp.spid IN (SELECT blocked FROM master.dbo.sysprocesses WITH (NOLOCK) WHERE blocked != 0) AND sp.blocked = 0 AND sp.spid = er.session_id ) lb WHERE er.sql_handle IS NOT NULL AND er.session_id != @@SPID ORDER BY CASE WHEN lb.lead_blocker = 1 THEN -1 * 1000 ELSE -er.blocking_session_id END, er.blocking_session_id DESC, er.logical_reads + er.reads DESC, er.session_id; END [/cc]

33 comments
Scott W. Vincent 11 Jan 2017 at 3:21 pm

Used this today to track down an Access DB that was locking up a table. Having the SQL statement and IP address was very helpful. Thanks!

James 10 Sep 2015 at 10:46 am

Watch out for the < in the above code for the SQLStatement.

It should be <

CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END – er.statement_start_offset / 2 < 0

Robert Sterbal 03 Sep 2015 at 5:00 pm

Is there any value in using sys.sysprocesses instead of master.dbo.sysprocesses ?

Mr C 15 Jul 2015 at 8:31 pm

Changed the < to < and it worked

James 10 Sep 2015 at 10:51 am

That is the ‘<‘ & lt;

JC 27 Jan 2015 at 11:05 pm

When I exec sp_who3 I don’t get any results. Am I missing something

Mahmmoud 21 Jan 2015 at 10:20 am

A very helpful example of sp_who3.

Thanks a lot Derek.

Rodrigo 12 Dec 2014 at 4:33 pm

Since sys.proccesses doesn’t exist in SQL AZURE, I had to come up with some changes for it to work.

ALTER PROCEDURE [dbo].[Sp_who3]
AS
BEGIN
SET TRANSACTION isolation level READ uncommitted

SELECT SPID = er.session_id,
–,BlkBy = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END,
ElapsedMS = er.total_elapsed_time,
CPU = er.cpu_time,
IOReads = er.logical_reads + er.reads,
IOWrites = er.writes,
Executions = ec.execution_count,
CommandType = er.command,
LastWaitType = er.last_wait_type,
ObjectName = Object_schema_name(qt.objectid, dbid) + ‘.’
+ Object_name(qt.objectid, qt.dbid),
SQLStatement = Substring (qt.text, er.statement_start_offset / 2, (
CASE
WHEN
er.statement_end_offset = -1 THEN
Len
(
CONVERT(NVARCHAR(max), qt.text)) *
2
ELSE
er.statement_end_offset
END –
er.statement_start_offset ) / 2),
STATUS = ses.status,
[Login] = ses.login_name,
Host = ses.host_name,
DBName = Db_name(er.database_id),
StartTime = er.start_time,
Protocol = con.net_transport,
transaction_isolation = CASE ses.transaction_isolation_level
WHEN 0 THEN ‘Unspecified’
WHEN 1 THEN ‘Read Uncommitted’
WHEN 2 THEN ‘Read Committed’
WHEN 3 THEN ‘Repeatable’
WHEN 4 THEN ‘Serializable’
WHEN 5 THEN ‘Snapshot’
END,
ConnectionWrites = con.num_writes,
ConnectionReads = con.num_reads,
ClientAddress = con.client_net_address,
Authentication = con.auth_scheme,
DatetimeSnapshot = Getdate(),
plan_handle = er.plan_handle
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
OUTER apply sys.Dm_exec_sql_text(er.sql_handle) AS qt
OUTER APPLY
(
SELECT execution_count = MAX(cp.usecounts)
FROM sys.dm_exec_cached_plans cp
WHERE cp.plan_handle = er.plan_handle
) ec
/*OUTER APPLY
(
SELECT
lead_blocker = 1
FROM master.dbo.sysprocesses sp
WHERE sp.spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND sp.blocked = 0
AND sp.spid = er.session_id
) lb*/
WHERE er.sql_handle IS NOT NULL
AND er.session_id != @@SPID
ORDER BY
–CASE WHEN lead_blocker = 1 THEN -1 * 1000 ELSE -er.blocking_session_id END,
er.blocking_session_id DESC,
er.logical_reads + er.reads DESC,
er.session_id
END

jsaon 17 Jun 2014 at 7:21 pm

What does a negative 4 mean on the blocked spot -4?

Carlos M. 13 Jun 2014 at 10:46 pm

When I run the query these messages appears:Msg 102, Level 15, State 1, Procedure sp_who3, Line 54
Incorrect syntax near ‘.’.
Msg 102, Level 15, State 1, Procedure sp_who3, Line 60
Incorrect syntax near ‘ec’.
Msg 102, Level 15, State 1, Procedure sp_who3, Line 69
Incorrect syntax near ‘lb’.

Please help, thanks in advance.

Chito 22 Oct 2014 at 11:00 am

Is there any solutions to this error?

joe 05 Jan 2015 at 3:39 am

You have to this query using the ‘master’ database.

dick wenning 31 Mar 2014 at 8:32 am

adding the ,er.sql_handle
is handy, so you know what to kill

Bethany 12 Nov 2013 at 4:26 pm

This has worked awesomely the past few weeks, but today the results I get show this SPID (sp_who3) as the top (-1) blocker, and I am unable to run anything. How is this possible, and how to I solve it? Has anyone seen this before?

Bethany 12 Nov 2013 at 4:28 pm

Also, the LastWaitype is ‘SOS_Scheduler_Yield’

Derek Dieter 10 Dec 2013 at 4:40 am

HMM.. never seen that Bethany. Try adding nolocks to the DMV’s and/or set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the top of your script. I have not seen this block before..

k3nnyg 07 Dec 2012 at 4:17 pm

This is awesome. i made one small change that others may find useful. Preceding the order by statement, I added the following where clause line to filter out the spwo3 process. change the name of the process, dbo.sp_who3 in this case, to whatever yours shows up as. This varies depending on where you placed the stored procedure.

where qt.objectid OBJECT_ID(‘dbo.sp_who3’)
ORDER BY
er.blocking_session_id DESC,
er.logical_reads + er.reads DESC,
er.session_id

END

Jason G. 09 Oct 2013 at 4:54 pm

Am I losing my mind, or is a “not equals” missing from your WHERE clause?

Derek Dieter 10 Dec 2013 at 4:46 am

For the sp.blocked = 0 ? Nope, that right.. I know it looks weird but works.

Edward Pinto 07 Mar 2012 at 11:51 am

This worked like a Charm Derek.. I was able to pinpoint the offender really quick and nip it in the bud.

Sean 11 Jul 2011 at 1:35 pm

This script/sp rocks!

Thanks!

Peter 10 Jun 2011 at 1:29 am

Nice work but it only seems to find connections to the master,tempdb databases…

EXEC SP_WHO2 gives me about 100 result and your query only 20…

Derek Dieter 09 Sep 2011 at 10:03 am

Hi Peter,

This is because this script only shows active connections. Personally I prefer this because when troubleshooting, I can immediately see how busy the server is just by the sheer number of results. sp_who2 will show all connections and I need to figure out which ones are asleep. To change this to show sleeping connections, simply join the sessions table first, then left join on the dm_exec_requests

Derek

Steve 27 May 2011 at 6:02 am

Nice. I have a long running Select (over 50 minutes). I’m running sp_who3 with sp_who2 in the same window. who2 DiskIO column is increasing each time I rerun, but who3 IOReads and IOWrites are static at the same values as the first time I ran it in this SSMS window. I would have expected who3 to change. I can see who3 changing ElapsedMS. CPUTime looks frozen too. Is this behaving as intended? Thanks for the tool!

Steve 27 May 2011 at 6:41 am

I see now the who3 CPUTime figure is coming from the first who2 row of 7 parallel processes for my query, which is done (or not changing) in who2.

adonetok 09 Jun 2010 at 1:10 pm

It is ok in sql 2008 but can not save in sql 2000, got an error:

Incorrect syntax near the keyword ‘OUTER’.

(OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st)

Derek Dieter 11 Jun 2010 at 8:36 am

Hi adonetok,

Yes, unfortunately you cannot run this in SQL 2000. It will only work in SQL 2005+

Sam 03 Oct 2013 at 7:41 pm

DMVs are also post 2005

Derek Dieter 07 Oct 2013 at 6:24 pm

No they’re not

eatdirt 12 Feb 2010 at 10:59 am

Excellent example, this is a big help for me in our monitoring application.

Derek D. 07 Apr 2010 at 8:17 am

Great, glad this helped. I have considered altering this to include all sessions and not just active ones. I may add an additional example for that one. This helps me sometimes but I still find myself using sp_who2 a lot.

Troubleshooting SQL Server Slowdowns 20 Jun 2009 at 12:42 am

[…] fix. It is likely you have a run away query. The first thing you want to do is run sp_who2 (or sp_who3) and look for connections that have a SPID > 50 with abnormally high CPU time. The CPU time shown […]

Featured Articles

 Site Author

  • Thanks for visiting!
css.php