I'm continuing my series on the Standard Reports in SQL Server Management Studio, and today I'm covering another "Activity" report.

The Activity – Dormant Sessions report shows you the sessions you have on SQL Server that are connected, and perhaps at one time even ran some query, but aren’t doing anything right now. There’s a summary area at the top and then two bands – one showing the Top 10 Dormant Sessions, and the other grouping the users of those sessions.

You might wonder why this report was included. You’re probably thinking, “Why in the world would someone connect to SQL Server but not do anything with the connection?” The answer might surprise you.

Straight client/server applications, those that just make a connection to SQL Server and do some work, are actually pretty rare these days. Most of the time developers include a “middle tier” of code that connects to one or more databases on behalf of the users. Sometimes the users don’t get to the database through this layer, but are abstracted out even further.

If the developer uses Microsoft components, and follows the best practices for coding those platforms, then sessions are terminated normally. But it is also rare that everything always goes as planned, and sometimes even Microsoft software has bugs J. Since sessions take valuable resources, such as memory, locks and CPU and I/O time, you need to make sure that every session is actually doing something.

That isn’t to say that if there is an entry on this report that something is wrong. There will always be at least a couple of entries here, more if there are lots of sessions. The key is to investigate anything you find here that seems out of the ordinary – a session that is several hours old, for instance.

In the first section of the report you get the following information:

# All Sessions All the sessions connected to this Instance of SQL Server.
# Dormant Sessions (1+ hours) The number of sessions that haven’t done any work in more than an hour.
# Users with Dormant Sessions (1+ hours) The number of users with sessions that haven’t done any work in more than an hour. This might be different than the number of sessions, because one again a single login might have more than one session open.

The next band shows the Top Ten Dormant Sessions. It has the following columns:

Session ID A number that uniquely identifies the session.
Lat Activity Time A timestamp of the last time this session did any work.
CPU Usage (ms) The cumulative amount of CPU time, in milliseconds, that the session has used.
Memory The cumulative amount of memory, in kilobytes, that the session has used.
Session Login Time A timestamp showing when the session was established.
Host Name The computer’s name where the session is connecting from. Note that this name can be set programmatically, and if one or more middle tiers are involved this might not reflect where the user is actually sitting.
Program Name The name of the program that is using the session. This can also be set by the program, and if it isn’t, it might not be as meaningful.
Host Process ID This is a unique number that identifies the process for this session.
Login Name The login associated with this session. Note that if the code is using a login role or impersonation, this might not be the actual user’s login name.
# Connections The number of connections this session is taking. Normally this will be one per SQL Statement.

The next report band groups the dormant sessions by user – which can be helpful if the developers are setting those names in the code, or at least passing them along. Here are the columns in this band:

User Name The name of the login for the sessions held by this user. Note that this can be set programmatically and also might be impersonated or using a application role.
# Dormant Sessions The number of sessions this login has that are not doing any work.
# Dormant Sessions (1+ hours) The number of sessions this login has that haven’t done any work in more than an hour.
# All Sessions The cumulative number of sessions that this user has taken.
Last Activity Time of Most Dormant Session Shows the time that the newest session did work.
Time Since Last Activity of Most Dormant Session (hrs) Shows the time (in hours) that the oldest session did work.