Connections for an Instance

Overview

This is the instance level answer to who is dialling in to this SQL Server, from where, and over what.

The instance Connections report
One row per physical connection, with a band above carrying three facts about the instance rather than about any one client.

The connection is the grain here, not the session. The query drives off sys.dm_exec_connections and reaches sessions with a LEFT JOIN. An inner join, which is what the old query used, silently drops any connection whose session ends between the two DMV reads, and on a page whose whole subject is connection churn that is the wrong row to lose.

Sessions is the logical layer and answers what people are doing. This is the physical layer and answers how they got here. The two overlap in their identity columns and nowhere else.

What this replaces

The old page was not a report class at all. It was a row in ReportsXmlConfig.xml with type="Grid": five columns of identity strings over one query, with no chart, no refresh, and nothing on the page that could be clicked. Six things were wrong with it:

Fault What it meant
COUNT and GROUP BY threw away the session id Nothing on the page could be acted on. No spid on a row means no query to show, no plan to open, no session to kill and no Connection Advisor to launch. Dropping the aggregate and rolling up in code is the single change everything else rests on.
Ordered by client address then program name Alphabetically, so the biggest client on the instance sat at whatever row its IP happened to fall on.
Every column was an identity string encrypt_option, auth_scheme, net_transport, protocol_version, connect_time, num_reads, num_writes and parent_connection_id were all in the row already, and all eight were discarded.
No is_user_process filter Most rows on a quiet instance were SQL Server talking to itself, with no way to tell them from a real client.
Program names printed raw An Agent job step read as a truncated hex job id nobody can identify, even though the code to normalise it already existed.
A live DMV page with no refresh On a product where Sessions, Active Queries and Blocking Tree all refresh themselves and say so.

Where to find it

Route How
Server tree Right-click the server → Instance Reports → Connections

The page title reads Connections for <server name>.

This is the instance scope report. The database scope report of the same name is documented separately as Connections, and it answers a different question: who is connected to one database.

Requirements

  • SQL Server 2008 or newer, for client_net_address and auth_scheme on sys.dm_exec_connections as 2008 and newer report them.
  • VIEW SERVER STATE on the instance.

The band above the chart

Three facts about the instance rather than about any one client:

  • How the count has moved since the last refresh. Connection churn is the thing this page is uniquely placed to see.
  • How much headroom is left before new logins are refused.
  • What the last 24 hours looked like.

That last one is the difference between “we have 400 connections” and “we have 400 connections and we had 90 this time yesterday”.

The four views

The Flow view: where connections come from and how they arrive
Clients on one side, the instance on the other, with the paths between them.
View What it answers
Clients Who has the most connections. Ranked by number, which the old page never did.
Flow Where connections come from and how they arrive.
Security How they authenticated and whether the connection is encrypted.
Pool The shape of connection pooling: many connections from one host and program is a pool, and its size is a setting somebody chose.

Security is the view worth opening on an unfamiliar instance. Encryption and auth scheme were both in the row the old query already read, and both were thrown away.

Reading the grid

Column What it is
Session The session id. Present at all, which the old page’s aggregate made impossible.
Client IP client_net_address.
Host The client machine name.
Program The application name, normalised, so an Agent job step reads as the job rather than as a hex id.
Login The login.
Status Session status.
Encrypted Whether the connection is encrypted.
Auth The authentication scheme: NTLM, Kerberos, SQL.
Transport TCP, shared memory, named pipes.
TDS The protocol version.
Open Tran Open transaction count.
Connected When the connection was made.
Last Activity When it was last used.
Reads / Writes Packet counts for the connection.
Database The database context.
The grid, one row per physical connection
Encrypted, Auth and Transport are three of the eight columns the old report read and discarded.

Actions

Kill Session is on the right-click menu. It names the session it is about to kill and asks first. Everything else on the page reads.

The session id being present at all is what makes the plan, the query text, the Connection Advisor and this action possible.

How to read the report

  1. Read the band first. Churn and headroom are instance facts and neither is in any row.
  2. Open Clients. One host with a hundred connections is a pool; a hundred hosts with one each is a different architecture entirely.
  3. Check Security on anything unfamiliar. Unencrypted connections and SQL authentication are both visible here and nowhere else in the product.
  4. Use Pool when the count is climbing. A pool that keeps growing is usually an application not returning connections rather than more users arriving.
  5. Cross to Sessions once you know which connection to care about. This page says how they got here; that one says what they are doing.

Common patterns

One host, one program, many connections. A connection pool. Normal, and its maximum size is a setting in the application’s connection string.

Connection count climbing steadily and never falling. A leak. Connections are being opened and not returned to the pool.

Many connections from one IP with different logins. An application server doing impersonation, or a shared gateway.

Unencrypted connections from outside the data centre. Worth a conversation. This page is where you would find out.

Headroom nearly gone. New logins are about to be refused, which presents to users as the server being down.

Where the data comes from

sys.dm_exec_connections as the driving view, left joined to sys.dm_exec_sessions, filtered to user processes.

Nothing is stored. The 24 hour figure in the band comes from the history repository where it is configured.

Settings

Setting Default What it does
InstanceConnectionsView clients Which view the page opens on
InstanceConnectionsRefreshInterval seconds How often the page re-reads

Messages you may see

Version too old:

This report needs SQL Server 2008 or newer. It reads client_net_address and auth_scheme from sys.dm_exec_connections the way SQL Server 2008 and newer report them.

Nothing connected:

Nothing is connected to this instance right now.

Report Why you would go there
Connections The same question scoped to one database.
Sessions The logical layer: what those connections are doing.
What is Active What is running right now across the instance.
SQL CPU Schedulers Whether the connection count is turning into worker thread pressure.
Linked Servers Outbound trusts, as opposed to inbound connections.

Frequently asked questions

How is this different from the database level Connections report? Scope and grain. That one answers who is connected to one database. This one is the whole instance, one row per physical connection, with the transport and security columns that only exist at this level.

How is this different from Sessions? Sessions is the logical layer and answers what people are doing. This is the physical layer and answers how they got here. They share identity columns and nothing else.

Why does one row per connection matter? Because a session can outlive a connection and a connection can exist without an active session. Counting sessions and counting connections give different answers, and connection churn is only visible at this grain.

Why is a program name different here from what the application sent? Because raw program names are normalised. An Agent job step arrives as a hex job id, which nobody can identify.

What is the headroom figure? How far the instance is from refusing new logins. When it runs out, users experience it as the server being down.