Monitoring SQL  «Prev  Next»

Lesson 3Monitoring connections with SQL-EM
Objective Use SQL-EM to monitor Connections.

Monitoring Connections with SQL-EM

Using SQL-EM to monitor Connections

In addition to the sp_who procedure, the SQL-EM process monitor can also be used to monitor connections. It displays a much more detailed level of information than the sp_who stored procedure does. To get to the Process Info display, you must follow these steps from inside SQL-EM:
  1. Select the server group
  2. Select the server
  3. Select Management
  4. Select Current Activity
  5. Select Process Info

The following Slide Show illustrates the SQL-EM process monitor screen.
Note that this screen is too large to show the entire thing on one page, so the SlideShow simulates scrolling across the screen from left to right.

Spid: The system process identifier, User: The user that is connected
1)
  1. Spid: The system process identifier,
  2. User: The user that is connected,
  3. Database: the database that the user is in,
  4. Status: The status of the process

Open Transaction: The number of nested transactions that the connection has open
2)
  1. Open Transaction: The number of nested transactions that the connection has open,
  2. Command: A high level representation of the last command that ran,
  3. Application: The name of the application that is connecting to SQL Server (This column only has a value if the application sets it).

Wait Time and Wait Type
3)
  1. Wait Time: The length of time the connection has been waiting (if the connection is not waiting this is a zero.)
  2. Wait Type: identifies what type of resource the process was waiting for (Some of the common classes of waits are locks, latches, or SQL Server internal resources.)
  3. Wait Resource: Identifies the resource that the connection is waiting for,
  4. CPU: Cumulative CPU time for the process of all statements executed with the set statistics time option turned on

Physical IO: The total number of reads and writes performed by the procedure
4)
  1. Physical IO: The total number of reads and writes performed by the process,
  2. Memory Usage: The number of procedure cache pages allocated to this process (If the number is negative, the process is freeing pages belonging to another process.)
  3. Login Time: The date and time that the connection was made.

Last Batch - the date and time that the connection performed an RPC or an execute statement
5)
  1. Last Batch - the date and time that the connection performed an RPC or an execute statement,
  2. Host: The name of the computer that the client is connecting from

Network Library: Identifies the netlib dll used to connect to the server
6)
  1. Network Library: Identifies the netlib dll used to connect to the server ,
  2. Network Address: Identifies the client's network interface card,
  3. Blocked By : The SPID of the process that is blocking the current process.

Blocking: The number of processes waiting for this processes resources
7) Blocking: The number of processes waiting for this processes resources

Monitoring with SQL Server - Enterprise Manager

Use SQL Server Enterprise Manager to view the following information about current Microsoft SQL Server activity:
  1. Current user connections and locks.
  2. Process number, status, locks, and commands that active users are running.
  3. Objects that are locked, and the kinds of locks that are present.

If you are a system administrator, you can view additional information about a selected process, send a message to a user who is connected currently to an instance of SQL Server, or terminate a selected process. Use the current activity window in SQL Server Enterprise Manager to perform ad hoc monitoring of an instance of SQL Server. This allows you to determine, at a glance, the volume and general types of activity on the system, for example:
  1. Current blocked and blocking transactions.
  2. Currently connected users on an instance of SQL Server and the last statement executed.
  3. Locks that are in effect.
SQL Server activity can be monitored using the sp_who and sp_lock system stored procedures.



sql_emprocdetail
sql_emprocdetail

When monitoring processes with SQL-EM, you can double-click a process and see the last command that the process executed. Click the View Image button to see an example. If you hit the Kill button it will terminate the process. You can also terminate processes with the Kill command. The next lesson covers how to view locked resources.

sp_who Quiz

Click the Quiz link below to test your knowledge of the concepts presented in this module so far.
sp_who quiz