One of the first lines of defense in determining the causes of database slowdowns is to use sp_who2. sp_who2 shows all the sessions that are currently established in the database. These are denoted as SPID‘s, or Server process Id’s. Running sp_who2 is easy, all that is required is to type sp_who2 and execute it, however it’s a good idea to call it with EXEC.
EXEC sp_who2
The first 50 results are system SPIDs. Generally these do not effect slowdowns of the system. These system events include the Checkpoint writer, Log writers and task schedulers. User processes are SPID numbers 50 and over. In diagnosing slowdowns it is these SPIDs that are the potential resource hogs.
sp_who2 also takes a optional parameter of a SPID. If a spid is passed, then the results of sp_who2 only show the row or rows of the executing SPID.
EXEC sp_who2 243
There are four main things to look for when when diagnosing slowdowns.
- Blocking
- High CPU usage
- High IO usage
- Multiple entries for the same SPID representing parallelism.
When a number is shown in the column named BlkBy, this represents the SPID that is currently stopping the SPID in the row shown. Sometimes many rows will show SPID numbers in the BlkBy column. This is because there is a chain of blockers. The way this occurs usually starts with one “lead” blocker blocking another process. In turn, the process that is being blocked, blocks others. This occurs down a chain. It can be a messy situation. In order to rectify, you may have to kill the lead blocker. If it happens often, you will want to research why this particular process is blocking. So, before you kill any process, find out what statement it is running first. To do this, execute DBCC Inputbuffer.
High CPUTime or High DiskIO time is usually spotted by comparing the relative CPUTime to the DiskIO time. It should be noted that CPUTime and DiskIO time represent the sum of all executions since the SPID has been active. It may take some training before you are able to spot a high number here. At times, you will see very high CPUTimes and almost no corresponding DiskIO. This is usually indicative of a bad execution plan. For more information see this article (slow performance).
Multiple rows that have the same SPID are known as Parallel processes. This happens when SQL Server has determined that a particular query is going to take a long time to run (according to the parallel settings at the server level). When this happens, SQL Server will launch multiple threads for the same procedure. When this happens often, it can have a devastating effect on SQL Server. Particularly IO. To rectify, either raise the query threshold for parallelism or turn down the maximum processors that parallelism can use (MAXDOP) Max degree of parallelism, a setting at the server level.
