Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
sp_WhoIsActive is a free, open-source T-SQL stored procedure for seeing what SQL Server sessions are doing now: which requests are running, what they are waiting on, whether they are blocking other work, and how they are using resources. Install the script for your SQL Server version, grant access carefully, and start with the default output before enabling heavier diagnostic options.
What sp_WhoIsActive does
sp_WhoIsActive is a stored procedure, not a service or standalone monitoring application. Once installed in a SQL Server database, you can execute it on demand to collect a snapshot of activity. It brings together information such as session identity, SQL text, waits, blocking, CPU and I/O, TempDB use, transactions, and—when requested—plans, locks, and memory-grant details. The project is maintained in Adam Machanic’s GitHub repository and is licensed under GPLv3.
It is especially useful during a live incident: a query is slow, users report blocking, or a server appears busy and you need to see what is happening at that moment. It does not automatically maintain a durable workload history, send alerts, or provide fleet-wide dashboards. You can capture its output yourself, but that requires you to design storage, retention, security, and any alerting.
Microsoft’s built-in sys.sp_who provides basic information about current users, sessions, and processes. The commonly used sp_who2 adds some columns but is undocumented and still less configurable. sp_WhoIsActive packages a broader set of diagnostic details into a practical result set. It is not a substitute for every DMV query, Query Store, Extended Events, or a monitoring platform; each serves a different purpose.
#1 Best Overall
Choose the right script for your SQL Server version
Do not assume one script works with every SQL Server release. The repository’s current root script is identified as v2200.20260409, dated April 9, 2026, and targets SQL Server 2022 and later. The repository provides separate compatibility scripts: use the 2019 folder for SQL Server 2012–2019 and the 2008 folder for SQL Server 2008 or earlier. Check the repository README and the selected script header before installation. The latest release structure uses sp_WhoIsActive.sql; older tutorials may refer to the removed legacy filename who_is_active.sql.
The project README also identifies Azure SQL Database as supported, but do not assume every option behaves identically to boxed SQL Server. DMV visibility, permissions, cross-database access, and available features can vary by Azure service and configuration. Verify the options you need in the particular environment.
Install sp_WhoIsActive
- Download the official script for your SQL Server version from the project repository.
- Open the script in SQL Server Management Studio (SSMS) and select the database where you want to install it.
masteris the conventional choice because a procedure named with thesp_prefix can then be called from other databases on the instance. A dedicated DBA database is another option if that better fits your governance model. - Execute the script. This creates or updates the stored procedure; it does not install a separate application or background service.
- Grant appropriate permissions, then test the installation:
EXEC master.dbo.sp_WhoIsActive;You should receive a result set describing sessions and activity. If you installed it in a database other than master, use that database’s name in the call. The official installation guide covers installation and access considerations.
Permissions and sensitive data
Most functionality requires VIEW SERVER STATE, because the procedure reads instance-level dynamic management views. A typical grant is:
GRANT VIEW SERVER STATE TO [login_or_user];Use the actual principal in place of the placeholder. Depending on the feature and environment, additional access to the database containing a locked or blocked object may be needed to resolve its name. Without that access, object details can be unavailable or an operation may report an error.
Grant access deliberately. SQL text can contain literal customer data, secrets accidentally included in statements, personally identifiable information, internal object names, or application details. Consider who can execute the procedure and who can read any captured output.
If broad VIEW SERVER STATE access is not acceptable, the project documents certificate-based module signing: create a certificate in master, create a certificate-based login, grant the required permission to that login, sign the procedure, and grant users EXECUTE on the procedure. A procedure alteration or upgrade removes its signature, so sign it again after updating. Module signing does not automatically grant every database-level permission needed for object resolution. See the official access documentation.
Start with a safe, useful snapshot
The simplest call is usually the right first step:
EXEC dbo.sp_WhoIsActive;To omit sleeping sessions, use:
EXEC dbo.sp_WhoIsActive
@show_sleeping_spids = 0;To include all sleeping sessions, use @show_sleeping_spids = 2. The default is 1, which includes sleeping sessions with an open transaction. Sleeping means a connection is not currently executing a request; it does not prove the session is harmless. An idle connection may still have an open transaction and retain locks or other resources.
For a session-centric investigation, you can also include system sessions or your own session:
EXEC dbo.sp_WhoIsActive @show_system_spids = 1;
EXEC dbo.sp_WhoIsActive @show_own_spid = 1;The procedure has many options. To see the parameters and output-column information for the installed version, run:
EXEC dbo.sp_WhoIsActive @help = 1;For the full parameter descriptions, consult the official options documentation. Defaults and available features can depend on the installed script version.
Free tools Windows power users keep installed
One-click scans. No signup required.
Read the output by diagnostic question
Rather than treating the result as a column dump, read it in groups and ask what each group tells you.
| Question | Useful columns | How to read them |
|---|---|---|
| Which connection or request is this? | session_id, request_id, login_name, host_name, program_name, database_name |
Use these to identify the session, client, application, login, database, and request. A session can outlive an individual request. |
| How long has it been running, and is it active? | start_time, dd hh:mm:ss.mss, status, percent_complete, collection_time |
Duration and status help distinguish long-running work from a short request. percent_complete is meaningful only for operations for which SQL Server reports progress. |
| Is it waiting or blocked? | wait_info, blocking_session_id, and, when enabled, blocked_session_count |
A wait is a description of what the request is waiting for, not by itself proof of a fault. Blocking is a lock-related wait; some blocking is normal. |
| What resources are involved? | CPU, reads, physical_reads, writes, physical_io, used_memory |
These help identify resource consumption, but interpret them alongside elapsed time, waits, request history, and workload context. |
| Is TempDB or a transaction involved? | tempdb_allocations, tempdb_current, open_tran_count |
TempDB values are in 8-KB pages. High allocations with low current use can indicate churn; high current use can indicate space still retained. An open transaction can explain persistent locks or log pressure. |
| What statement or additional detail is available? | sql_text, sql_command, outer_command, query_plan, locks, memory_info, additional_info |
Some columns are optional or conditionally populated. Enable the relevant collection option and include its column in the output list. |
Column availability depends on the installed version and options. The default-column reference explains the standard output, while @help = 1 reports details for your installation.
Find the query consuming resources
Start by comparing active requests’ CPU, reads, writes, duration, waits, and database or application identity. High cumulative reads or CPU do not automatically mean the request is the source of the current incident: the value may reflect work accumulated over the request’s lifetime. If you need to see how much a request consumes over a defined short interval, use the delta option:
EXEC dbo.sp_WhoIsActive
@delta_interval = 5;@delta_interval takes two samples separated by the specified number of seconds and can report changes in measures such as CPU, reads, writes, TempDB use, context switches, memory, and physical I/O. A five-second delta is a short observation, not workload history or a complete benchmark.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallTo view execution plans during an investigation:
EXEC dbo.sp_WhoIsActive @get_plans = 1;@get_plans = 1 retrieves a plan based on the request’s statement offset; @get_plans = 2 retrieves the full plan based on the request’s plan handle. To see the full stored procedure or batch text, use @get_full_inner_text = 1. To see the outer ad hoc command or stored-procedure call, use @get_outer_command = 1.
EXEC dbo.sp_WhoIsActive
@get_full_inner_text = 1,
@get_outer_command = 1;Plans and large SQL batches increase collection cost and output size. Enable them when they answer a specific question rather than leaving them on in a high-frequency polling job.
Diagnose blocking without guessing at the root cause
For a fuller look at waits and blocking chains, try:
EXEC dbo.sp_WhoIsActive
@get_task_info = 2,
@get_additional_info = 1,
@find_block_leaders = 1;blocking_session_id shows immediate blocker information. In a chain, however, the immediate blocker may itself be waiting on an earlier session. @find_block_leaders = 1 adds blocked_session_count to help identify leaders with downstream impact. Task-level detail at @get_task_info = 2 provides expanded task and wait information; @get_additional_info = 1 can add resource details useful during investigation. See the blocking documentation and block-leader documentation.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Use this sequence before taking action:
- Confirm that the blocking is materially affecting work. The goal is to address harmful or excessive blocking, not to eliminate every lock wait.
- Identify the head of the chain, the SQL involved, the database, and the transaction state. A sleeping session with an open transaction may be the cause even though it is not executing a request.
- Determine whether the blocker is an expected transaction, a long-running operation, or an application problem. If the application owns the transaction, coordinate with its owner where possible.
- Estimate the consequences of cancellation. Terminating a session can start a rollback, which may take time and add work; it can also produce application errors.
- Only terminate a session when the operational impact and rollback risk are understood and the responsible operator has authority to do so.
For lock-level details, enable:
EXEC dbo.sp_WhoIsActive @get_locks = 1;Lock output is aggregated in XML and can become large. Object-name resolution may require access to the database containing the object. Use lock collection selectively, especially on a busy server or when many sessions are involved. More detail is available in the locks documentation.
Separate long-running queries from long-running transactions
A request’s elapsed time is not the same thing as the lifetime of its transaction. A statement may finish while its transaction remains open; a connection may then appear sleeping while still holding locks. A transaction can also be rolling back after cancellation. Look at open_tran_count, session status, waits, and the transaction details together instead of equating “not running” with “no impact.”
To collect transaction information, use:
EXEC dbo.sp_WhoIsActive
@get_transaction_info = 1;This can expose transaction duration, log-write information, and implicit-transaction indicators. It is useful when investigating idle sessions that retain locks or prevent log truncation. It does not make every transaction problem self-explanatory: connect the output to application behavior and database context before intervening.
Check memory grants and TempDB use
For memory-grant information, run:
EXEC dbo.sp_WhoIsActive
@get_memory_info = 1;Depending on version and request, the output can include requested memory, granted memory, maximum memory used, and a memory_info structure. A large grant is not automatically a fault. Compare requested, granted, and actually used memory, and check whether a request is waiting for a grant; then interpret the result alongside the plan and workload. The current script notes that this option is unavailable on SQL Server 2005.
For TempDB, compare tempdb_allocations with tempdb_current. The former can indicate how much a session has allocated, while the latter helps show what it is currently retaining. A large allocation total with low current use can point to churn rather than a large retained footprint. Both are measured in 8-KB pages, so multiply by 8 to convert the page count to kilobytes.
Filter sessions and tailor the result
Filtering reduces noise when you know which database, application, login, host, or session matters. For example:
EXEC dbo.sp_WhoIsActive
@filter = 'SalesDB',
@filter_type = 'database';
EXEC dbo.sp_WhoIsActive
@filter = 'AppServer%',
@filter_type = 'host';
EXEC dbo.sp_WhoIsActive
@not_filter = 'SQLAgent%',
@not_filter_type = 'program';Available filter types include session, program, database, login, and host. Session filters use session IDs; the other filter types support % and _ wildcards. Check the installed procedure’s help for exact parameter syntax.
You can control output columns and sort order as well. To focus on TempDB columns:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
EXEC dbo.sp_WhoIsActive
@output_column_list = '[temp%]';To put TempDB columns first and retain the other output columns:
Best Value
EXEC dbo.sp_WhoIsActive
@output_column_list = '[temp%][%]';To sort by CPU:
EXEC dbo.sp_WhoIsActive
@sort_order = '[CPU] DESC';A common gotcha: enabling a feature does not guarantee that its column appears. The final output is the intersection of enabled features and the columns requested by @output_column_list. For example, @get_locks = 1 cannot display locks if the output-column list excludes it. See the options reference.
Capture output for later analysis
To keep a history, you can send results to a table. A direct INSERT ... EXEC against sp_WhoIsActive can fail because the procedure itself uses INSERT EXEC, and SQL Server does not allow the resulting nested pattern. The documented approach is to generate a matching schema with @return_schema and then use @destination_table.
DECLARE @schema varchar(max);
EXEC dbo.sp_WhoIsActive
@get_task_info = 2,
@return_schema = 1,
@schema = @schema OUTPUT;
SELECT @schema;Replace the <table_name> placeholder in the returned definition and execute it to create the destination table:
Recommended Free Tools
SET @schema = REPLACE(
@schema,
'<table_name>',
'dbo.WhoIsActiveCapture'
);
EXEC (@schema);Then capture a result:
EXEC dbo.sp_WhoIsActive
@get_task_info = 2,
@destination_table = 'dbo.WhoIsActiveCapture';The table must match the output shape for the options you use. If you add a feature, change the requested columns, or otherwise alter the output configuration, regenerate the schema as needed. The capture guide explains the pattern.
Capturing output does not automatically create a well-designed history system. Decide on polling frequency, retention and purging, indexes, access controls, and whether storing SQL text and plans is appropriate. More frequent or more detailed captures increase collection, storage, and review costs.
Common problems and what to check
| Symptom | Likely check |
|---|---|
| Permission error or missing instance-level details | Confirm that the caller has the needed permissions, usually VIEW SERVER STATE, and check any database access needed for object resolution. |
| Script fails on an older server | Confirm that you downloaded the script for that SQL Server compatibility range rather than using the latest root script by default. |
| An option is enabled but its column is missing | Check both the feature parameter and @output_column_list; both must allow the column to appear. |
| Object name is missing from blocking or lock detail | Check access to the affected database and whether the selected feature can resolve that object in the environment. |
Direct INSERT ... EXEC fails |
Use the documented @return_schema and @destination_table capture method instead. |
| Output is too large or collection feels costly | Start with defaults, narrow the target with filters, and enable plans, locks, expanded task information, or full text only when the question calls for them. |
How it fits alongside other SQL Server tools
- DMVs: Queries against views such as
sys.dm_exec_requests,sys.dm_exec_sessions, andsys.dm_os_waiting_tasksoffer full control and can be tailored for a monitoring system. You must compose the relevant data and interpretation yourself;sp_WhoIsActivepackages many common joins and details into one diagnostic procedure. - Activity Monitor: A graphical way to inspect activity, but a stored-procedure result can be easier to run repeatedly, filter, and incorporate into DBA workflows.
- Query Store: Better suited to persisted query-performance trends, plan history, and regression analysis than to answering “what is blocking us right now?”
- Extended Events: Better for event capture over time, such as deadlocks, errors, or selected long-running activity. It takes setup and event interpretation rather than providing a one-line live snapshot.
- Monitoring products: A commercial or broader monitoring platform can provide persistent history, dashboards, alerting, estate-wide views, and operational workflows. That capability is different from a richer one-off session snapshot.
If you want a broader open-source monitoring system rather than a single procedure, Erik Darling’s Performance Monitor advertises multiple collectors, alerts, plan viewing, and SQL Server/Azure-related support. It brings a larger deployment and maintenance footprint than installing sp_WhoIsActive.
Choose sp_WhoIsActive when you need immediate, DBA-directed diagnosis, low deployment overhead, or a controlled capture on one or a few instances. Consider a monitoring product when the requirement is continuous alerting, multi-instance visibility, historical dashboards, capacity planning, anomaly detection, or centralized audit and access controls. sp_WhoIsActive itself is open-source software under GPLv3; commercial products have separate licensing and deployment terms.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsQuick reference
-- Basic live snapshot
EXEC dbo.sp_WhoIsActive;
-- Show procedure help
EXEC dbo.sp_WhoIsActive @help = 1;
-- Detailed task and blocking investigation
EXEC dbo.sp_WhoIsActive
@get_task_info = 2,
@get_additional_info = 1,
@find_block_leaders = 1;
-- Plans and transaction details
EXEC dbo.sp_WhoIsActive
@get_plans = 1,
@get_transaction_info = 1;
-- Locks or memory-grant details
EXEC dbo.sp_WhoIsActive @get_locks = 1;
EXEC dbo.sp_WhoIsActive @get_memory_info = 1;
-- Take two samples five seconds apart
EXEC dbo.sp_WhoIsActive @delta_interval = 5;Use the simplest call that answers the question. Add detail deliberately, verify the output columns you need, and treat every snapshot as evidence to interpret—not an automatic instruction to cancel a session.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
