The plan points at a large percentage, and everyone wants to fix that operator. Operator costs describe estimated work rather than measured elapsed time. Read the execution evidence before choosing your target.
Understand Operator Costs Above Each Icon
I ask whether a plan percentage came from execution timing before accepting it as the bottleneck. The percentage comes from the optimizer's cost model. It isn't a stopwatch result.
The optimizer estimates CPU and I/O components while choosing a plan. Those internal costs help compare alternatives. Their units aren't a direct prediction of milliseconds on your hardware.
The percentage is relative to estimated costs within the displayed plan. A ninety-percent operator in a small plan can perform little actual work. Another operator with a smaller percentage can become expensive when its estimate is wrong.
The figures remain estimates even when SSMS displays an actual execution plan. Runtime information is added to the plan. The estimated cost model doesn't turn into measured cost after execution.
Operator costs are still useful context. They explain where the optimizer expected work. Treat them as expectations to check, not measured results to quote.
Distinguish Operator Costs From Subtree Cost
Estimated operator cost describes one modeled operator's contribution. Estimated subtree cost includes its descendants' modeled contributions. Read the property name before comparing two numbers.
A parent above several expensive branches naturally has a larger subtree cost. That doesn't mean the parent itself performed all the work. Confusing those scopes sends tuning in the wrong direction.
Likewise, plan percentages are relative within their comparison context. Don't compare percentages from two unrelated plans as absolute resource measurements. Their denominators differ.
Estimated row counts feed the cost calculation. An error in those counts changes the expected work downstream. A lookup estimated for a small population can become much larger at runtime.
I follow that row-count error toward the earliest useful source. Fixing the resulting expensive operator alone can leave the root estimate wrong. The next execution then repeats the same surprise.
Build a Plan You Can Inspect
Run the following example in an ordinary SSMS connection. Enable Include Actual Execution Plan before execution. The temporary sample doesn't change an application table.
The generated inputs make repeated customer groups. They aren't measured row counts from a production workload. COUNT_BIG reports the loaded population if you need it for the test notes.
The aggregation and ordering give the optimizer choices about processing the data. The exact operators depend on the loaded sample and server. Don't expect one guaranteed icon arrangement.
STATISTICS IO and TIME provide statement-level evidence. Save that output with the actual plan. The plan's estimated percentage alone isn't a substitute for either result.
Run the same query with the same input when comparing a change. A different requested result makes the comparison harder to interpret. Correctness belongs beside the resource observations.
CREATE TABLE #CostSalesDemo
(SaleId int NOT NULL PRIMARY KEY, CustomerId int NOT NULL, Amount decimal(12,2) NOT NULL);
INSERT #CostSalesDemo(SaleId, CustomerId, Amount)
SELECT n, n % 100, 10.00
FROM (SELECT TOP (20000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS s;
SELECT COUNT_BIG(*) AS LoadedRows FROM #CostSalesDemo;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT CustomerId, SUM(Amount) AS CustomerAmount
FROM #CostSalesDemo
GROUP BY CustomerId ORDER BY CustomerAmount DESC;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
Check Operator Costs Against Actual Rows
Select an operator and inspect its estimated rows. Then inspect actual rows and actual executions. The number of executions matters for repeated inner work.
A nested loops inner branch can run once for each outer row. A small per-execution result therefore adds up. Read total actual work and execution counts together.
Look for the first substantial divergence between expected and observed rows. A later sort receiving too many rows is a consequence. The earlier predicate or join deserves examination.
Check parameter values and statistics used for compilation. A different parameter distribution can explain a reused plan's surprise. An actual plan describes its particular execution context.
What row estimate made the optimizer prefer this access path? Answer that before replacing the operator with a hint. The shape is the result of several prior decisions.
Read Runtime Properties With Their Scope
Recent actual plans expose elapsed time, CPU and read information through supported runtime properties. Look at actual logical reads on access operators where supplied. Use statement STATISTICS IO as a cross-check.
Not every operator exposes every metric under every profiling path. Lightweight profiling provides rows with less timing detail in some contexts. An absent property isn't proof that an operator consumed no resources.
Elapsed and CPU time mean different things. Waiting increases elapsed time without equivalent CPU. Parallel workers can accumulate CPU beyond the wall-clock elapsed duration.
Operator timings also have scope and overlap considerations. Row-mode and batch-mode reporting aren't identical. Don't sum every displayed elapsed figure into a supposed statement duration.
Use the statement's QueryTimeStats for overall timing where available. Then interpret operator metrics within that execution's reporting model. An attractive total created from overlapping measurements isn't evidence.
Inspect Reads Beyond the Percentage
A selective lookup provides another useful comparison. The following query filters the same temporary table by customer, so run it in the same session. Inspect its access operator and actual read information.
Without an index on CustomerId, the optimizer has a limited set of efficient choices. Adding one on the sample supplies another access path. Compare the actual work without assuming the index must win.
The included Amount makes the second test capable of reading the required value from that index. It doesn't change the selected business rows. Save both result and plan comparisons.
A scan can still be appropriate when the qualifying fraction is large. Don't label every scan a defect. The actual reads and workload requirement settle the question.
Operator costs can help explain why an alternative was chosen. Actual rows and reads show whether that choice served this execution. Keep both forms of evidence in the review.
SET STATISTICS IO ON;
SELECT SaleId, Amount FROM #CostSalesDemo WHERE CustomerId = 10;
CREATE INDEX IX_CostSalesDemo_Customer ON #CostSalesDemo(CustomerId) INCLUDE(Amount);
SELECT SaleId, Amount FROM #CostSalesDemo WHERE CustomerId = 10;
SET STATISTICS IO OFF;Fix the Work the Execution Demonstrates
Spill warnings, unexpected rows and repeated lookups deserve attention even with small cost percentages. Check the associated memory grant and predicates. Those properties explain actual work more directly.
I preserve the original plan when testing a change. I compare the same representative parameters and concurrency conditions. A quiet single call doesn't represent every application execution.
Report measured timings and reads only after collecting them on your server. The examples here supply the experiment rather than its outcome. The largest printed percentage doesn't receive an automatic repair ticket.
Use operator costs to understand the optimizer's expectation. Use actual execution evidence to choose the next investigation. That keeps the plan review grounded in the work users experienced.
Related reading on this blog: Why Query Cost Percentages in a Plan Mislead You and Number of Rows Read: Execution Plan.
A plan percentage is not elapsed time, it is a relative estimate that needs execution evidence.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
