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.
You can view activity on an instance of SQL Server in the following ways:
Activity Monitor
Use the Activity Monitor in SQL Server Management Studio to perform ad hoc monitoring of an instance of SQL Server. This enables 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.
For more information, see Activity Monitor.
Standard reports
Use the standard reports that are included with Management Studio to view activity on an instance of SQL Server. To access the reports, right-click the instance name in Object Explorer, point to Reports, point to Standard Reports, and then click the report that you want to view. For example, you can view reports such as Activity - Top Sessions or Activity - All Blocking Transactions.