SQL includes a simple stored procedure, sp_who that allows for fast monitoring of SQL server resources by examining active and inactive SQL processes. Even with an efficient facade and coding style, sp_who can be used to assess performance in several different ways:

1. Detecting connection leaks – Even a well-developed .NET application can be brought to its knees with a single connection leak, causing the application’s connection pool to fill and prevent further SQL queries.
2. Assessing application load – How much of a resource hog is your application in a live environment? If you can identify SQL-based bottlenecks in your application, you’ll be able to optimize them.

Executing sp_who
Sp_who can be executed with the following query:

EXEC sp_who

The resulting set will include several system rows, but will notably include rows that include:

Utilizing sp_who
At first glance, sp_who’s usefulness may not be immediately visible. If you’re in a development or test environment, you can run sp_who while testing your application — if you have an excessive number of processes attached to your dbname (database), then you probably have a connection leak.

Sp_who is also very useful for assessing the SQL load of an application. Even if you don’t have any connection leaks, you might be interested in viewing the SQL server load of your application in a live environment. You can examine application load with even more depth using sp_who2, which will be discussed later.

Variations of sp_who
Sp_who does have a single parameter that can display either all the processes from one username, all the active processes (which merely excludes inactive processes), and a single process with a given process ID, as seen below:

EXEC sp_who 'JoeUser'; --Display all processes associated with 'JoeUser' as a loginname
EXEC sp_who 'active'; --Exclude all inactive processes
EXEC sp_who 50; --Display the process with an spid of '50'

The parameters for sp_who do not directly allow for sorting out the processes for a given dbname, however, the following snippet displays processes with a dbname of ‘MyDB’ — this technique can be used when filtering any result from a stored procedure as well:

DECLARE @retTable TABLE (
	spid smallint,
	ecid smallint,
	status nchar(30),
	loginname nchar(128),
	hostname nchar(128),
	blk char(5),
	dbname nchar(128),
	cmd nchar(16),
	request_id INT
)

INSERT INTO @retTable EXEC sp_who

SELECT * FROM @retTable WHERE dbname = 'MyDB'

An advanced (undocumented) version: sp_who2
MSSQL 2005 contains an advanced version of sp_who called sp_who2 — how original. Sp_who2 contains additional information that you might find helpful, especially if you’re interested in examining your application’s performance in a live environment. The three main contributions of sp_who2 include:

• CPUTime – The total amount of CPU time (in milliseconds) that the process has taken
• DiskIO – The total amount of disk read/write time (in milliseconds) that the process has taken
• LastBatch – The date and time of the last query from the process

If you’re trying to optimize your application, it is worth your time to identify processes that are hogging CPUTime and DiskIO time and attempt to reduce their load on the server.

I have barely scratched the surface of examining performance monitoring with SQL Server, but this article should be a good start to get you interested in further research. The main function of sp_who and sp_who2 is to detect connection leaks and to do preliminary research for the performance of your application. If you have any suggestions for a next-step to performance monitoring, feel free to leave a comment.