This article is still in-progress!

When Control / SQL performance degrades, often it can be the result of SQL processes that have become stuck or are blocking other processes from being completed. The Stored Procedure 'sp_who2' and management command 'DBCC InputBuffer' can be used to identify the specific T-SQL commands that are causing the problem.

exec sp_who2

In the result example above, what you see is a list of Session Processes (just think “SQL activities”) currently running on the server. Here are what the columns tell you:

SPID: The currently running processes' IDs, known as SPIDs (Session Process ID's).

Status: The status of the process. Most often you will be looking for 'SUSPENDED' or 'sleeping'.

Login: The SQL or Windows account under which the process was begun.

HostName: The name of the computer from which the process was begun.

BlkBy: If the process is being blocked, this will display the SPID of the process blocking it. In the above example, 56 is blocked by 55, and 55 is blocked by 54.

DBName: The name of the database on which the process is running.

Command: The beginning of the T-SQL command used in the process. Not the entire command.

CPUTime: The cumulative CPU time since the process was begun.

DiskIO: The amount of I/O (Input/Output) that the process has performed so far. Input = writing to the disk, Output = reading from the disk.

LastBatch: The last time the process