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.
Denny
