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
Results of 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.
1. Blocking
2. High
CPU usage
3. High
IO usage
4. 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(SPID).
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.
Multiple rows that have the same SPID
are known as Parellel 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 (Max DOP) Max degree of parallelism, a setting at the
server level.
