Monitoring Connections with SQL Server Management Studio (SSMS)

The sp_who (or sys.sp_who) system stored procedure covered in the previous lesson still exists in SQL Server 2025 and is still fully documented for the current release (17.x). It continues to report on current users, sessions, and processes, filterable by login, session ID, or 'ACTIVE'. The related, undocumented sp_who2 also still ships and still returns a few extra columns, CPU time and disk I/O among them, but as noted previously, it carries no guarantee of staying consistent across versions.

For day-to-day work, though, SSMS 22 monitors connections in SQL Server 2025 primarily through Activity Monitor rather than through either stored procedure. Activity Monitor queries the same underlying session and request data, requires VIEW SERVER STATE permission, and presents it as a live, refreshing, filterable grid instead of a one-time result set you have to rerun by hand.

1. Connect Object Explorer to the Database Engine instance you want to monitor.
2. Open Activity Monitor: right-click the server and choose Activity Monitor, click its toolbar icon, or press Ctrl+Alt+A.
3. Inspect the Processes pane, which expands by default when Activity Monitor opens.
4. Focus by filtering, sorting, and customizing columns to isolate the sessions you actually care about, for example everything in a RUNNING state, or everything with a non-zero blocking value.

Activity Monitor also opens a handful of other expandable panes beyond Processes, including an Overview pane with high-level graphs for processor time, waiting tasks, database I/O, and batch requests per second, useful for a quick sense of instance load before you drill into individual sessions. A sudden climb in Batch Requests/sec with no corresponding rise in the Processes pane's session count is itself a useful connection-level clue: it usually means the same, relatively small set of connections is simply doing more work, rather than more clients having connected.

Activity Monitor's VIEW SERVER STATE permission requirement is worth understanding rather than just accepting. It's a server-level permission, separate from any database-level role, and it's what lets a login see session, request, and wait information for every connection on the instance, not just its own. Members of the sysadmin fixed server role have it implicitly. For anyone else, it has to be granted explicitly:

GRANT VIEW SERVER STATE TO [login_name];

Without it, Activity Monitor either refuses to open at all or, depending on the SSMS version, opens but shows an empty or heavily restricted Processes pane. This is a common point of confusion for a newly onboarded DBA or developer who can connect and query their own database fine, but can't see what anyone else on the instance is doing until this permission is granted.

Seeing a session and being able to act on it are two separate permissions, worth not conflating. VIEW SERVER STATE only grants visibility. Actually killing someone else's session, the right-click option covered above, additionally requires either membership in the sysadmin or processadmin fixed server roles, or the ALTER ANY CONNECTION server permission; a login can always terminate its own session without any special permission at all, since nothing stops you from ending your own work.

Processes isn't the only pane in Activity Monitor; it's simply the one most directly tied to connections. Resource Waits groups waiting tasks by category rather than by session, useful for spotting a waits pattern across the whole instance rather than one session at a time. Data File I/O breaks out read and write latency per database file, and Recent Expensive Queries surfaces the queries currently consuming the most CPU, logical reads, or memory, regardless of which session is running them. These panes were introduced alongside Processes in the first lesson of this module; worth remembering here because a connection that looks unremarkable in the Processes pane can still be the one driving a spike visible in Resource Waits or Recent Expensive Queries.

The first lesson in this module also covered the Standard Reports available by right-clicking a server or database node and choosing Reports, Standard Reports. One of those, Activity, All Blocking Transactions, is worth calling out specifically here: it shows blocking chains in a tree view, with each blocked session nested under the session that's blocking it, which is easier to read at a glance than scanning the Processes pane's blk column row by row when several sessions are involved rather than just two. It's a snapshot like everything else covered in this lesson, not a continuously updating view, but it's a fast way to confirm whether a single blocking session is responsible for a chain of several waiting sessions, versus several independent, unrelated blocking situations happening at once.

The Processes pane lists one row per session, with the columns you'll use most often:

1. Spid the Session Process ID, the same identifier sp_who returns.
2. User/Login the login connected under that session.
3. Database the database the session is currently using.
4. Status the session's current state (sleeping, suspended, running, and so on, as covered in the previous lesson).
5. Open Transaction the number of transactions, including nested ones, the session currently has open.
6. Command a high-level description of the session's last command.
7. Application the name of the connecting application, populated only when the application itself sets this value; many default client tools leave it blank.

Right-clicking any row in the Processes pane opens a shortcut menu with a handful of practical actions: viewing the full text of the session's last command, killing the session outright (covered in the previous lesson, and with the same caution that applies there), and, as a legacy holdover from older SSMS versions, tracing the process in SQL Server Profiler. Profiler itself has been deprecated in favor of Extended Events since the previous module's coverage of monitoring tools; if you need to trace a specific session's activity in detail, setting up a targeted Extended Events session is the current, supported path, even though the older menu option is still visible in the interface.

The default Processes pane column set rarely matches exactly what you need for a given investigation. Right-clicking the column header row lets you add columns that aren't shown by default, such as Memory Use, Login Time, or Wait Time, and remove ones that are cluttering the view for your current task. Clicking any column header sorts by that column, which is a fast way to find, for example, the longest-running session or the one holding a wait the longest, without writing a query. These customizations are per-session in SSMS; they don't persist automatically if you close and reopen Activity Monitor, so a layout you find useful for a recurring task is worth noting down rather than re-discovering each time.

