sp_who2 - filtering and sorting the results

Category : Tips User Rating : 4.5 Stars      Views : 5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




The stored procedure sp_who2 lists all current processes connected to a SQL Server :
exec sp_who2

A typical resultset from Management Studio is :

sp_who2 results

sp_who2 is one of the most useful and widely used stored procedures, along with its predecessor sp_who. However it is also one of the most frustrating as it only takes a single parameter and the results cannot be ordered. For a large server with a lot of connections this can be a real nuisance. I usually store the results in a temporary table and then filter and/or order the results from there :

CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255),
      BlkBy  VARCHAR(255),DBName  VARCHAR(255),
      Command VARCHAR(255),CPUTime INT,
      DiskIO INT,LastBatch VARCHAR(255),
      ProgramName VARCHAR(255),SPID2 INT,
      REQUESTID INT)
INSERT INTO #sp_who2 EXEC sp_who2
SELECT      *
FROM        #sp_who2
-- Add any filtering of the results here :
WHERE       DBName <> 'master'
-- Add any sorting of the results here :
ORDER BY    DBName ASC
 
DROP TABLE #sp_who2
Some people encapsulate the above code in a stored procedure and run that, but my preference is always to run it as a script.

Finally

A word of warning. Sp_who2 is undocumented, meaning that Microsoft could change it in the future without warning. I’ve tested the code above on SQL Server 2005, 2008 and 2008 R2, however it’s possible that the columns or datatypes returned could change in future versions which would require a small change in the code.

Related Articles

The following articles may also be of interest :

Link back to this article : https://www.sqlmatters.com/Articles/sp_who2 - filtering and sorting the results.aspx

Keywords

TSQL,sp_who,sp_who2,who


Comments
Post by TheDeb on Thu 24 Jan 2013 18:35. Report Inappropriate Post

Perfect! Just what I was looking for. Thank you!
Post by Jake on Wed 30 Jan 2013 14:42. Report Inappropriate Post

Thanks for the simply query, exactly what I needed.
Post by chris on Mon 04 Feb 2013 14:30. Report Inappropriate Post

V. Useful thanks ;)

Works on SS2012
Post by Henk on Tue 01 Oct 2013 09:19. Report Inappropriate Post

Works like a charm & very usefull - thank you!
Post by bernardo on Thu 10 Oct 2013 16:59. Report Inappropriate Post

muy bueno, gracias
Post by Geraldo on Wed 23 Oct 2013 20:01. Report Inappropriate Post

Valeu!!!! Me ajudou.. Thank you!!!
Post by Faith on Wed 15 Jan 2014 14:01. Report Inappropriate Post

how to pull data result from sp_who2 to asp.net web app?
Post by turboys on Tue 18 Feb 2014 11:07. Report Inappropriate Post

Can sys.sp_who2 modify ?when I right click it says Modify / Refresh - therefore I selected Modify and Execute- Received an error Invalid object name 'sys.sp_who2'.

anyone can help?

Post by Peter Keats on Tue 04 Mar 2014 16:05. Report Inappropriate Post

Nice. Thanks for making it look so easy. :)
Post by Jeff on Fri 18 Apr 2014 16:45. Report Inappropriate Post

Its easier to use a table variable so you don't have to deal with temp tables:

declare @tempTable table (SPID INT,Status VARCHAR(255),
Login VARCHAR(255),HostName VARCHAR(255),
BlkBy VARCHAR(255),DBName VARCHAR(255),
Command VARCHAR(255),CPUTime INT,
DiskIO INT,LastBatch VARCHAR(255),
ProgramName VARCHAR(255),SPID2 INT,
REQUESTID INT);

INSERT INTO @tempTable
EXEC sp_who2

select *
from @tempTable
Post by John on Fri 18 Jul 2014 16:52. Report Inappropriate Post

This is awesome! Exactly what I was looking for. Many thanks!!!!!
Post by ARSH on Sat 20 Dec 2014 10:02. Report Inappropriate Post

THANKS NICE INFO


Post by Dan MacNeil on Wed 07 Jan 2015 19:32. Report Inappropriate Post
Website : http://howto.omacneil.org/
Thank you for putting this out there for Dr Google to share with me. My life is easier now. I will help a older person across the street with my time savings.

Also nice to have general principle of inserting stored procedure output into a temp table.
Post by Damien Cornwall on Wed 01 Apr 2015 15:11. Report Inappropriate Post

AWESOME!! You just shaved years off my life
Post by Foley on Mon 06 Apr 2015 19:16. Report Inappropriate Post

AWESOME JOB!!!
Post by Oscar Luna on Wed 15 Apr 2015 22:24. Report Inappropriate Post

