What is Active
Overview
What is Active is the instance level answer to why is this server slow right now: every running request across every database, ranked, with the blocking chains drawn as chains and the execution plan one right-click away.
The sample is what makes this report different
The counters on sys.dm_exec_requests are cumulative. They answer “what has this request cost since it started”, which is the wrong question during an incident: a report that has been running forty minutes and is now sitting on a lock beats a query that started nine seconds ago and is pinning four cores.
This report snapshots the counters, waits a second, and subtracts, which turns them into a rate. That is the question actually being asked.
The cost is that every refresh takes at least that second, which is why the refresh interval floors at five seconds and defaults to fifteen.
What this replaces
The old page was declared type="Grid" in ReportsXmlConfig.xml, which routes through a path that passes all three chart columns as zero, so it could not have had a chart under any circumstances. Twenty columns of DMV output and nothing else. Underneath that were four defects:
| Fault | What it meant |
|---|---|
order by 3 desc, and column 3 is wait_type |
The top of the report used to triage a slow server was whichever wait type sorted latest in the alphabet, so WRITELOG outranked a four minute lock wait. |
abs(r.cpu_time - t.cpu_time) over a LEFT JOIN |
A request that started during the sample had no snapshot row, so the cell came back empty, blanking exactly the arrivals worth seeing. The ABS hid the other half: a session whose request finished and whose next one started inside the window matched on session id alone and produced a negative delta turned positive. |
last_request_start_time = start_time on the join, and CROSS APPLY on dm_exec_sql_text |
Each silently dropped rows. The report reported the instance quieter than it was. |
blocking_session_id was not in the query |
On the report you open to find out why a server is slow, the most common answer could not be seen. A session asleep on an open transaction, the ordinary head of a chain with no row in sys.dm_exec_requests at all, could not appear under any circumstances. |
Where to find it
| Route | How |
|---|---|
| Server tree | Right-click the server → Instance Reports → What is Active |
The page title reads What is Active on <server name>.
This is the instance scope report. The database scope equivalent is Active Queries, which shares the same lane chart but is filtered to one database.
Requirements
- SQL Server 2008 or newer, for
sys.dm_tran_active_snapshot_database_transactions. VIEW SERVER STATEon the instance.- Three columns are substituted per version rather than the query being version-gated as a whole: dop needs 2016 or newer, and
database_idandopen_transaction_countonsys.dm_exec_sessionsneed 2012 or newer.
The three views
Live
One lane per request ranked by elapsed time, the bar split into CPU, the named wait, and time spent runnable, with blocked requests indented under their blocker.
By Wait
The same requests regrouped by wait category, answering what the instance as a whole is stuck on rather than what any one session is doing. This is the same chart the database level Active Queries page uses.
Burn
Plots the one second sample the old report was already taking and never drawing. This is where the rate columns become a picture: a request in the top right is burning CPU and reads right now, regardless of how long it has been running.
Reading the grid
| Column | What it is |
|---|---|
| Session | The session id. |
| Blocked By / Blocking | The chain, in both directions. |
| Status | Running, runnable, suspended, sleeping. |
| Command | The command type. |
| Elapsed | How long the request has been running. |
| CPU | Cumulative worker time. |
| CPU/sec | Rate, from the sample. This is the column to sort on during an incident. |
| Read/sec | Rate, from the sample. |
| Wait / Wait Type / Resource | What it is waiting on. |
| Isolation | The transaction isolation level. |
| Snapshot | Whether it holds an active snapshot transaction. |
| Reads / Writes / Rows | Cumulative counters. |
| Open Tran | Open transaction count. |
| Progress | Percent complete where SQL Server reports it. |
| Login / Host / Program | Who, from where, and with what. |
The execution plan
Right-click a lane, a bubble or a grid row to open the execution plan in the Plan Analyzer.
The plan is fetched for the one session clicked, rather than for everything on every refresh. Where the server can produce it, the live plan from sys.dm_exec_query_statistics_xml is preferred over the cached estimated plan. For a query you are watching because it will not finish, actual row counts are the whole point.
Both DMVs are instance scoped and keyed by session id, so no database context switch is needed.
Killing a session
Kill Session is offered on the right-click menu. It is the one action on this page that changes anything, it names the session it is about to kill, and it asks first.
How to read the report
- Sort by CPU/sec, not CPU. Cumulative CPU tells you what has already been spent. The rate tells you what is being spent now.
- Follow the indentation. The request at the top of an indented group is the cause; everything under it is a symptom.
- Look for a sleeping head of chain. A session with an open transaction and no running request is the classic blocker, and the old report could not show it at all.
- Switch to By Wait when several unrelated sessions are slow. One dominant category usually means one cause.
- Switch to Burn to separate “running a long time” from “costing a lot right now”.
- Right-click for the plan before killing anything.
Common patterns
A long lane with almost no CPU/sec. Waiting, not working. Read the wait type before doing anything.
A short lane at the top of CPU/sec. Just arrived and expensive. The old report could not surface this at all.
A deep chain under a sleeping session. An application that opened a transaction and went away. Killing the head releases everything under it.
Everything runnable. CPU pressure on the instance, not a slow query.
Where the data comes from
sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_connections, sys.dm_os_waiting_tasks and sys.dm_tran_active_snapshot_database_transactions, read twice a second apart so the counters can be turned into rates.
Nothing is stored. This is a live picture.
Settings
| Setting | Default | Values |
|---|---|---|
WhatIsActiveRefreshInterval |
15 seconds |
Floors at 5, because each refresh includes the one second sample |
Messages you may see
Version too old:
This report needs SQL Server 2008 or newer. It reads the active snapshot transactions view, which SQL Server 2005 does not have.
Timed out:
The active request lookup did not finish in time. A heavily blocked instance can take a while to answer sys.dm_os_waiting_tasks; try the Refresh button.
Related reports
| Report | Why you would go there |
|---|---|
| Active Queries | The same lane chart scoped to one database. |
| Blocking Tree | Blocking drawn as an icicle, when the chains are the whole story. |
| Sessions | Everything connected, including what is idle. |
| Connections | The physical layer: who dialled in, from where, over what. |
| Waits | What the instance waits on over time rather than right now. |
Frequently asked questions
Why does each refresh take a second? Because the counters are cumulative and a rate needs two readings. That second is what makes CPU/sec and Read/sec possible, and it is why the interval floors at five seconds.
Why is CPU/sec empty for a request? It arrived during the sample, so there was no earlier reading to subtract. That is one row rather than a blanked column, which is what the old report produced.
How is this different from Active Queries? Scope. This is every database on the instance; Active Queries is one database. This one also has the Burn view and the sampled rate columns.
Can a sleeping session appear here? Yes, and that is deliberate. A session asleep on an open transaction is the ordinary head of a blocking chain, and it has no row in sys.dm_exec_requests at all.
Is the plan the live one or the cached one? The live one where the server can produce it, because for a query that will not finish the actual row counts are the point. It falls back to the cached estimated plan.