SQL Server Management Studio Standard Reports - Activity - All Sessions and Activity - Top Sessions

I'm continuing my serie s on the Standard Reports in SQL Server Management Studio, and today I'm covering two of them: Activity - All Sessions and Activity - Top Sessions. These are part of the a activity series of reports that provide more detail than the "Activity Monitor" panel.

Activity - All Sessions

I'll start with the All Sessions report. This is one of the most useful activity reports, since it provides an overview of the sessions (which are just the connections to SQL Server, either internal or external) and their The "outside" cell that contains all of the others shows the Login Name, which is the name of the user or process that is running statements on the system. That way all of your activity is broken out by user. The report takes a little time to run, since it's collecting a lot of data. Here's the first level of the report:

Session ID	The session number associated with each active primary connection.
Login Time	Time when session was established.
Last Request End Time	Time of the last completion of a request on the session.
Host Name	Name of the client workstation that is specific to a session. The value is NULL for internal sessions.
Program Name	Name of client program that initiated the session.
# Connections	Shows the number of connections this Login has.
CPU Time (ms)	CPU time, in milliseconds, that is used by this session so far.
Memory Usage (KB)	Number of 8-KB pages of memory used by this session.
Total Scheduled Time (ms)	Total time, in milliseconds, for which the requests in the session were scheduled for execution.
Total Elapsed Time (ms)	Time, in milliseconds, since the session was established.
# Reads	Number of reads performed during this session, by this session.
# Writes	Number of writes performed during this session, by this session.

The next layer of the report contains the Connection ID's within the session:

Connection ID	A number that identifies each connection uniquely.
Connect Time	A timestamp showing when the connection was established.
# Reads	Number of packet reads that have occurred over this connection.
# Writes	Number of packet writes that have occurred over this connection.
Last Read Time	A timestamp showing when the last read occurred over this connection.
Last Write Time	A timestamp showing when the last write occurred over this connection.
Client Net Address	Host address of the client connecting to this server.
Client TCP Port	Port number on the client computer that is associated with this connection.

Below that is the band that shows any Request ID's belonging to the Connection ID:

Request ID	A number that identifies each request uniquely.
Start Time	A timestamp showing when the request started.
Command	Identifies the type of command that is being processed. Common command types include the following: SELECT, INSERT, UPDATE, DELETE, BACKUP LOG, BACKUP DB, DBCC, WAITFOR. The text of the request can be retrieved by using the sys.dm_exec_sql_text dynamic management function with the corresponding sql_handle for the request. Internal system processes set the command, depending on the type of task that they perform. Tasks can include the following: LOCK MONITOR, CHECKPOINTLAZY WRITER.
# Open Transactions	Number of result sets that are open for this request.
Percent Complete	Percent of work completed for certain operations, rollbacks included. Note: This does not provide progress data for queries.
Estimated Completion Time	You actually can't rely on this number - but it is intended to be a guess from the plan guide for how long this request will take to complete.

Finally, if there is a Request ID when the report runs (again, for a Connection ID), there's a portion of the SQL Statement that is running for each Request ID:

SQL Statement

A portion of the SQL Statement running on the Request ID.

Activity - Top Sessions
This report shows some of the same information as the "All Sessions" reports, except from the Session ID viewpoint. Again, this is a very useful; report, because of the way it groups the information. You get the following columns:

Session ID

Login Time

Last Report End Time

Host Name

Program Name

# Connections

CPU Time (ms)

Memory Usage (KB)

Total Scheduled Time (ms)

Total Elapsed Time (ms)

# Reads

# Writes

You can look at the columns from the previous report to see what these mean. You get the following groupings of these reports, which are pretty self-explanatory from the headings:

Top Oldest Sessions

Top CPU Consuming Sessions

Top Memory Consuming Sessions

Top Sessions By # Reads

Top Sessions By # Writes