CPU by Database

Overview

The CPU by Database report shows which databases the CPU recorded in the plan cache was spent on, drawn as a treemap, with a drill-down into CPU by Query for any of them.

• Tile area is that database’s share of all cached CPU.
• Tile colour is how recently the database last ran anything.
• A hatched grey tile holds ad hoc SQL whose text resolves to no database, so the parts visibly sum to the whole.
• The header bar is the instantaneous CPU on the box – SQL Server against everything else – kept separate from the cumulative figures below it, because they measure different things.

That separation is the point of the report. Cumulative CPU since each plan compiled and “what the CPU is doing this minute” are two different questions, and multiplying one by the other – which the report this replaced did – produces a number that means neither.

What this fixes

Three things were wrong with the old half-pie and its four-column grid:

• The SQL aliased SUM(total_worker_time) – microseconds – as CPU_Utilized_in_Seconds, and the grid printed it raw, so the CPU column read 184392847261 and implied seconds.
• The “CPU %” column multiplied each database’s share of cumulative since-compile cache CPU by the instantaneous server CPU from the ring buffer. Weeks of history scaled by this minute’s load is not a meaningful quantity.
• Ad hoc SQL whose text resolves to no database was filtered out entirely, so on an ad-hoc-heavy server a slice of the CPU simply was not in the report.

---

Where to find it

Route	How
Server tree	Right-click the server → Instance Level Reports → CPU by Database
Server Overview page	Click the report link, or the CPU panel
Report arrows	Sits next to Sessions in the instance report order

The page title reads CPU Used By Database for <server name>.

---

Requirements

• VIEW SERVER STATE on the instance.
• The query timeout is 60 seconds. A very large plan cache takes a while to read.
• Nothing is installed on the monitored instance and nothing is stored.

---

The important caveat

These totals live in the plan cache, not in a history table. They are cumulative since each plan compiled, and they reset when a plan is evicted or the service restarts. The subtitle line says so on every load:

totals are since each plan compiled and reset on eviction or restart · oldest plan cached 07/29 04:12 · ad hoc SQL with no resolvable database shown as Unattributed

The oldest plan cached timestamp is the practical horizon of the report – nothing older than that is represented, whatever happened.

An amber notice appears when the instance has been up for less than 24 hours:

SQL Server restarted 3h 12m ago – these CPU totals only cover since then.

Memory pressure evicts plans, so a busy instance under memory pressure can show a much shorter history than its uptime suggests.

---

Reading the treemap

The header bar

A separate bar showing SQL Server’s share of the box’s CPU right now, against everything else. This is the only instantaneous number on the page and it is deliberately not mixed into the tiles.

Tile colour: recency

Band	Last execution	Meaning
Last hour (deepest blue)	within 1 hour	Actively running work.
Today	within 24 hours	Ran today.
This week	within 7 days	Ran this week.
Older (palest)	beyond 7 days, or never recorded	Cached CPU from a database that has not run anything lately.

A large pale tile is worth noticing: it is a database that consumed a lot of CPU at some point and has done nothing since. That is either a batch workload between runs, or plan cache holding history for something that has been decommissioned.

Colour is a single-hue blue ramp rather than a red-amber-green scale, because recency is not a verdict. In colour-blind mode the ramp is preserved.

Special tiles

• Unattributed – hatched and grey. Ad hoc SQL with no resolvable database is a different kind of answer, not a worse one, and the texture says so without using a colour that implies severity.
• Other – the tail beyond the current Top N, rolled into one grey tile so the map still sums to the whole instance. Its CPU is the instance total minus what the fetched rows carry.

Chips

A tile carries an Idle 34d chip once the database has not executed anything for 7 days or more.

The header lines

The summary line:

41h 12m of cached CPU across 14 databases · top 3 = 78% of all CPU · 4 active in the last hour

Chart interactions

Gesture	Result
Hover a tile	Tooltip with CPU, statement count and when it last ran
Click a tile	Selects the matching grid row
Double-click a tile	Opens CPU by Query for that database
Right-click a tile	The database menu, plus Copy Chart to Clipboard
Right-click empty space	Copy Chart to Clipboard only

The legend hint reads click a tile to find it in the grid – double-click to open CPU by Query – right-click for scripts.

---

Reading the grid

