Monitoring SQL  «Prev  Next»

Lesson 2 Connections
Objective Monitor SQL Server connections.

Monitor SQL Server Connections

When users access SQL Server, they make a connection[1] to SQL Server. All connections exist independently of one another, even if the same application makes multiple connections. SQL Server allows you to monitor the activity of each connection through SQL and SQL-EM.

Using SQL to monitor connections

The simplest way to monitor how users and processes are connected with SQL is to use the system stored procedure [2], sp_who.
This procedure will retrieve information about all active connections. The syntax of sp_who is as follows:
sp_who [[@login_name =] "login|spid|ACTIVE"]

While SQL key words are always case independent, stored procedure names' case sensitivity depends on the sort order installed.
You should always type stored procedure names in lower case.
The one parameter for sp_who, @login_name, allows you to limit the results of sp_who to a specific login, or spid, or to all active processes.

sp_who produces the following output: (Note that this output has been reformatted to fit on your screen).

A note about status

In the sp_who output, you may see different types of status. The most common ones are:
  1. background: Identifies this as a SQL Server task.
  2. runable: The task would run if it could get access to the CPU.
  3. running: The process is running, sleeping.
  4. waiting: The process is waiting.
In the next lesson, another method for monitoring connections will be discussed.

[1] Connection: A person or application that has logged onto SQL Server.

[2] System stored procedure: A procedure stored in the master database which begins with sp_ that is used to perform system functions.