The Activity Monitor gives us a view of current connections on an instance. The monitor can be used to determine whether we have any processes blocking other processes. To open the Activity Monitor in Management Studio, right click on the Server in the Object Explorer, then select Activity Monitor.
1. Session ID: The unique number assigned to a process connected to SQL Server. This is also called a SPID. An icon next to the number represents what is happening in the connection. If you see an hourglass, you can quickly tell that the process is waiting on or is being blocked by another connection.
2. User Process Flag: Indicates whether processes that are internal SQL Server processes are connected. These processes are filtered out by default. You can change the value to see the SQL Server internal processes by clicking the drop down and selecting the appropriate value.
3. Login: The login to which the process is tied.
4. Database: The current database context for the connection.
5. Task State: Indicates whether the user is active or sleeping. (No, this doesn’t mean the user is active or sleeping at his or her keyboard; it means that SQL Server keeps the connection active even though no activity is coming from the login until the user disconnects.)
6. Done: Completed.
7. Pending: The process is waiting for a worker thread.
8. Runnable: The process has previously been active, has a connection, but has no work to do.
9. Running: The process is currently performing work.
10. Suspended: The process has work to do, but it has been stopped. Additional information about why the process is suspended may be found in the Wait Type column.
11. Command: Shows the type of command currently being executed. For example, you may see SELECT, DBCC, INSERT, or AWAITING COMMAND here, to name a few. This won’t show you the actual query that the user is executing, but it does highlight what type of activity is being run on your server. To see the actual command, select a row in this table, right click and choose Details.
12. Application: The application that is connecting to your instance. This can be set by the developer in the connection string.
13. Wait Time (ms): If the process is being blocked or waiting for another process to complete, this indicates how long the process has been waiting, in milliseconds; it will have a value of 0 if the process is not waiting.
14. Wait Type: Indicates the event you are waiting on.
15. Wait Resource: The text representation of the resource you are waiting on.
16. Blocked By: The Session ID (SPID) that is blocking this connection.
17. Head Blocker: A value of 1 means the Blocked By Session ID is the head of the blocking chain,otherwise 0.
18. Memory Use (KB): The current amount of memory used by this connection. Number of pages in the Procedure cache attributed to this connection. Note this was reported in pages in prior releases.
19. Host: The login’s workstation or server name. This is a really useful item, but in some cases you may have a Web server connecting to your SQL Server, which may make this less important.
20. Workload Group: The name of the Resource Governor workload group for this query.