Column	Units	What it is
Database	–	Database name, or Unattributed, or Other.
Total CPU	time	Cumulative CPU across all cached plans for that database, formatted as a duration rather than raw microseconds. Drawn with an in-cell bar.
% of Instance	percent	Share of all cached CPU. Under 0.1% shows as <0.1%.
Statements	count	How many cached statements.
Avg / Statement	time	Total CPU divided by statement count.
Last Activity	relative	just now for anything inside 90 seconds, otherwise how long ago; never recorded when the DMV has no last execution time.
Status	–	Active when the database ran something in the last hour, Idle 34d past the idle threshold, otherwise blank.

Total CPU is a duration, not a raw counter. That is the single biggest readability fix over the old page, whose CPU column printed microseconds under a heading that said seconds.

---

The toolbar

Button	What it does
Top 20 · Top 50 · All	How many databases get their own tile before the rest roll into Other.
Refresh	Reload now.

A countdown sits to the right. The page refreshes itself every 60 seconds by default, on its own private timer – the right-now CPU bar is only worth having if it stays current.

---

Right-click actions

Item	What it does
Open CPU by Query for <database>	The per-query drill-down.
Copy Database Name to Clipboard	Just the name.
Copy Top CPU Statements Script to Clipboard	A read-only script returning the top 50 CPU consumers currently cached for that database.
Copy Chart to Clipboard	(chart only)

---

How to read the report

1. Read the header bar first. If SQL Server is not using much of the box’s CPU right now, the tiles below are history rather than a live problem.
2. Read the summary line. How concentrated is the CPU? top 3 = 78% says where to start.
3. Look at the largest tile, then at its colour. Large and dark blue is your current workload. Large and pale is history.
4. Check the Unattributed tile. A large hatched tile means much of your CPU is going to ad hoc SQL that cannot be attributed to a database – often an application sending un-parameterised statements.
5. Double-click the biggest tile to open CPU by Query and find the statements.
6. Check Avg / Statement. High total with few statements is a small number of expensive queries; high total across many statements is volume.

Common patterns

One database is most of the map, dark blue. Normal for a single-application instance. Drill in to find which queries.

A large pale tile. A database that burned CPU and then stopped. Either a batch job between runs, or plan cache still holding history for something retired.

A large hatched Unattributed tile. Ad hoc SQL that cannot be tied to a database. Usually an application building statement text rather than using parameters, which also bloats the plan cache.

Everything small and pale, header bar showing high CPU. The CPU being used right now is not in the plan cache – look at backups, CHECKDB, a non-SQL process on the box, or something outside the cache entirely.

Totals much smaller than you expect. The cache was recently emptied. Check the oldest plan cached timestamp in the subtitle and the restart notice.

---

Where the data comes from

sys.dm_exec_query_stats, aggregated per database, plus the ring buffer for the instantaneous CPU bar. Nothing is stored.

Because it reads the plan cache, this report shows what is currently cached, not everything that has ever run. For CPU over days and weeks, use the historic CPU reports.

---

Settings

Setting	Default	Values
CPUByDatabaseTopN	20	20, 50, 10000
CPUByDatabaseRefreshInterval	60	seconds, minimum 10

---

Messages you may see

Nothing cached:

No cached statements have used CPU on this instance. 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 database lookup did not finish in time. A very large plan cache can take a while to read; try the Refresh button.

---

Related reports

Report	Why you would go there
CPU by Query	The drill-down – which statements inside a database.
Waits	Whether the instance is CPU-bound or waiting on something else.
Sessions	Who is running the work.
CPU by Hour by Day	The historic view, from collected history rather than the plan cache.

---

Frequently asked questions

Why do the totals not match my monitoring tool? Because they come from the plan cache and cover only cached plans, from whenever each was compiled. They are not a wall-clock measurement of the instance.

What is Unattributed? Ad hoc SQL whose text does not resolve to a database. The old report filtered it out entirely; here it gets a hatched tile so the parts sum to the whole.

Why is a database I never use taking a big tile? Cached CPU is history. Check the tile colour and the Last Activity column – a pale tile with an Idle chip is a database that stopped running things a while ago.

What is the header bar measuring? The box’s CPU right now, split between SQL Server and everything else. It is instantaneous and deliberately kept apart from the cumulative tiles.

Why is CPU shown as a duration rather than a percentage? Because CPU time is what the DMV records. A percentage would need a wall-clock window, and the plan cache does not have one.

---