Sessions

Overview

The Sessions report answers who is connected to this SQL Server – drawn as a unit chart. One square per session, grouped into labelled blocks by login, host, program or database, and coloured by what the session is actually doing.

One square per session matters because the absolute number is the finding. A treemap of session counts draws the same picture for two hundred sessions as for twenty. Here, two hundred sessions look like two hundred squares.

Colour carries state, because a session running a query, a session holding an open transaction, a session blocked behind another, and a session asleep since Friday are four completely different things that most connection reports render identically.

This report replaces four older ones – Sessions by Login, Sessions by DBName, Connections, and Connections by Database – which disagreed with each other and between them read master.dbo.sysprocesses, deprecated since SQL Server 2005, filtered on spid > 50 (which is not what separates user sessions from system ones), and counted physical connections rather than sessions, so MARS inflated the number.

Squares shrink rather than disappear

As the instance gets busier the squares shrink – 11px, then 7px, then 4px – and past six thousand sessions a block falls back to a proportional bar with the count written on it. The shrinking is itself the signal. Nothing is dropped until a square would be invisible.

---

Where to find it

Route	How
Server tree	Right-click the server → Instance Level Reports → Sessions
Server Overview page	Click the Sessions report link
Report arrows	Sits between CPU by Database and Last DBCC CheckDB Known Good

The page title reads Sessions for <server name>.

---

Requirements

• SQL Server 2008 or newer. The report reads open_transaction_count from sys.dm_exec_sessions and blocking_session_id from sys.dm_exec_requests, neither of which SQL Server 2005 has. On 2005 it says so and stops:

  This report needs SQL Server 2008 or newer. It reads open_transaction_count from sys.dm_exec_sessions and blocking_session_id from sys.dm_exec_requests, which SQL Server 2005 does not have.

• Grouping by database needs SQL Server 2012 or newer. On earlier versions the subtitle line says so and that grouping is unavailable.

• VIEW SERVER STATE on the instance.

• The query timeout is 60 seconds.

---

The states

State	Meaning
Sleeping	Connected, not running a request.
Running	Executing a request right now.
Open transaction	Not running anything, but holding a transaction open.
Blocked	Waiting behind another session.

The states are ordered worst last, and a session takes the worst that applies. A session that is both blocked and holding a transaction reads as blocked, because that is the one somebody has to do something about.

System sessions are drawn outlined rather than filled, and are hidden by default.

---

Reading the chart

The blocks

Each labelled block is one identity – a login, host, program or database, whichever the toolbar says to group by. The chart shows the busiest 15; the rest roll into one block labelled all smaller – every session is in the grid. The rollup keeps every square, because the count is the whole point of the shape; it just stops giving each tail identity a heading of its own.

The two shapes

• Sessions (the default) – squares packed into blocks by identity.
• Idle age – the same squares placed on a logarithmic axis by time since the last request, so the sessions that have been quiet longest sit furthest right. The axis tops out at one week; anything older piles up on the last tick.

Idle age is the view that finds connection leaks. A block of squares sitting at the right-hand edge is an application that opened connections and never closed them.

The header

The summary line counts what is in front of you:

214 sessions ┬╖ 12 logins from 31 hosts ┬╖ 4 running now ┬╖ 7 open transactions ┬╖ 2 blocked

The chip strip carries the transport and authentication facts – the instance-level questions the old Connections report read from the DMVs and then printed nowhere:

• 3 unencrypted (amber)
• 18 SQL logins, 196 Windows auth
• 4 over named pipes
• 61 system sessions hidden – so hiding them is visibly a choice rather than a silent omission.

The subtitle line:

grouped by login ┬╖ one square per session ┬╖ live from sys.dm_exec_sessions

It adds SQL Server started 12m ago when the instance has been up less than an hour, because a server that came up ten minutes ago has not had time to collect its usual connections and an unexplained empty page reads as a quiet afternoon.

The blocking band gets its own amber strip, because blocking is the one thing on this page somebody has to act on, and the head of a chain is usually a sleeping session that looks like every other sleeping session:

6 sessions are blocked right now – session 84 is at the head of the chain (app_svc, sleeping).

With more than one chain it names up to three heads.

Chart interactions

Gesture	Result
Hover a square	Session tooltip
Click a square	Selects that session in the grid
Double-click a square	Opens the Connection Advisor for that session
Right-click a square	The session menu, plus Copy Chart to Clipboard
Click a block name	Filters the grid to that identity
Right-click a block name	The block menu
Right-click empty space	Copy Chart to Clipboard only

The legend hint reads click a square to find the session – double-click for the Connection Advisor – click a name to filter the grid.

---

Reading the grid

Column	What it is
Session	The session id.
Login	Login name.
Host	Client machine name.
Program	Application name, tidied – SQL Agent job step, SQL Agent, .NET SqlClient application, or (no program name).
Database	Current database context.
Status	running, sleeping or blocked.
Open Tran	Open transaction count.
Blocked By	The session id blocking this one, if any.
Last Activity	When the last request ended.
Login Time	When the session connected.
Connection	Transport and authentication, or (no connection).

Program names are normalised rather than printed raw, because the raw values are GUIDs and version strings that make the column unreadable.

