MSSQL – sp_who3, A better sp_who2

As a DBA, sp_who2 is a procedure that I use on a daily basis. It’s a good procedure, but you always have to copy the entire result into excel sheet and then sort through it to find the SPID of the session that you were looking for in the first place. And sometimes, you just don’t have the time to do that. So there are two things you can do to solve this issue. First, you can get the result of the sp_who2 into a @table and then look through there (which is way easier than using excel), or you can create your own procedure that accepts a parameter as the DBName and filters the outcome based on that. Let me show you both and you can choose whichever works for you best.

1. Sort through the result of sp_who2 via @table

So, if all you want to do is find out what processes are running against a specific database, you can copy and paste the following query and execute it. Make sure to edit line 35 and replace <DATABASE NAME GOES HERE> with the name of the database you have.


DECLARE @whotbl TABLE
    (
      SPID        INT    NULL
     ,Status    VARCHAR(50)    NULL
     ,Login        SYSNAME    NULL
     ,HostName    SYSNAME    NULL
     ,BlkBy        VARCHAR(5)    NULL
     ,DBName    SYSNAME    NULL
     ,Command    VARCHAR(1000)    NULL
     ,CPUTime    INT    NULL
     ,DiskIO    INT    NULL
     ,LastBatch VARCHAR(50)    NULL
     ,ProgramName VARCHAR(200)    NULL
     ,SPID2        INT    NULL
     ,RequestID INT    NULL
     )

     INSERT INTO @whotbl
     EXEC sp_who2

    SELECT W.*
          ,CommandText = sql.text
          ,ExecutionPlan   = pln.query_plan
          ,ObjectName  = so.name
          ,der.percent_complete
          ,der.estimated_completion_time
          --,CommandType =der.command
      FROM @whotbl  W
 LEFT JOIN sys.dm_exec_requests der
        ON der.session_id = w.SPID
       OUTER APPLY SYS.dm_exec_sql_text (der.sql_handle) Sql
       OUTER APPLY sys.dm_exec_query_plan (der.plan_handle) pln
 LEFT JOIN sys.objects so
        ON so.object_id = sql.objectid
      WHERE DBName = '<DATABASE NAME GOES HERE>'

2. Create a better sp_who2 procedure

If you will be using this more than once, it’s better to create a procedure that you can pass the name of the database to it and it could return the result to you. If that’s the case, copy and paste the following and execute it to create the procedure:


IF OBJECT_ID('sp_who3','P') IS NOT NULL DROP PROC sp_who3

GO
CREATE PROC sp_who3 @DBNAME nvarchar(100) = NULL
AS

    DECLARE @whotbl TABLE
    (
      SPID        INT    NULL
     ,Status    VARCHAR(50)    NULL
     ,Login        SYSNAME    NULL
     ,HostName    SYSNAME    NULL
     ,BlkBy        VARCHAR(5)    NULL
     ,DBName    SYSNAME    NULL
     ,Command    VARCHAR(1000)    NULL
     ,CPUTime    INT    NULL
     ,DiskIO    INT    NULL
     ,LastBatch VARCHAR(50)    NULL
     ,ProgramName VARCHAR(200)    NULL
     ,SPID2        INT    NULL
     ,RequestID INT    NULL
     )

     INSERT INTO @whotbl
     EXEC sp_who2

    SELECT W.*
          ,CommandText = sql.text
          ,ExecutionPlan   = pln.query_plan
          ,ObjectName  = so.name
          ,der.percent_complete
          ,der.estimated_completion_time
          --,CommandType =der.command
      FROM @whotbl  W
 LEFT JOIN sys.dm_exec_requests der
        ON der.session_id = w.SPID
       OUTER APPLY SYS.dm_exec_sql_text (der.sql_handle) Sql
       OUTER APPLY sys.dm_exec_query_plan (der.plan_handle) pln
 LEFT JOIN sys.objects so
        ON so.object_id = sql.objectid
      WHERE DBName = ISNULL(@DBNAME,DBName)

go

You can use the procedure similar to sp_who2 (without argument) or with a @DBNAME = ‘ ‘ argument, like bellow:

sp_who3 @DBNAME = '<DATABASE NAME GOES HERE>'

Obviously, the credit for the code goes to someone else. I just thought of improving it a bit. Hope it helps you.

One comment on “MSSQL – sp_who3, A better sp_who2

Leave a reply