Active Queries
Overview
The Active Queries report answers three things about a database right now: what is running, how long it has been running, and what it is waiting on.
Each bar is split three ways, which is the whole point of the shape:
- CPU, time actually spent executing.
- The named wait, time spent waiting on a resource.
- Runnable, time spent ready to run and queueing for a scheduler.
A request that is 90% CPU and one that is 90% waiting on a lock are different problems, and on a single-length bar they look identical.
This is the database scope report. The instance scope equivalent is What is Active, which shares this lane chart, covers every database, and adds sampled per-second rate columns and a Burn view. If you do not already know which database the problem is in, start there.
What this replaces
The old page was a pie of up to twenty slices weighted by wait time over a six column grid. Six things were wrong with it, and none of them were cosmetic:
| Fault | What it meant |
|---|---|
| The pie charted the wrong number | Weighted by wait time, a query burning a CPU core for six minutes with no waits got a one millisecond sliver, and a session parked on a lock was the biggest thing on the page. |
| Colours dealt by row order | Every refresh repainted the chart in different colours. |
| Blocking was in the data and not in the picture | blocking_session_id was a bare number in a column, so who is stuck behind whom had to be reassembled by reading spids back and forth across rows. |
| A full execution plan fetched for every request, every refresh | sys.dm_exec_query_plan was selected for every row and the column was never read. |
| No elapsed time, no CPU, no reads, no progress | Wait time was the only number on the page, printed as raw milliseconds. |
| The statement substring was wrong | Division binds tighter than subtraction, so any statement inside a batch was extracted too long and ran on into the statements after it. |
Where to find it
| Route | How |
|---|---|
| Server tree | Expand a database → Real Time → Active Queries |
The page title reads Active Queries for <database name>.
Requirements
VIEW SERVER STATEon the instance, forsys.dm_exec_requestsand its neighbours.- Nothing is installed on the monitored instance.
The two views
Live
One lane per request, ranked by elapsed time, longest first. Blocked requests are indented under the request blocking them, so a blocking chain reads as a shape rather than as a column of session ids to cross-reference.
That indentation is the single biggest difference from the old page. Blocking was always in the data; it was never in the picture.
By Wait
The same requests regrouped by wait category, answering what the database as a whole is stuck on rather than what any one session is doing.
Use Live when you are chasing a specific session. Use By Wait when several things are slow and you do not yet know whether they share a cause.
Reading the grid
| Column | What it is |
|---|---|
| Session | The session id. |
| Blocked By | The session blocking this one, if any. |
| Blocking | How many sessions this one is blocking. |
| Status | Running, runnable, suspended, sleeping. |
| Command | The command type. |
| Elapsed | How long the request has been running. |
| CPU | Worker time consumed. |
| Wait | Time spent waiting. |
| Wait Type | The current wait type. |
| Resource | What the wait is on. |
| Reads / Writes | Logical reads and writes. |
| Open Tran | Open transaction count. |
| Progress | Percent complete, where SQL Server reports it. |
| Login / Host / Program | Who, from where, and with what. |
| Database | The database context of the request. |
| Query | The statement text. |
Progress is only populated for the commands SQL Server reports it for, which is mostly backups, restores, DBCC and rollbacks. Blank is normal.
Database is present even though the page is scoped to one database, because a request can change context mid-batch and it is worth seeing when it has.
The execution plan
Right-click a lane or a grid row to open the execution plan in the Plan Analyzer.
The plan is fetched for the one session you clicked, rather than for everything on every refresh. That is both cheaper than the old page and fresher. Where the server can produce it, the live plan is used, which shows actual row counts for a query that is still running.
Killing a session
Kill Session is on the right-click menu. It names the session it is about to kill and asks first. It is the only action on this page that changes anything.
Refreshing
The page refreshes itself on a timer and says so under the header, in the same way Sessions, Connections and Blocking Tree do. Refresh re-reads immediately.
How to read the report
- Look at the top lane. Longest running first is almost always where to start.
- Read the split, not the length. Mostly CPU is a plan problem. Mostly wait is a resource problem. Mostly runnable is CPU pressure on the instance rather than on this query.
- Follow the indentation. The request at the top of an indented group is the one to deal with. Everything under it is a symptom.
- Switch to By Wait if several unrelated sessions are slow. A single dominant category usually means one cause.
- Right-click for the plan before changing anything.
- Go to What is Active if the cause looks like it is outside this database. A blocker in another database cannot appear here.
Common patterns
One long lane, everything else short. A single slow query. Read the plan.
A deep indented stack under one short request. The blocker is not slow, it is just holding something. Look at what it has open rather than at how long it has run.
Every lane mostly runnable. CPU pressure on the instance. The queries are not slow; they are queueing.
A blocked request whose blocker is not on the page. The blocker is in another database. Only What is Active can show you both ends.
Many lanes, all short, constantly changing. Normal busy. Nothing here is a problem.
Where the data comes from
sys.dm_exec_requests joined to the session, connection and text views, filtered to this database. Execution plans are fetched on demand for one session at a time.
Nothing is stored. This is a live picture and there is no history behind it.
Settings
| Setting | What it holds |
|---|---|
ActiveQueriesShape |
Which view the page opens on, Live or By Wait |
ActiveQueriesRefreshInterval |
How often the page re-reads |
Per-user rather than per-database, so switching databases keeps the view and interval you chose.
Messages you may see
Nothing running:
Nothing is running against this database right now. The page checks again every …
Timed out:
The active query lookup did not finish in time. A heavily blocked instance can take a while to answer; try the Refresh button.
Related reports
| Report | Why you would go there |
|---|---|
| What is Active | The same shape at instance scope, with sampled rate columns and the Burn view. |
| Blocking Tree | The whole instance’s blocking, drawn as an icicle. |
| Waits | What the instance waits on over time rather than right now. |
| Connections | Who is connected to this database, whether or not they are running anything. |
| CPU by Query | Which queries cost the most across the plan cache, rather than right now. |
Frequently asked questions
Why is a session shown as running with no wait type? Because it is on a CPU. That is the healthy state for a query doing work.
What does runnable mean? Ready to execute and queueing for a scheduler. A lot of runnable time across many requests is CPU pressure, not a slow query.
Why is Progress blank? SQL Server only reports percent complete for certain commands, mostly backups, restores, DBCC and rollbacks. For everything else there is no number to show.
Why does the plan only open on right-click? Because fetching a plan is expensive and the old page did it for every request on every refresh and then discarded it. Fetching one on demand is cheaper and gives you a fresher plan.
Does this show queries in other databases? No. It is scoped to the database you opened it against, which is also why a blocker living in another database will not appear. Use What is Active for the whole instance.
How is this different from What is Active? Scope, and two extras. That page covers every database, samples the counters a second apart to give per-second CPU and read rates, and has a Burn view built on that sample. This one shares the lane chart and is filtered to one database.