Narcis Miclaus

5 min read

SQL Server, from the ground up Lesson 39 / 40

sp_BlitzCache and sp_WhoIsActive: what's happening RIGHT NOW

Adam Machanic's sp_WhoIsActive is the emergency-response procedure. Plus sp_BlitzFirst for point-in-time wait stats.

The phone rings at 3am. “The database is slow. Everything is timing out.”

You open your laptop, connect, run one query. It tells you exactly what’s running, who’s blocking whom, which queries are eating CPU, which are waiting on disk, which are holding locks. You spend the next five minutes killing the right session and life goes back to normal.

That query is sp_WhoIsActive. It’s Adam Machanic’s 20-year-old open-source procedure, and it’s the single most useful emergency-response tool in a DBA’s arsenal. sp_BlitzFirst (part of the First Responder Kit) is the slightly friendlier version from Brent Ozar’s team.

Install both. Thank me later.

sp_WhoIsActive: the classic

Adam Machanic wrote this around 2007. It’s the gold standard “who’s running queries on my server right now” procedure. Download: whoisactive.com or search “sp_WhoIsActive github.”

Install: download the .sql file, run it against master (or your DBA database).

Basic call:

EXEC sp_WhoIsActive;

Returns one row per active user session, with columns:

  • session_id — the SPID.
  • start_time — when this request started.
  • sql_text — what it’s running (clickable XML, opens the full query).
  • login_name / host_name / program_name — who’s calling.
  • database_name.
  • wait_info — what it’s waiting on, for how long.
  • CPU, reads, writes — resource usage so far.
  • blocking_session_id — who’s blocking this session (if anyone).
  • status — running, suspended, etc.

Sort by CPU or reads desc; the top rows are your heaviest queries.

One-line diagnosis:

  • wait_info shows LCK_M_X(42) — waiting for session 42 to release an exclusive lock.
  • blocking_session_id = 42 — confirms it.
  • Run EXEC sp_WhoIsActive @session_id = 42 to see what 42 is doing.
  • Or kill 42: KILL 42.

Useful parameters

  • @get_plans = 1 — include the execution plan XML.
  • @get_locks = 1 — show all locks held by each session.
  • @find_block_leaders = 1 — sort sessions so the “block leader” (the one causing a blocking chain) is at the top.
  • @sort_order = '[CPU] DESC' — pick your sort.
  • @get_task_info = 2 — verbose task details.

My usual go-to in an emergency:

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

Everything you need on one screen.

Logging sp_WhoIsActive to a table

For ongoing monitoring:

-- Create the destination table
DECLARE @schema NVARCHAR(MAX);
EXEC sp_WhoIsActive @return_schema = 1, @schema = @schema OUTPUT;
SET @schema = REPLACE(@schema, '<table_name>', 'DBA.dbo.WhoIsActive_log');
EXEC (@schema);

-- Schedule this via Agent every 60 seconds
DECLARE @dest NVARCHAR(4000) = 'DBA.dbo.WhoIsActive_log';
EXEC sp_WhoIsActive @destination_table = @dest;

Now you have a rolling log of everything that ran. When someone asks “what happened at 2:15?”, you know.

sp_BlitzFirst: the “right now” sibling

Brent’s equivalent in the First Responder Kit. Different style from sp_WhoIsActive; both are useful.

USE DBA;
EXEC sp_BlitzFirst;

Runs for 5 seconds by default, sampling wait stats during that window. Gives you point-in-time instead of historical wait-stat distribution.

Output is sp_Blitz-style priority-ordered findings:

  • Current top wait types.
  • Current long-running queries.
  • CPU utilization at this moment.
  • Database with highest activity.

@SinceStartup = 1 — instead of a 5-second sample, show cumulative stats since server start.

@ExpertMode = 1 — more columns, deeper diagnosis.

My default triage sequence when the phone rings:

  1. sp_WhoIsActive @find_block_leaders = 1; — who’s blocking whom?
  2. sp_BlitzFirst @ExpertMode = 1; — what are we waiting on, collectively?
  3. Pick the biggest problem, act.

Ten seconds of typing. Ninety percent of the time, the root cause is obvious from those two queries.

Killing a session

When sp_WhoIsActive tells you session 42 is holding everyone up:

KILL 42;

Session rolls back its in-flight transaction and disconnects. Everyone it was blocking unblocks. Problem over.

Caveat: KILL rolls back the transaction. If it’s a big transaction that’s been running for an hour, the rollback can take another hour. Check sys.dm_exec_requests.percent_complete for rollback progress.

Alternative: KILL N WITH STATUSONLY; — report rollback progress without initiating another kill.

Don’t be afraid of KILL. It’s the right answer for runaway queries, stuck transactions, and sessions from a dead client that never closed its connection.

The three “zombie” session types you’ll kill regularly

  1. sleeping sessions with open transactions. Someone started a BEGIN TRAN in SSMS, went to lunch. The connection is sleeping but the locks are held. Kill it.
  2. suspended sessions waiting on lock forever. Root cause is the session blocking them; sp_WhoIsActive @find_block_leaders = 1 shows the block leader. Kill the leader.
  3. background processes doing weird things. Rare. Usually a sign of a deeper issue. Don’t kill indiscriminately; some of them are SQL Server’s internal threads.

Monitoring best practices

  • sp_WhoIsActive every 30-60 seconds, logged to a table. Rotate the log (keep 7 days). One of the highest-leverage monitoring moves you can make.
  • Alert on “blocked sessions for > N seconds.” Run sp_WhoIsActive @find_block_leaders = 1 from the alert check; if there’s a block leader and the victim has been waiting > 30 seconds, ping the on-call.
  • Chart CPU / wait stats from sys.dm_os_ring_buffers so you have visibility over weeks, not just now.

Run this on your own machine

-- 1. Install sp_WhoIsActive from whoisactive.com
-- 2. Install the First Responder Kit for sp_BlitzFirst

-- 3. Run a query that takes a while, in one session:
BEGIN TRAN;
UPDATE Sales.Customer SET Name = Name + N' (updating)' WHERE CustomerId = 1;
-- Don't commit. Leave this session open.

-- 4. In another session:
EXEC sp_WhoIsActive @find_block_leaders = 1, @get_plans = 1;

-- 5. See the current state of waits
EXEC sp_BlitzFirst;

-- 6. Go back to session 1 and commit (or rollback)
COMMIT;

In an emergency you don’t get to read a lesson first. Practice these tools in peacetime. The muscle memory is what saves you at 3am.

Next, the final lesson of the course: a 30-minute SQL Server health check — the ordered checklist you run on a server you’ve never seen before. Putting everything from lessons 21 through 40 together.

Search