CPU By Query
Overview
The CPU by Query report is the drill-down from CPU by Database: which statements inside one database are using the CPU.
The chart is a scatter, because the question this report is opened to answer is not what is the biggest number but what kind of problem is it. Executions on one axis, average cost per execution on the other, and the four quadrants are four genuinely different problems needing four different fixes.
The four patterns
| Pattern | When | What to do |
|---|---|---|
| Fire | 1,000+ executions and 100 ms+ average | Expensive and running constantly. This is the one. |
| Heavy | 100 ms+ average, fewer executions | Expensive per execution – tune the query or index it. |
| Frequent | 1,000+ executions, cheap each | Cheap but relentless – batch it or cache it. |
| Minor | Neither | Background noise. |
“Tune the query” and “call it less often” are different projects. A total-CPU bar chart cannot tell you which one you need; the quadrant can.
The grid carries a Pattern column with the same four-way classification, so the chart and the grid tell one story rather than two.
What this fixes
The old report was a horizontal bar strip over a seven-column grid:
- It labelled each bar from
OBJECT_NAME, which is null for the ad hoc and parameterised SQL that makes up most of a normal plan cache – so most labels readRow 7 -with nothing after the dash. - CPU by query is a hard power law: the top statement is routinely a hundred times the twentieth. Past the first few bars everything was a hairline against the axis.
- The numbers in the grid were raw milliseconds with no separators.
Where to find it
A database-level report. Select a database and open CPU by Query, or double-click a tile on CPU by Database.
The page title reads CPU by Query for <database name>.
The plan cache caveat
Printed under the headline rather than left implied: total_worker_time is cumulative for as long as a plan stays in cache, so these totals are since the plan was compiled and not since any particular point in time. They reset when a plan is evicted or the service restarts.
Two statements in the same list may therefore be measured over completely different windows.
Reading the chart
Reading the grid
| Column | What it is |
|---|---|
| Pattern | Fire, Heavy, Frequent or Minor – the chart quadrant. |
| Total CPU | Cumulative CPU for this statement. Drawn with a bar. |
| % of DB CPU | Share of every cached statement for the database, not of the Top N on screen – so the figure does not move when you change the toolbar. |
| Executions | How many times it ran. |
| Avg CPU / Exec | The cost of one run. |
| Last Execution | When it last ran. |
| Object | The object name, where there is one. |
| Query | The statement text. |
% of DB CPU is measured against the whole database, deliberately. A share that changed when you clicked Top 100 would be useless.
The toolbar
| Button | What it does |
|---|---|
Top 25 · Top 50 · Top 200 · Top 500 |
How many statements. |
Refresh |
Reload now. |
Right-click a statement
The same menu comes up on a grid row and on a bubble in the chart.
| Item | What it does |
|---|---|
View Execution Plan |
Opens the Plan Analyzer on the plan for that statement. |
Open in Query Advisor |
The statement text, with its plan attached so the plan analysis button is live. |
Copy Query to Clipboard |
The full statement text. |
Double-clicking a row or a bubble opens the Query Advisor, the same as the menu item.
The plan is read when you ask for it, not with the grid. Reading a plan for all 500 rows to show the one you open would make the report far slower for no gain.
“There is no execution plan available for this statement” means the plan was evicted from the cache between this page loading and your click. The CPU total on that row has been reset to zero along with it, so Refresh and it will be gone.
How to read the report
- Look at the Fire quadrant. Expensive and constant is where the CPU is.
- Then Heavy. One expensive statement is usually a tuning or indexing job.
- Then Frequent. Cheap and relentless is usually an application problem – batching, caching, or a loop that should be a set operation.
- Check Last Execution. A large total from a statement that has not run for a week is history.
- Compare Executions against Avg CPU. They are the two axes and they lead to different fixes.
- Read the Query text before assuming which it is.
Common patterns
One statement in Fire, everything else Minor. The best possible outcome for a tuning session. Fix one thing, fix the database.
A cluster in Frequent with tiny average cost. Usually a row-by-row loop in an application. Total CPU is large, per-execution cost is trivial, and no amount of query tuning will help – the fix is calling it less.
A statement in Heavy that runs nightly. Expensive but scheduled. Worth tuning, not worth an incident.
Everything Minor and CPU is still high. The CPU is not in the plan cache. Look at backups, CHECKDB, or a non-SQL process on the box.
Ad hoc statements dominating. Un-parameterised SQL. Look at Plan Cache – the same cause bloats the cache and shows up as single-use plans.
Messages you may see
Nothing cached:
No cached statements for this database have used CPU. This reads sys.dm_exec_query_stats, which only holds plans currently in cache – a recent restart or memory pressure empties it.
Timed out:
The CPU by query lookup did not finish in time. A very large plan cache can take a while to read; try a smaller Top N.
Related reports
The related links bar across the top of the report carries the first five of these.
| Report | Why you would go there |
|---|---|
| Long Running | Whether the statement burning the CPU is also the one that is slow. Often it is not. |
| Active Queries | Whether it is running right now. |
| Page Reads by Query | Whether the CPU is really the cost of reading, in which case an index is the fix. |
| Page Writes by Query | The same question for the write side. |
| Plan Cache | Where the ad hoc SQL problem shows up. |
| CPU by Database | The level above – which database the CPU is going to. |
| Missing Indexes | The usual fix for a Heavy statement. |
| Waits | Whether the instance is CPU-bound at all. |
Frequently asked questions
Why a scatter rather than bars? Because CPU by query is a power law, so bars past the first few are hairlines – and because the useful question is which kind of problem a statement is, which is a position in two dimensions rather than a length.
Why does % of DB CPU not change when I change Top N? Because it is measured against every cached statement for the database. A share that moved with the toolbar would be meaningless.
Why do most of my statements have no Object name? Because ad hoc and parameterised SQL has none. That is normal, and it is why the old report’s labels were mostly empty.
Are these totals since the server started? No – since each plan was compiled. Plans are evicted independently, so two rows may cover different windows.
What do I do about a Frequent statement? Usually nothing to the query. Look at how often the application calls it.