Monitoring with SQL Server Management Studio
Use SQL Server Management Studio to view the following information about current 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, or terminate a selected process.
Use Activity Monitor in SQL Server Management Studio to perform ad hoc monitoring of an instance of SQL Server. Activity Monitor allows you to determine 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.
You can monitor SQL Server activity by using the sp_who and sp_lock system stored procedures.
For the descriptions of the process information in the Activity Monitor window, see Activity Monitor (Process Info Page).
For the descriptions of the lock information in the Activity Monitor window, see Activity Monitor (Locks by Object Page).
In SQL Server Service Pack 2 (SP2), right-click an Object Explorer node or a node in the Details pane, point to Reports, and then point to Standard Reports to display the list of reports associated with the selected node. Not all nodes have standard reports.
In SP1, reports are available for some of the Object Explorer nodes. When a report is available for a node, the Reports icon on the Summary pane is active. Use the drop-down list to select the reports that you want to view.