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.