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:
The next band shows the Top Ten Dormant Sessions. It has the following columns:
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: