Finding Which Database Uses the Most CPU on a Shared Instance

Task Manager shows one busy sqlservr.exe process, not the database responsible for the work. To find which database uses the most CPU, start with cached query statistics and then check their blind spots.

See Which Database Uses the Most CPU in Cached Plans

sys.dm_exec_query_stats stores cumulative worker time for cached statements. Use sys.dm_exec_plan_attributes to retrieve the plan's dbid attribute, then group totals by database. Worker time measures CPU work across executions, not elapsed wall time. I use it to find candidates during the incident and record the capture time. Plans can be evicted or recompiled, so the cache is a changing sample. A database with little cached history can look quiet even after a large query finished. On my test instance one row came back with a NULL name; its dbid was 32767, the hidden resource database that system procedures run from.

SELECT DB_NAME(CONVERT(int, pa.value)) AS database_name,
       SUM(qs.total_worker_time) AS total_worker_microseconds,
       SUM(qs.execution_count) AS executions
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
WHERE pa.attribute = N'dbid'
GROUP BY CONVERT(int, pa.value)
ORDER BY total_worker_microseconds DESC;

Understand Attribution Errors

The plan's database context is not always the same as every object read. An ad hoc query executed from master can join tables in another database and be attributed to master. Cross-database procedures and dynamic SQL add similar ambiguity. I examine the top statements' text and plans before naming a database owner. Cached totals also begin when each plan enters cache, not at a common clock time. Compare two snapshots if you need a rate for the current incident.

Do not divide the summed worker time by the current uptime and call it a precise per-database percentage. Plans have different lifetimes and parallel queries can accumulate CPU across workers.

Find the Expensive Statements

Sort individual cached statements by total_worker_time and inspect their text, execution count, and average CPU. A high total from a frequent small query calls for a different fix from one giant report. Include query_hash to group similar statements, but review literal and parameter differences before combining them. I look at logical reads and elapsed time too. A CPU problem can be caused by excessive row processing, scalar work, compilation, or a plan regression.

SELECT TOP (20) qs.query_hash, qs.execution_count,
       qs.total_worker_time, qs.total_worker_time / NULLIF(qs.execution_count,0) AS avg_worker_time,
       qs.total_logical_reads, st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_worker_time DESC;

Check Which Database Uses the Most CPU Over Time

Query Store records runtime statistics inside each enabled database, subject to capture policy and retention. Query its runtime intervals and CPU metrics in each candidate database. That history survives an instance restart and lets you compare before and after a release. It is still not a universal instance CPU ledger; queries executed in a different database context can remain attributed there. I collect the same interval from each database and label the units. Do not compare one database's busy hour with another database's whole week.

SELECT i.start_time, i.end_time,
       SUM(rs.avg_cpu_time * rs.count_executions) AS estimated_total_cpu_microseconds
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS i
  ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
GROUP BY i.start_time, i.end_time
ORDER BY i.start_time DESC;

Check the Instance Context

Compare database candidates with instance CPU utilization, active requests, and the time of the user complaint. A backup compression job or system task can consume CPU without appearing as a normal user query in the ranking. I inspect active sys.dm_exec_requests during a spike and look at wait types. If most sessions are waiting on something else, a high historical CPU total can be unrelated to the current slowdown. One chart rarely settles the question.

What changed before the CPU spike? A deployment, statistics update, or new report can narrow the search. Use the query text and plan history to test that lead.

Treat Ad Hoc and Cross-Database Work Honestly

The plan dbid attribute identifies the compilation context. It does not prove every page read or CPU cycle belonged to that database's tables. A query launched from master can reference another database with a three-part name. I inspect the top statement text and plan before assigning ownership. Cross-database procedures and dynamic SQL can further split the evidence. If application code routinely connects to master and names target databases explicitly, fix the connection default or report the attribution limit. A precise-looking pie chart built from plan context alone can point at the wrong team.

I also separate system work and backup compression from user-query CPU. Task Manager sees the process total, while query stats only cover cached query execution. A large difference between them is a reason to investigate, not a reason to force the numbers to add up.

Compare Windows, Not Lifetime Totals

Take snapshots of cached query stats during the incident and subtract totals for plans that exist in both captures. Note plans that arrived or disappeared. Query Store intervals provide a more durable comparison inside each enabled database, but capture policy and retention still apply. I use identical start and end times across databases and report the sum as attributed query CPU, not total server CPU. If a database is not using Query Store, mark its history as incomplete.

What would change your conclusion? If the top plan is an ad hoc batch compiled in master but reading another database, the ownership label changes. If a plan was evicted before the second snapshot, its missing delta cannot be treated as zero. I keep those caveats beside the ranking. The result is still actionable: identify the high-work query family, test its plan and reads, then verify the instance CPU and user latency after the fix.

Query Store needs to be read in each database. An instance-level loop can gather those per-database results, but the output should retain the source database name and interval. I do not combine all intervals into one unlabeled total. Plan context and database ownership can diverge, so top statements still need a text and object review. A pie chart cannot identify an owner just by choosing a color.

Report Which Database Uses the Most CPU, With Its Limits

State the observation window, cache reset risk, Query Store coverage, and any cross-database context. Then name the statements and owners that need tuning. I avoid telling a team that "their database uses 70 percent" when the method only sums surviving cached plans. A careful ranking is still useful. It tells you where to inspect actual plans, reads, and call frequency next.

After a fix, compare the same workload interval and query family. The server-level CPU graph should improve alongside the measured statement work. That is stronger evidence than a database label on a single screenshot.

Related reading on this blog: Troubleshooting High CPU and Query for CPU Pressure.

Instance CPU is not a database name, it is work you must attribute with evidence.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.