Connections
Overview
Connections to the current database: who is on it, from where, and whether they are doing anything.
The report groups by one identity at a time, picked on the toolbar: login, program or host. “Who is connected” means grouping by login, and a list of sessions is not that.
What this replaces
The old page was an eight column grid over a pie of up to sixteen slices, grouped by whichever dimension a combo box in the header said. Four things were wrong with it:
| Fault | What it meant |
|---|---|
| The pie never answered the question | Nobody can tell four sessions from six by slice angle, the counts were hidden in the legend text, and the colours were dealt by row order so every refresh repainted everything. |
| The grid made the reader aggregate | It listed the sessions and left the counting to you. |
| Nothing said whether a session mattered | One running a query, one holding an open transaction, and one asleep for three days all looked identical. |
The data source was sysprocesses |
Deprecated since SQL Server 2005, filtered on spid > 50, which is not what separates user sessions from system ones, and carrying a Library column that has said TCP/IP for twenty years. |
Where to find it
| Route | How |
|---|---|
| Server tree | Select a database → Connections |
The page title reads Connections to <database name>.
This is the database scope report. The instance scope report of the same name is documented separately as Connections for an Instance, and answers a different question: every physical connection to the server, with the transport and security columns.
Requirements
VIEW SERVER STATEon the instance, forsys.dm_exec_sessions.- SQL Server 2012 or newer for the modern path. Older versions fall back to
sysprocesses, which is the only thing they have.
Reading the bars
One bar per identity, ranked by session count, split into running and sleeping.
That split is the finding the old page could not show. Twenty connections from an application server is normal; twenty connections from an application server where none are running and the oldest has been idle for three days is a leak.
Click a bar to filter the grid to that identity’s sessions. Click again to clear.
The chips
Amber chips mark the three things worth noticing:
| Chip | When |
|---|---|
| Open transaction | A session in the group is holding one. This is the one that turns into blocking. |
| sa | The group is connecting as sa. |
| Idle for days | The whole group has been asleep long enough to be a leak rather than a lull. |
Groups with nothing remarkable get no chip. The chips are the difference between a list of who is connected and a list of what to look at.
Reading the grid
| Column | What it carries |
|---|---|
| Session | The session id. |
| Login | The login. |
| Host | The client machine. |
| Program | The application name, normalised, so an Agent job step reads as the job rather than a hex id. |
| Status | Running or sleeping. |
| Open Tran | Open transaction count. |
| Last Activity | When the session last did anything. |
| Login Time | When it connected. |
Actions
Kill Session is on the right-click menu. It names the session and asks first. Everything else reads.
The toolbar
| Control | What it does |
|---|---|
| By Login / By Program / By Host | Which identity the bars group by. |
| Refresh | Re-read now. |
The page refreshes itself on a timer, and says so under the header. The interval is a setting.
How to read the report
- Start By Login. That is what “who is connected” usually means.
- Look at the split, not the length. A tall bar that is entirely sleeping is a different thing from a tall bar that is entirely running.
- Chase the chips. An open transaction is the one that becomes somebody else’s blocking problem.
- Switch to By Program when a login is shared. One service account covering four applications is one bar By Login and four By Program.
- Switch to By Host when you need to know which machine to go and look at.
- Click the bar to see the individual sessions before killing anything.
Common patterns
One tall bar, all sleeping, one open transaction. The classic. An application opened a transaction and went away, and everything blocked behind it is waiting on a session that is doing nothing.
Many hosts, one session each. Direct client connections rather than an application tier. Usually a reporting or admin population.
One host, many sessions, mostly sleeping. A connection pool. Normal, and its size is a setting in the application’s connection string.
An sa chip on anything. Worth a conversation regardless of what the session is doing.
A group idle for days. Connections nobody closed. They cost worker threads and they hold locks if they left a transaction open.
Where the data comes from
sys.dm_exec_sessions with is_user_process = 1 on SQL Server 2012 and newer, filtered to this database. It falls back to sysprocesses only on versions that have nothing better.
Nothing is stored. This is a live picture.
Settings
| Setting | What it holds |
|---|---|
ConnectionsGroupBy |
Whether the bars group by login, program or host |
ConnectionsRefreshInterval |
How often the page re-reads |
Per-user rather than per-database.
Messages you may see
Nobody connected:
No one is connected to this database right now. The page checks again every …
Timed out:
The connections 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 |
|---|---|
| Connections for an Instance | Every physical connection to the server, with transport and security. |
| Sessions | The same population at instance scope, in more detail. |
| Active Queries | What the running sessions on this database are actually doing. |
| Blocking Tree | Where an open transaction turns into everybody else’s problem. |
Frequently asked questions
How is this different from the instance Connections report? Scope and grain. This is one database, grouped by identity, and answers who is on it. That one is the whole instance, one row per physical connection, and answers how they got here.
How is this different from Sessions? Sessions is instance-wide and carries more per session. This groups by identity and is scoped to one database, which is the shape of the question “who is connected to this”.
Why does a group have a chip but the sessions look fine? The chip is about the group. One session in it holds an open transaction, or the whole group has been idle long enough to be a leak.
Why is a program name different from what the application sent? Because raw program names are normalised. An Agent job step arrives as a hex job id that nobody can identify.
Is sa being flagged a problem? Not automatically. It is flagged because it is worth knowing, and because on most estates it is not what applications are supposed to connect as.