trank you,
Post by Hudson Santos on Fri 02 Oct 2015 16:59. Report Inappropriate Post
Website : http://www.smallbee.com.br
Great workaround! Easy to filter when you get lots of databases.
Post by Fabio on Mon 28 Dec 2015 19:32. Report Inappropriate Post

Great article!
Post by Joe Hayes on Mon 28 Dec 2015 21:37. Report Inappropriate Post

Thanks! I've used this several times, and it's very useful :)
Post by Siva on Wed 09 Mar 2016 20:19. Report Inappropriate Post

Excellent Solution
Post by Karthikeyan A on Tue 14 Jun 2016 10:23. Report Inappropriate Post

select 'dbcc inputbuffer(' + convert(char,(spid)) + ')' , * from sys.sysprocesses

You can try this system table for all info of Sp_who2
Post by DiegoSierra on Mon 25 Jul 2016 20:03. Report Inappropriate Post

Excellent information. Solution for different needs and/or preferences
Post by DiegoSierra on Mon 25 Jul 2016 20:53. Report Inappropriate Post

Here is my own version of sp_who (sp_who3) combining info from the posts above:

declare @tempTable table
( SPID SMALLINT
, Status NCHAR(30)
, Login NCHAR(128)
, HostName NCHAR(128)
, BlkBy SMALLINT
, DBName VARCHAR(255)
, Command NCHAR(16)
, CPUTime INT
, Disk_IO BIGINT
, LastBatch DATETIME
, ProgramName NCHAR(128)
, REQUESTID INT
, HostProcess NCHAR(10)
, LoginTime DATETIME
, OpenTransactions SMALLINT
)

INSERT INTO @tempTable
SELECT SPID
, sysprocesses.STATUS
, LOGINAME
, HOSTNAME
, BLOCKED
, sysdatabases.name
, CMD
, CPU
, PHYSICAL_IO
, LAST_BATCH
, PROGRAM_NAME
, REQUEST_ID
, HOSTPROCESS
, LOGIN_TIME
, OPEN_TRAN
FROM sys.sysprocesses
JOIN master.dbo.sysdatabases
ON sysprocesses.DBID = sysdatabases.DBID

SELECT *
FROM @tempTable
-- Add any filtering of the results here :
WHERE DBName <> 'master'
-- Add any sorting of the results here :
ORDER BY SPID ASC

Post by Oscar Luna on Fri 19 Aug 2016 16:08. Report Inappropriate Post

Hello! Here is my own version of sp_who combined with INPUTBUFFER:

BEGIN TRANSACTION --Cambie LastBatch por ActiveTime y ProgramName por CurrentSQL
CREATE TABLE #sp_who3 (SPID VARCHAR(255), Status VARCHAR(255), Login VARCHAR(255), HostName VARCHAR(255), BlockedBy VARCHAR(255), DBName VARCHAR(255), Command VARCHAR(255), CPUTime INT, DiskIO INT, ActiveTime VARCHAR(1024), CurrentSQL VARCHAR(4444), SPID2 INT, REQUESTID INT)
INSERT INTO #sp_who3 EXEC sp_who2 active

UPDATE #sp_who3 SET ActiveTime = CONVERT(VARCHAR(8), GETDATE() - SUBSTRING(ActiveTime,7,14), 108), CurrentSQL = '-'