The connection pooling behavior covered in the previous lesson shows up directly in the Processes pane. A pooled application typically presents as a cluster of rows under the same login and application name, most of them sitting in a sleeping or dormant status with zero open transactions, and only one or two actually running or suspended at any given moment. Seeing that pattern is normal and expected; what's worth investigating is a pool that keeps growing over time without those sleeping connections ever cycling back to real work, which usually points to the same connection leak discussed previously, now visible directly in the Processes pane rather than just as a rising count from a DMV query.

Activity Monitor is at its most useful when a session is stuck. A typical investigation follows three steps, using the same illustrative scenario from the previous lesson: session 52 (status Suspended, running an UPDATE, wait type LCK_M_X, blocked by session 51) and session 51 (status Sleeping, with an open transaction).

1. Inspect the wait check the wait type, how long it's been waiting, and what resource it's waiting on. Not every wait is a lock; LCK_M_X specifically is.
2. Follow the blocker find the session named in the suspended session's Blocked By column, and review that session's own SQL and transaction context. A sleeping session, like 51 here, can still be holding the lock, since sleeping does not mean harmless; an open transaction retains its locks until it's committed or rolled back.
3. Verify the outcome after taking an approved corrective action, confirm the blocking has actually cleared and that work has resumed, rather than assuming it did.

For a deeper look than the Processes pane itself provides, the same investigation can be run directly in a query window against sys.dm_exec_sessions, sys.dm_exec_requests, sys.dm_os_waiting_tasks, and sys.dm_tran_locks, the last of which shows the specific lock resources involved rather than just the fact that a lock wait is occurring.

It's also worth being clear about what Activity Monitor doesn't give you: history. Close it, and whatever blocking pattern you were watching is gone; there's no built-in record of what happened five minutes ago. If a blocking issue recurs often enough that you need to catch it after the fact rather than while it's happening, that's exactly the gap Query Store and a standing Extended Events session, both covered in the first lesson of this module, are built to fill. Activity Monitor is the right tool for watching a problem as it happens; it was never meant to be the tool for reconstructing one after the fact.

As with the previous lesson's coverage of KILL: do not terminate a session solely because it is blocking. Confirm what its transaction is doing and what rolling it back will cost before acting.

Not every connection problem is a single stuck session; sometimes the issue is the opposite, far too many connections, none of them individually blocked, but collectively exhausting the instance. Suppose the Processes pane suddenly shows 400 sessions under the same login and application name where it normally shows 50, most of them sleeping. Sorting by Login Time quickly shows whether this is a gradual climb, consistent with a connection leak, or a sharp jump at a single point in time, consistent with a deployment, a configuration change, or a client application that was restarted without properly closing its previous pool. Cross-checking against the connection limit covered in the previous lesson tells you how much headroom remains before new connection attempts start failing outright. Unlike the blocking scenario above, there's rarely a single session to act on here; the fix is almost always on the application side, correcting pool configuration or a code path that isn't returning connections, rather than anything you can resolve from within Activity Monitor itself.

SSMS also supports monitoring through T-SQL run directly in the query editor against sys.dm_exec_sessions and sys.dm_exec_connections, which complement rather than replace the graphical Activity Monitor. A query combining them reproduces much of what the Processes pane shows, with a couple of details Activity Monitor doesn't surface directly, such as each connection's client IP address and how long it's been open:

SELECT s.session_id, s.login_name, s.host_name, s.program_name,
       s.status, c.client_net_address, c.connect_time
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_connections AS c
  ON s.session_id = c.session_id
WHERE s.is_user_process = 1
ORDER BY c.connect_time;

Server Properties shows the instance's configured maximum user connections (the same user connections setting covered via sp_configure previously), and is worth checking any time you see a connection count that's climbing toward a round, suspiciously specific number.

Most DBAs are responsible for more than one SQL Server instance, and switching Object Explorer between them one connection at a time doesn't scale well. Registered Servers, available from the View menu or Ctrl+Alt+G, lets you organize instances into named groups and connect to any of them without re-entering connection details each time. More usefully for monitoring, a multi-server query run against a Registered Servers group executes the same T-SQL, such as the connection query above, against every instance in the group in one pass, with the results combined into a single grid tagged by server name. For a fleet of instances, this turns what would otherwise be opening Activity Monitor separately against each server into a single query that surfaces, for example, every instance currently showing an unusually high connection count.

Between this lesson and the previous one, you now have both ends of connection monitoring covered: a quick, scriptable check with sp_who for the moments you just need an answer fast, and a fuller, visual, filterable view through Activity Monitor for the moments you need to watch a situation unfold or dig into exactly why a session is stuck. A reasonable habit is to open Activity Monitor whenever something feels off rather than waiting for a user complaint, sort the Processes pane by status or wait time to see what stands out, and only reach for the DMV queries or Registered Servers multi-server view once the Processes pane has pointed you toward a specific session, database, or instance worth investigating further. Treat Activity Monitor as the first stop, not the only stop, and the deeper tools covered in this lesson as what you reach for once it's told you where to look.

Across these two lessons, connection monitoring on SQL Server 2025 comes down to a small toolkit used in combination rather than any single command or window: sp_who for a fast, scriptable answer; Activity Monitor for a live, visual view with filtering and the All Blocking Transactions report for chains of blocked sessions; the DMVs, sys.dm_exec_sessions, sys.dm_exec_connections, sys.dm_exec_requests, sys.dm_os_waiting_tasks, and sys.dm_tran_locks, for the detail none of the higher-level tools expose on their own; and Registered Servers for applying all of it across more than one instance at once. None of these tools replaces the others; each answers a slightly different question, and knowing which one fits the question in front of you is most of what separates a quick five-minute check from an hour spent guessing.

Click the Quiz link below to test your knowledge of the concepts presented in this module so far.
sp_who quiz