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


 

Comments (2)

  1. Carpe Datum says:

    I’ve completed documenting all of the Standard Reports in SQL Server Management Studio. You can get to

Skip to main content