How I use sp_whoisactive and sp_who3

Published On: 2012-03-22By:

On occasion I’m asked to come and look into some SQL performance problems on systems.  Normally when I’m doing this the customer doesn’t have any third party tools installed and they are actively having problems, and these problems are leading to lost revenue or at least really pissed off customers and/or employees.  As the systems are currently in a system down situation there’s usually no time to pitch, buy, install and configure a performance monitoring solution from one of the many vendors.

When I run across these sorts of problems the first thing that I do is put sp_whoisactive and sp_who3 on the server.  As these are just stored procedures getting approval to install them is usually pretty easy.

Once the stored procedures are installed sp_whoisactive will pretty quickly tell you the stored procedures which are having performance problems.  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.

Once we added the index we figured out the man hours wasted per day by having the index missing.  We assumed that the procedure was run 3 times per second over the course of a 12 hour period and the query was taking 3 seconds to complete before the index was added.  After the index was created the query was running in just a few milliseconds.  This single query running for 3 seconds, 3 times per second, for 12 hours comes out to 108 man hours wasted while this one query was being run.

Using two community written stored procedures and a few minutes of troubleshooting time 108 man hours per day are now being saved for that specific company.

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.


Contact the Author | Contact DCAC


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link