Monitor SQL Server Connections

Every time a user or application logs on to SQL Server, it opens a connection^[1]. Connections exist independently of one another, even when the same application opens several of them at once, so a slow or blocked connection from one client has no direct bearing on another client's connection unless they happen to be waiting on the same lock or resource. SQL Server lets you monitor the activity of every connection to the instance, through SQL Server Management Studio (SSMS) itself, as covered in the previous lesson, and through T-SQL commands you can run from any client tool. This lesson focuses on the simplest of those commands.

The simplest way to monitor how users and processes are connected to SQL Server is the system stored procedure^[2], sp_who. This procedure retrieves information about current connections. Its syntax is:

EXEC sys.sp_who [ [ @login_name = ] 'login' | 'spid' | 'ACTIVE' ];

SQL keywords are always case-independent, but the case sensitivity of object names, including stored procedure names, depends on the server's installed collation. Typing system stored procedure names in lower case is the safest habit regardless of collation. The one parameter, @login_name, lets you limit the results of sp_who to a specific login, a specific session ID (spid), or to only the sessions that are currently active rather than idle.

A few common ways to call sp_who:

EXEC sp_who;            -- every session, active and sleeping
EXEC sp_who 'ACTIVE';   -- only sessions currently doing work
EXEC sp_who 51;         -- a single session by its spid
EXEC sp_who 'app_user';   -- every session connected under that login

Filtering to 'ACTIVE' is usually the fastest way to cut through a busy instance's output and see only what's actually executing right now, while filtering to a specific spid is the natural next step once a session such as the blocking one from the worked example below has already been identified.

Running sp_who with no parameter returns one row for every session on the instance, including background and sleeping sessions. The columns you'll work with most are described below, and the figure that follows shows how they line up against a real result set.

The full result set also includes two columns that are easy to overlook: ecid, the execution context ID used by parallel query workers, and request_id. Neither shows up in the commonly quoted seven-column summary, but both matter once you're troubleshooting a parallel query or a session running more than one batch at a time.

One more thing worth knowing before you start reading output: SQL Server reserves the lowest spid numbers, roughly 1 through 50, for its own internal system processes rather than user connections. Unfiltered sp_who output always includes those rows, which is why a busy production instance can show dozens of sessions even when only a handful of them represent actual application or user activity; the sys.dm_exec_sessions.is_user_process column used in the DMV query later in this lesson is the cleanest way to filter those system rows out entirely.

Consider three sessions returned by sp_who: session 51 (status sleeping, blk = 0), session 52 (status suspended, blk = 51), and session 53 (status running, blk = 0). Session 52 is blocked by session 51, even though session 51 itself shows as sleeping. This is one of the more common points of confusion for anyone new to reading sp_who output: a session can go idle, sleeping, while still holding locks from an earlier statement inside a transaction that was never committed or rolled back. Any other session that needs those same locks, session 52 in this example, has to wait regardless of what session 51's client application is doing in the meantime. Spotting this pattern, a sleeping session with other sessions blocked on it, is usually the first sign of a client that opened a transaction and never closed it.

Connection counts are also worth comparing against the instance's own configured ceiling. sp_configure reports the user connections setting, which defaults to 0, meaning SQL Server calculates a dynamic maximum based on available resources rather than enforcing a fixed cap:

EXEC sp_configure 'user connections';

A nonzero value here means someone has deliberately capped the instance below its dynamic default, which is worth knowing before you spend time chasing a connection-refused error that's actually a configuration choice rather than a resource problem. The related max worker threads setting, also visible through sp_configure, governs how many worker threads SQL Server has available to actually process the connections it accepts; a high connection count against a low worker-thread ceiling is a common, and often overlooked, cause of sessions sitting in a runnable state longer than expected.

It's worth being clear about what sp_who actually gives you: a one-time snapshot of session state at the moment the query ran, not a continuously updating view. If you want to watch a blocking situation develop, you have to rerun the procedure, typically every few seconds, or switch to Activity Monitor's auto-refreshing panes, covered in the previous lesson, which poll the same underlying information on a timer rather than requiring you to requery by hand.

The status column is one of the more commonly misread parts of sp_who output. The values you'll see most often are:

1. background: a SQL Server internal or background task, not a user connection.
2. dormant: a connection SQL Server has reset and is holding open for reuse by a connection pool.
3. running: the session currently has the CPU and is actively executing a command.
4. runnable: the session is ready to run but is waiting its turn for CPU time.
5. sleeping: the session is idle and not currently executing a command. As the worked example above shows, a sleeping session can still be holding locks from an earlier, uncommitted statement.
6. suspended: the session is waiting on something other than the CPU, most often a lock, a disk I/O, or another resource; exactly the case for session 52 in the worked example.

sp_who has been part of SQL Server for a very long time, and Microsoft keeps it available mainly for backward compatibility with existing scripts. For new monitoring work, the current recommendation is to query the Dynamic Management Views (DMVs) directly instead: sys.dm_exec_sessions, sys.dm_exec_connections, and sys.dm_exec_requests. These are the same views that power SSMS's own Activity Monitor, covered in the previous lesson, and they return far more detail than sp_who does, including login time, client program name, CPU time consumed, memory usage, and the actual wait type behind a suspended status rather than just the fact that the session is suspended.