---------------------------------------------------------------------------------------------------------------------
DECLARE @SpidActual VARCHAR(255)
DECLARE cursorwho3 CURSOR FOR (SELECT DISTINCT SPID FROM #sp_who3 WHERE (ActiveTime > '00:00:07') AND (Login <> 'sa') )

OPEN cursorwho3
FETCH NEXT FROM cursorwho3 INTO @SpidActual

WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #inputbufferTable (myEventType nvarchar(256), myParameters int, myEventInfo nvarchar(4000) )
INSERT INTO #inputbufferTable EXEC(' BEGIN TRY
DBCC INPUTBUFFER(' + @SpidActual + ')
END TRY
BEGIN CATCH
END CATCH ' )
UPDATE #sp_who3 SET CurrentSQL = (SELECT myEventInfo FROM #inputbufferTable) WHERE SPID = @SpidActual
DROP TABLE #inputbufferTable
FETCH NEXT FROM cursorwho3 INTO @SpidActual
END

CLOSE cursorwho3
DEALLOCATE cursorwho3

---------------------------------------------------------------------------------------------------------------------
SELECT ActiveTime, SPID, Login, BlockedBy, CurrentSQL, Status, Command, DBName, CPUTime, DiskIO, HostName
FROM #sp_who3
WHERE (Login <> 'sa') AND (CurrentSQL IS NOT NULL)
ORDER BY ActiveTime DESC

DROP TABLE #sp_who3
ROLLBACK TRANSACTION
Post by Thami on Tue 17 Jan 2017 08:27. Report Inappropriate Post

Thank you very much. Worked for me to pinpoint an issue that has been persisting for days.
Post by Vijay on Sun 12 Feb 2017 21:46. Report Inappropriate Post

What is SPID ?
Post by Eduardo Henrique Belinatti on Mon 11 Sep 2017 18:55. Report Inappropriate Post

Vijay, SPID is Server Process ID
Post by Cpt Trips on Tue 13 Mar 2018 13:15. Report Inappropriate Post

Sry, all these solution suck a bit as Microsoft is not capable of returning a proper dataset from sp_who2 :/
Post by shiwangini on Sat 14 Jul 2018 06:07. Report Inappropriate Post

wow. This is the same i was looking for
Post by Mr. SQL on Mon 23 Jul 2018 13:30. Report Inappropriate Post

sp_whoisactive
Post by Alexey Kuzmin on Sat 30 Mar 2019 11:50. Report Inappropriate Post

thanks a lot for all scripts!
Post by Carlos Pachon on Fri 26 Jul 2019 15:07. Report Inappropriate Post

Thanks for the scripts guys, i have this one for that

SELECT
cpu_time, total_elapsed_time,
session_Id, [DatabaseName] = DB_NAME(sp.dbid), Hostname, [User] = nt_username,
[Status] = ER.status, [WaitType] = wait_type,
[QueryText] = 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),
[Parent Query] = qt.text, [Program] = program_name, start_time
FROM sys.dm_exec_requests AS ER
INNER JOIN sys.sysprocesses AS SP ON ER.session_id = SP.spid
CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS QT
WHERE session_Id <> @@SPID
ORDER BY cpu_time DESC, total_elapsed_time DESC ;

-------------------------------------------------------------------

SELECT spid, 'KILL ' + CONVERT (VARCHAR ,spid) 'Kill' --,*
,'DBCC INPUTBUFFER ' + '('+CONVERT (VARCHAR ,spid) + ')' 'Dbcc'
,loginame,hostname,blocked,dbid,program_name,login_time
from master..sysprocesses
where dbid = '5'
order by blocked desc,loginame,hostname --

BTW your scripts are the best, tank you!!
Post by Giri on Mon 19 Aug 2019 17:44. Report Inappropriate Post

Great stuff. thanks a ton for this.
Post by Martin on Thu 17 Sep 2020 10:28. Report Inappropriate Post

Very useful. tnx
Post by TanPro on Sat 06 Nov 2021 08:52. Report Inappropriate Post
Website : http://khongcoweb.com
It works very well with SQLServer 2019. Thank so much!!!
Post by Tim on Wed 12 Jul 2023 21:18. Report Inappropriate Post

I loved your query. I turned it into a sproc with parameters:

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================================
-- Description: Using SP_Who2 this allows the user to customize the output
-- Parameters -
-- @dbname - Database Name is required
-- @orderby - (Optional) Choices are SPID, Login, or BlkBy - Default is SPID
-- @SortDirection - (Optional) Choices are "A" for ascending (default), and
-- and "D" for Descending
-- =======================================================================================
Create PROCEDURE [dbo].[sp_who2_custom]
-- Add the parameters for the stored procedure here
@dbname varchar(50),
@orderby varchar(50) = 'spid',
@SortDirection VARCHAR(10) = 'A'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

CREATE TABLE #sp_who2 (SPID varchar(10),Status VARCHAR(255),
Login VARCHAR(255),HostName VARCHAR(255),
BlkBy VARCHAR(255),DBName VARCHAR(255),
Command VARCHAR(255),CPUTime INT,
DiskIO INT,LastBatch VARCHAR(255),
ProgramName VARCHAR(255),SPID2 INT,
REQUESTID INT)
INSERT INTO #sp_who2 EXEC sp_who2
SELECT *
FROM #sp_who2
-- Add any filtering of the results here :
WHERE DBName = @dbname
-- Add any sorting of the results here :
ORDER BY

CASE WHEN @SortDirection = 'A' THEN
CASE
WHEN @orderby = 'SPID' Then SPID
WHEN @orderby = 'Login' Then Login
WHEN @orderby = 'BlkBy' Then BlkBy
END
END ASC
, CASE WHEN @SortDirection = 'D' THEN
CASE
WHEN @orderby = 'SPID' Then SPID
WHEN @orderby = 'Login' Then Login
WHEN @orderby = 'BlkBy' Then BlkBy
END
END DESC

DROP TABLE #sp_who2
END
GO

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered