DB-유

Using sp_WhoIsActive for Live SQL Server Troubleshooting

· mssql, troubleshooting, monitoring

When an application is "slow right now", the built-in sp_who2 and Activity Monitor tell you very little: a list of sessions, a status, maybe a blocking SPID. Adam Machanic's free sp_WhoIsActive stored procedure has been the standard answer to "what is the server doing at this moment" for years. One call gives you every running request with its elapsed time, the exact statement, wait information, the blocking chain, reads and writes, tempdb usage, and optionally the execution plan and lock details.

It's the first thing I install on any instance I take over. Below are the parameters I actually use, and how to log the output to a table so you can answer "what was running at 03:12 last night".

It works on SQL Server 2005 SP2 and later, including 2016 and 2017 on Linux. Whoever runs it needs VIEW SERVER STATE. Grab the script from the sp_WhoIsActive GitHub repository (the project moved there from the original blog in 2016; version 11.x is current).

Install it

Open sp_WhoIsActive.sql in SSMS and run it against master. The script creates dbo.sp_WhoIsActive. Because it is in master and starts with sp_, you can call it from any database. Confirm the version:

EXEC sp_WhoIsActive @help = 1;

The @help output is the full parameter documentation and is worth reading once.

Plain call first

EXEC sp_WhoIsActive;

By default you get one row per active request, excluding your own session and system sessions. The key columns:

  • dd hh:mm:ss.mss: how long the request has been running.
  • sql_text: the current statement, clickable XML that opens in a new window.
  • wait_info: for example (2504ms)LCK_M_S or (15ms)PAGEIOLATCH_SH, meaning what the request is waiting on and for how long.
  • blocking_session_id: who is blocking this row.
  • CPU, reads, writes, tempdb_allocations: resource use so far.
  • status: running, suspended, sleeping, runnable.

A row with sleeping status and an open transaction is the one that usually explains everything.

Blocking chains

EXEC sp_WhoIsActive @find_block_leaders = 1, @sort_order = '[blocked_session_count] DESC';

@find_block_leaders adds a blocked_session_count column; the sort puts the root blocker at the top. Combine with @get_locks = 1 to see exactly which objects and pages each session holds:

EXEC sp_WhoIsActive @find_block_leaders = 1, @get_locks = 1, @get_outer_command = 1;

@get_outer_command shows the batch or procedure call, not just the current statement, which is what you need to identify the application code.

Plans and task details

EXEC sp_WhoIsActive @get_plans = 1, @get_task_info = 2, @get_additional_info = 1;

@get_plans = 1 adds the estimated plan for the current statement as XML; @get_task_info = 2 shows every wait across all parallel threads instead of the single worst one, and @get_additional_info adds session settings, transaction isolation level and, for Agent jobs, the job and step name. The plans make it heavier, so I keep this variant for manual use and never put it on a tight polling loop.

Logging to a table

The procedure can generate the table definition for you:

DECLARE @schema NVARCHAR(MAX);
EXEC sp_WhoIsActive @get_transaction_info = 1, @get_outer_command = 1, @find_block_leaders = 1,
     @return_schema = 1, @schema = @schema OUTPUT;
SET @schema = REPLACE(@schema, '<table_name>', 'DBA.dbo.WhoIsActive_Log');
EXEC (@schema);

Then create a SQL Server Agent job that runs every minute:

EXEC sp_WhoIsActive @get_transaction_info = 1, @get_outer_command = 1, @find_block_leaders = 1,
     @destination_table = 'DBA.dbo.WhoIsActive_Log';

DELETE FROM DBA.dbo.WhoIsActive_Log WHERE collection_time < DATEADD(DAY, -7, GETDATE());

The parameter list used for @return_schema must match the one used for @destination_table exactly, or the insert fails on a column mismatch. Add a clustered index on collection_time.

Try it on a fake blocker

Open two SSMS windows. In the first:

BEGIN TRAN;
UPDATE dbo.Orders SET Status = 'X' WHERE OrderId = 1;
-- leave the transaction open

In the second, SELECT * FROM dbo.Orders WHERE OrderId = 1;. In a third, run EXEC sp_WhoIsActive @find_block_leaders = 1;. You should see the sleeping session with blocked_session_count = 1 and the select with wait_info showing LCK_M_S and blocking_session_id pointing to the first. Roll back the first window.

Notes

"Msg 297: The user does not have permission to perform this action." The caller lacks VIEW SERVER STATE. Grant it with GRANT VIEW SERVER STATE TO [login]; or wrap the call in a certificate-signed procedure.

The sql_text column shows <?query -- ... --?> with only a fragment. That is the current statement, not the whole batch. Add @get_outer_command = 1.

Logging job fails with "Column name or number of supplied values does not match table definition." The table was created with a different parameter set than the collection call. Drop the table and regenerate with @return_schema using the exact same parameters.

Output is empty even though the server is busy. By default sleeping sessions without an open transaction and system sessions are hidden. Use @show_sleeping_spids = 2 to see all sleeping sessions, or @show_system_spids = 1 for background tasks.

The one-minute logging job is what pays for itself. Tonight's mystery slowdown will have evidence tomorrow morning.