How to Check Who Is Active on SQL Server (sp_who, sp_who2, and sp_WhoIsActive)

When SQL Server slows down, the first question every DBA asks is: who’s running what, and is something blocking? This guide walks you through three methods — from SQL Server’s built-in commands to the community’s go-to monitoring tool — so you can pinpoint active sessions fast.

What Does “Active” Mean in SQL Server?

An active session in SQL Server is any connection that is currently executing a query, waiting on a lock, or consuming server resources. SQL Server assigns each connection a Session ID (SPID), which you’ll see across all monitoring tools.

Being able to see active sessions lets you:

• Identify long-running queries consuming CPU or I/O
• Detect blocking chains where one session is locking others
• Confirm who is connected before maintenance windows
• Troubleshoot sudden performance drops in real time

Method 1: sp_who (Built-In, Basic)

sp_who is a system stored procedure included in every version of SQL Server. It returns a snapshot of current users, sessions, and processes.

EXEC sp_who;

-- Filter to a specific login:
EXEC sp_who 'domain\username';

-- Filter to active sessions only:
EXEC sp_who 'active';

What it returns:

Limitation: sp_who tells you that something is running, but not what query or how long it has been going.

Method 2: sp_who2 (Built-In, More Detail)

sp_who2 is an undocumented but widely-used extension of sp_who. It adds CPU time, disk I/O, last batch time, and program name — useful for a quick triage.

EXEC sp_who2;

-- Filter by SPID:
EXEC sp_who2 55;

Additional columns over sp_who:

• CPUTime — total CPU consumed by the session
• DiskIO — total disk reads/writes
• LastBatch — when the session last sent a command
• ProgramName — application (SSMS, application name, SQL Agent, etc.)

Limitation: Still no query text, no wait information, no execution plan, and output can’t be easily queried or filtered with WHERE clauses.

Method 3: sp_WhoIsActive (Community Tool — Recommended)

sp_WhoIsActive, written by Adam Machanic (Microsoft MVP), is the standard tool used by DBAs for real activity monitoring. It pulls data from multiple DMVs and presents it in a clean, queryable format.

Installing sp_WhoIsActive

1. Download the script from whoisactive.com/downloads or the GitHub repo
2. Open who_is_active.sql in SSMS
3. Switch to the master database (so it’s accessible from any database context)
4. Execute the script — it creates the sp_WhoIsActive stored procedure

USE master;
-- Run the downloaded script here

Basic Execution

EXEC sp_WhoIsActive;

This immediately shows you every active user process with: duration, login, SQL text, wait info, CPU, blocking status, and TempDB usage.

Useful Parameters

-- See execution plans for running queries
EXEC sp_WhoIsActive @get_plans = 1;

-- Find blocking sessions and who is the lead blocker
EXEC sp_WhoIsActive @find_block_leaders = 1;

-- Show lock details (helpful for deadlock investigation)
EXEC sp_WhoIsActive @get_locks = 1;

-- Sort by longest-running first
EXEC sp_WhoIsActive @sort_order = '[start_time] ASC';

-- Filter to a specific login
EXEC sp_WhoIsActive @filter = 'domain\username', @filter_type = 'login';

Key Output Columns

Method 4: Dynamic Management Views (DMVs)

If you prefer writing your own queries or need custom filtering, DMVs give you the most flexibility. sys.dm_exec_sessions is the starting point for active session data.

SELECT
    s.session_id,
    s.login_name,
    s.status,
    s.host_name,
    s.program_name,
    s.cpu_time,
    s.memory_usage,
    r.wait_type,
    r.blocking_session_id,
    t.text AS sql_text
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.is_user_process = 1;

This joins sessions with active requests and retrieves the actual SQL text — more powerful than sp_who2 but requires more setup than sp_WhoIsActive.

Comparison: Which Tool Should You Use?

Common Questions

What is a SPID in SQL Server? SPID stands for Server Process ID. SQL Server assigns a unique SPID to every connection. You’ll use the SPID when you want to kill a runaway session with KILL 55 (replacing 55 with the actual SPID).

How do I kill a blocking session in SQL Server? Once you’ve identified the blocking SPID using any of the tools above, you can terminate it with:

KILL 55; -- Replace with the actual SPID

Use with care — this rolls back any open transaction on that session.

Can I log sp_WhoIsActive results over time? Yes. You can use the @destination_table parameter to write output into a table, then schedule it as a SQL Agent job every 30–60 seconds. This is useful for post-incident analysis when a slow period has already passed.

Summary

For day-to-day SQL Server monitoring, sp_WhoIsActive is the tool to install and use. It turns a vague “the server is slow” report into an actionable list of sessions, queries, and wait types in seconds.