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:
Select the server group
Select the server
Select Current Activity
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.
Monitoring with SQL Server - Enterprise Manager
Use SQL Server Enterprise Manager to view the following information about current Microsoft SQL Server activity:
Current user connections and locks.
Process number, status, locks, and commands that active users are running.
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:
Current blocked and blocking transactions.
Currently connected users on an instance of SQL Server and the last statement executed.
Locks that are in effect.
SQL Server activity can be monitored using the sp_who and sp_lock system stored procedures.
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.
Click the Quiz link below to test your knowledge of the concepts presented in this module so far.