Why Query Cost Percentages in a Plan Mislead You

The plan marks one statement as 80 percent of the batch, so everyone tunes it first. Query cost percentages are optimizer estimates, even when the plan is labeled actual. Measure the statements before deciding which one consumed the time and reads.

What Query Cost Percentages Actually Divide

SSMS displays a statement's estimated cost as a share of the estimated cost of statements in the batch. The denominator is the sum of those estimated costs. The number is not the percentage of elapsed time, CPU, or logical reads measured during execution. An actual plan adds runtime row counts and other observations, but it does not replace the cost percentages with measured work.

I see the percentage used as a tuning queue when a procedure contains several statements. That is convenient, and it can be wrong. Functions, cardinality errors, waits, and spills can make runtime differ sharply from the estimate. What did the slow call actually spend its time doing?

A Two-Statement Lab for Query Cost Percentages

The example uses catalog rows so it runs in a test database without permanent objects. One statement counts a joined set. The other runs FORMAT over many values. FORMAT can consume substantial CPU that the simple estimated cost does not capture well. Plan percentages and timing vary by version and row count; repeat with a larger local table if the catalog is too small to show a clear difference.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT COUNT_BIG(*) AS joined_count
FROM sys.all_objects AS a
JOIN sys.all_columns AS c ON c.object_id = a.object_id;
SELECT SUM(LEN(FORMAT(a.object_id, 'N0')))
       AS formatted_length
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

Run this in a test window. The cross join looks huge, but the optimizer evaluates FORMAT once per row of the first input and scales the partial sums, so it finishes quickly. In my test, the plan gave the FORMAT statement the smaller cost share, yet it used far more CPU than the join. The point is to observe the difference between estimated plan share and actual work, not to benchmark FORMAT as an application design. Read the Messages tab for each statement's CPU, elapsed time, and logical reads. The slow statement can have modest reads and large CPU.

Read STATISTICS IO by Statement

STATISTICS IO reports logical and physical reads for tables accessed by each statement. A high logical-read count shows buffer pages touched, even when the pages were already in memory. Physical reads vary with cache and storage conditions, so do not compare one cold run with one warm run and claim a tuning win. A function-heavy query can consume CPU without large logical reads. That is why IO and TIME belong together.

The Messages tab can be noisy when a batch touches several tables. Keep the two statements separate during a careful comparison, or use distinctive comments and capture their DMV rows. I save the result values too. An optimization that changes a count is a bug, no matter how attractive the timing becomes.

Measure CPU and Elapsed Time

STATISTICS TIME prints parse, compile, and execution timing. CPU time measures processor work. Elapsed time includes waits and parallel scheduling. A query with low CPU and high elapsed time can be blocked or waiting on I/O. A parallel query can spend more total CPU than elapsed wall time. Treat those as different measures, not competing versions of the same number.

I run each statement several times under representative parameters and record the range, not just the best result. On a busy server, concurrency can move elapsed time from run to run. Query Store is useful for longer history, while STATISTICS TIME gives a direct lab measurement. Neither turns the plan's percentages into runtime percentages.

Check Cached Statement Counters

sys.dm_exec_query_stats stores aggregate and last-execution counters for cached statements. Find the exact SQL text and statement offsets, then inspect last_worker_time, last_elapsed_time, last_logical_reads, and execution_count. The sample below locates a distinctive fragment of the second statement. It can match other batches, so verify the returned text and plan handle.

SELECT TOP (20) qs.execution_count,
       qs.last_worker_time, qs.last_elapsed_time,
       qs.last_logical_reads, qs.total_worker_time,
       st.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.text LIKE N'%formatted_length%'
ORDER BY qs.last_worker_time DESC;

These values are cumulative or last-execution observations while the plan remains cached. They reset when the plan leaves cache. The batch text can contain both statements, and the DMV row refers to a specific statement offset. Use those offsets when you need precise attribution. Also remember that the cache omits a plan after eviction, so absence is not evidence of zero work.

Find the First Estimate That Misled the Plan

Cost percentages can still help navigate a plan, but they are weak evidence for prioritizing a batch. If the measured slow statement has bad row estimates, find the first operator where estimated and actual rows diverge. Then inspect the predicate, statistic, or parameter that produced the estimate. If the measured cost is CPU from a function, an index change can do little. If the time is blocking, the plan shape is not the immediate cause.

A plan's operator cost is a model used to compare candidate plans during compilation. It is not a bill from the processor after execution. That distinction explains why a query can show a small plan percentage while occupying the server. I keep the measured CPU, duration, and reads beside the plan screenshot so the next person does not chase the largest colored box.

Rank Work by Users, Not Query Cost Percentages

Prioritize statements by total resource use, per-call latency, execution frequency, and business impact. A two-second query run once nightly can matter less than a 50-millisecond query run thousands of times per minute. Query Store or a monitoring system can provide the workload view that one plan lacks. Then use the plan to explain the measured problem.

I finish a tuning note with the exact statement, parameter values, result check, runtime counters, and the plan difference after a change. The percentage can remain on the screenshot as a visual aid. It should not be the reason the work was chosen. Measure first, then read the plan with a question in mind.

Related reading on this blog: Query Cost 100% and Number of Rows Read: Execution Plan.

Plan cost percentage is not elapsed time, it is an optimizer estimate for comparison.

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