sp_whoisactive and sp_who3

For looking into some SQL performance problems on systems I normally use these procedures.

sp_whoisactive 
sp_who3 

On running sp_whoisactive, if the output is showing a CXPACKET wait type that’s when sp_who3 comes into play.  Running sp_who3 with a SPID number after the procedure like “exec sp_who3 125″ will give you the wait type for each thread within the process.

When doing this recently on a system sp_whoisactive showed me that CXPACKET was the wait type.  After digging into the process with sp_who3 I saw that one of the threads was waiting on SOS_SCHEDULER_YIELD.  I then used sp_whoisactive to get the execution plan showing me the missing index which needed to be created.  In this case there was a clustered index on the table which was being scanned.  Based on the page count output from SET STATISTICS IO we were scanning the entire table every time the query was run.  This massively expensive query was causing the query to parallelize and the run time to go insanely high.

Hopefully you’ll find these stored procedures to be useful in your performance troubleshooting.  They aren’t hard to use, but they sure are useful.

sp_who3 - A new version of sp_who2

While working I came across this interesting version of sp_who2 named as sp_who3

It can be most useful when trying to diagnose slow running queries as it can provide a wealth of information in a single screen.

CREATE PROCEDURE sp_who3 

( 
    @SessionID int = NULL
) 
AS
BEGIN
SELECT
    SPID                = er.session_id 
    ,Status             = ses.status 
    ,[Login]            = ses.login_name 
    ,Host               = ses.host_name 
    ,BlkBy              = er.blocking_session_id 
    ,DBName             = DB_Name(er.database_id) 
    ,CommandType        = er.command 
    ,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 
        ) 
    ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) 
    ,ElapsedMS          = er.total_elapsed_time 
    ,CPUTime            = er.cpu_time 
    ,IOReads            = er.logical_reads + er.reads 
    ,IOWrites           = er.writes 
    ,LastWaitType       = er.last_wait_type 
    ,StartTime          = er.start_time 
    ,Protocol           = con.net_transport 
    ,ConnectionWrites   = con.num_writes 
    ,ConnectionReads    = con.num_reads 
    ,ClientAddress      = con.client_net_address 
    ,Authentication     = con.auth_scheme 
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 
WHERE er.session_id > 50 
    AND @SessionID IS NULL OR er.session_id = @SessionID 
ORDER BY
    er.blocking_session_id DESC
    ,er.session_id 
  
END

Usage:
exec sp_who3