The numeric and time columns keep raw values behind the displayed text so the header sort is a real sort – a list view sorts as text, and would read 3d ago as junk.

---

The toolbar

Group	Buttons
Group by	By Login ┬╖ By Host ┬╖ By Program ┬╖ By Database
Shape	Sessions ┬╖ Idle age
	System sessions (toggle) ┬╖ Refresh

A countdown sits to the right. The page refreshes itself every 60 seconds by default, on its own private timer with a visible countdown rather than through the application’s shared navigation timer – which would push every firing into the go-back history and the popular-reports tracking.

Auto-refresh is suspended while a confirmation dialog is open, so a page cannot reload out from under a question you are being asked.

---

Right-click actions

On a session:

Item	What it does
Open Connection Advisor for session N	The per-session detail dialog.
Kill Session N	(action) Confirms, then runs KILL.
Copy Login Name to Clipboard	Just the login.
Copy Session Detail Script to Clipboard	A read-only script for investigating that session in SSMS.

On a block:

Item	What it does
Copy <Login/Host/Program/Database> Name to Clipboard	The identity the block is grouped on.
Copy KILL Script for these N Sessions to Clipboard	One KILL per session, commented with who each belongs to.

About Kill Session

This is the one genuinely destructive action on the page. It confirms first, naming the session, its login, host and program:

Are you sure you want to kill session #84?

app_svc on WEB03 .NET SqlClient application

This session has an open transaction. Killing it rolls that transaction back.

The open-transaction sentence appears only when there is one, and it is the part worth reading – a rollback can take considerably longer than the kill.

KILL runs on a connection of its own so that a permissions or state failure reaches you as a message box rather than disappearing into a log. If it fails you get Session 84 was not killed, with what SQL Server said.

The bulk KILL script is deliberately clipboard-only. It is generated with a header telling you to read it before running it, and one commented KILL per session – killing thirty sessions is not a thing that should happen on one click.

---

How to read the report

1. Read the summary line. Session count, login count, host count, and how many are actually doing anything.
2. Check for the blocking band. If it is there, it names the head of the chain. Start there, not with the blocked sessions.
3. Look at the colour mix. A wall of sleeping squares is normal. A wall of open-transaction squares is not.
4. Check the chip strip for unencrypted connections and SQL logins – the security questions nobody asks until an audit.
5. Switch to Idle age. A tight block at the right-hand edge is a connection leak.
6. Change the grouping. By Program finds the application responsible; By Host finds the machine; By Login finds the service account.
7. Double-click anything interesting for the Connection Advisor.

Common patterns

Hundreds of sleeping sessions from one program. Connection pooling working as designed, or a leak. Switch to Idle age – if they are all old, it is a leak.

One sleeping session at the head of a blocking chain. The classic. An application opened a transaction and went away without committing. The blocking band names it.

Many open transactions, nothing running. Sessions holding transactions open without doing work. Look at the application’s transaction handling before you look at SQL Server.

A wall of squares appearing between refreshes. Something is opening connections faster than it closes them. The absolute count is the finding, which is exactly why the chart draws one square each.

Unencrypted chip with a nonzero count. Those sessions are sending credentials and data in clear text over the network.

---

Where the data comes from

Live, every refresh, from sys.dm_exec_sessions joined to sys.dm_exec_requests and sys.dm_exec_connections. Nothing is stored.

Sessions are counted, not connections – the report the old Connections page gave counted physical connections, so MARS inflated the number and any session without a connection row dropped off the page entirely. Here a session with no connection row is still a session, and its Connection column reads (no connection).

---

Settings

Setting	Default	Values
InstanceSessionsGroupBy	Login	Login, Host, Program, Database
InstanceSessionsShape	Sessions	Sessions, Idle
InstanceSessionsShowSystem	off	on / off
InstanceSessionsRefreshInterval	60	seconds, minimum 10

---

Messages you may see

Nothing connected:

No user sessions are connected to this instance right now. The page checks again every 60 seconds – the System sessions button shows what SQL Server is running for itself.

Timed out:

The sessions lookup did not finish in time. Try the Refresh button, or check the status of the SQL Server.

---

Related reports

Report	Why you would go there
What is Active	What those sessions are actually executing.
Blocking by Hour by Day	Whether this blocking is a pattern or a one-off.
Waits	What the running sessions are waiting on.
Deadlocks by Database	Where blocking has already turned into deadlocks.

---

Frequently asked questions

Why are the squares so small? Because there are a lot of sessions. The shrinking is the signal – nothing is dropped until a square would be invisible, and past six thousand a block becomes a bar with the count on it.

Why is a session both blocked and holding a transaction shown as blocked? Because that is the one somebody has to do something about. States are ordered worst last.

Where did Sessions by Login and Sessions by DBName go? This report replaced them, along with Connections and Connections by Database. Use the By Login and By Database grouping buttons.

Why does my session count differ from other tools? This counts sessions, not connections. Tools that count physical connections inflate the number when MARS is in use.

Why are system sessions hidden? They are what SQL Server runs for itself and they crowd out the answer. The System sessions button shows them, drawn outlined rather than filled, and a chip in the header always tells you how many are hidden.

Can I kill several sessions at once? Only via Copy KILL Script for these N Sessions to Clipboard, which you then read and run yourself. Killing thirty sessions is not a one-click action.

---