A query joining two of these views reproduces the same session-and-blocking picture as sp_who, with more context:

SELECT s.session_id, s.status, s.login_name, s.host_name,
       r.blocking_session_id, r.wait_type, r.wait_time, s.database_id
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r
  ON s.session_id = r.session_id
WHERE s.is_user_process = 1;

sp_who remains perfectly fine for a quick, ad hoc look, especially from a command line or an older script, but any dashboard, alert, or automated script you build going forward is better served by the DMVs, since they expose the underlying cause of a wait rather than just its symptom.

sys.dm_exec_connections in particular adds detail sp_who never exposed at all: client_net_address (the connecting machine's IP address, more precise than a hostname that may not resolve), connect_time (when the connection was first established), and num_reads / num_writes (how much network I/O the connection has done since it opened). For tracking down exactly which client machine or application is opening an unusual number of connections, these columns are usually more useful than anything sp_who returns.

Once sp_who or the DMV query above tells you which spid is blocking, the next question is almost always: what is that session actually running? sys.dm_exec_sql_text answers it, given the session's current SQL handle from sys.dm_exec_requests:

SELECT r.session_id, t.text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id = 51;

If the blocking session is sleeping rather than actively running a request, as in the worked example, this query against sys.dm_exec_requests alone won't return anything, since there's no active request to inspect; in that case sys.dm_exec_connections' most_recent_sql_handle column, passed through the same sys.dm_exec_sql_text function, shows the last statement that session ran, which is usually the statement that opened the still-uncommitted transaction.

Most application frameworks, including ADO.NET and the common ODBC and JDBC drivers, use connection pooling by default: rather than opening and closing a physical connection to SQL Server for every request, the client library keeps a pool of already-open connections and hands them out and takes them back as the application needs them. This matters when you're reading sp_who output, because the number of rows you see is the number of physical connections, not the number of application users or even the number of logical requests in flight at that moment. A web application with a pool size of 50 might show 50 connections in sp_who even when only a handful are doing real work, with the rest sitting in a dormant or sleeping state waiting to be reused. A sudden, sustained jump in connection count usually points to a pool-sizing change, a connection leak in application code that never returns connections to the pool, or a new client that was never configured to use pooling at all.

You may come across sp_who2 in older scripts and forum posts. It returns a similar result set to sp_who with a few extra columns, such as CPU and disk I/O totals, but it has never been an officially documented Microsoft procedure. Because it's undocumented, its exact behavior and column set are not guaranteed to stay consistent across versions or service packs. It's fine for a one-off interactive check on a server where someone has already installed it, but it isn't something to build new, supported tooling around; the DMV query above covers the same ground and is fully documented. Where it's installed, it typically adds columns such as CPUTime, DiskIO, LastBatch, and ProgramName on top of what sp_who already shows, all of which are also available, with documented guarantees, from the DMVs covered above.

So far this lesson has covered connections that succeeded. A connection that's refused outright, because of a bad password, a login that lacks permission on the target database, or the instance rejecting new connections because it's at its configured limit, won't show up in sp_who or the DMVs at all, since no session was ever created. Those attempts land in the SQL Server error log instead, covered in the previous lesson, and are also captured as audit events if you've configured login auditing in the server's security properties (Object Explorer, right-click the server, Properties, Security). Checking both the active-connection picture from this lesson and the error log's failed-login history gives you the full picture: who successfully connected and what they're doing, alongside who tried to connect and couldn't.

Finding a blocking session is only half the job; sometimes you also need to end one. The KILL command terminates a session by its spid:

KILL 51;

KILL is a blunt instrument. It rolls back whatever transaction the session was in the middle of, which can take as long to undo as the original work took to perform, and it has no effect on the client application itself, which may simply reconnect and start over. Reach for it only after you've confirmed, using sp_who, the DMV query above, or Activity Monitor, that the session is genuinely stuck rather than just slow, and after you understand what rolling back its transaction will mean for the application.

Once a session is killed, its rollback can take a while, sometimes longer than the original work. SQL Server reports progress for that rollback if you reissue the same command with an added option:

KILL 51 WITH STATUSONLY;

This returns an estimated completion percentage and time remaining rather than killing anything a second time, which is worth knowing before you conclude that a kill "didn't work" and try something more drastic. For a recurring blocking problem, fixing the query or transaction logic that's causing the lock is a better long-term answer than routinely killing sessions.

A connection-monitoring habit doesn't need to be elaborate: a quick sp_who 'ACTIVE' or a look at the DMV query above when something feels off, a periodic check of connection counts against the instance's configured limits, and an occasional glance at the error log for failed logins catch the large majority of connection-related problems before they become a wider outage. The commands in this lesson are deliberately simple precisely so that running them becomes second nature rather than a chore you have to look up every time.

The next lesson covers another method for monitoring connections.

[1] Connection: A person or application that has logged onto SQL Server.

[2] System stored procedure: A procedure stored in the master database whose name begins with sp_, used to perform system functions.