The thickest arrow and largest percentage in a plan can send you to the wrong operator. Operator cost percentages are estimates, even when the plan includes actual runtime details.
Know What Operator Cost Percentages Describe
SQL Server's optimizer estimates a plan's cost using its model of CPU and IO. The percentages shown under operators divide that estimated cost across the chosen plan. They do not update to actual elapsed time after execution. A plan can estimate an expensive sort and then process only a few rows, while a small nested loops operator repeats a lookup thousands of times. I check estimated rows against actual rows before deciding where to focus. A large mismatch explains why the chosen plan can perform badly even when its cost diagram looks tidy.
The percent is still useful for understanding the optimizer's decision. It tells you what the optimizer thought was expensive under its assumptions. It does not tell you where the query spent wall-clock time on this run.
Capture the Right Evidence
In SSMS, enable Include Actual Execution Plan and SET STATISTICS IO, then run a representative query in a safe environment. Read the operator Properties pane. Actual Number of Rows and Number of Executions reveal repeated work. On supported versions and plan forms, runtime counters also show elapsed and CPU time per operator. Inspect Actual Rows Read and Actual Logical Reads where available, and watch for warnings such as spills or implicit conversions. Logical reads from STATISTICS IO tell you which tables consumed buffer reads when an operator does not expose that detail.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT o.object_id, o.name
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE s.name = N'dbo'
ORDER BY o.name;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;Build a Counterexample to Operator Cost Percentages
Create a test table with a skewed distribution and query both a common and rare value. A plan compiled for one value can be reused for the other. The displayed operator costs remain estimates for that compiled plan, while actual rows change. The exact chosen plan depends on data, statistics, and version, so do not promise a particular shape from a tiny sample. The lesson is in the mismatch, not a rehearsed percentage. I use the actual plan's row and execution counters to point at the work the optimizer did not anticipate.
Ask whether a costly-looking operator ran once or whether a cheap-looking operator ran for every outer row. Multiplication is where a small estimate can become a large real cost.
CREATE TABLE #PlanDemo (id int NOT NULL PRIMARY KEY, category int NOT NULL);
INSERT #PlanDemo(id, category)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) <= 9900 THEN 1 ELSE 2 END
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
CREATE INDEX IX_PlanDemo_Category ON #PlanDemo(category);
EXEC sys.sp_executesql N'SELECT id FROM #PlanDemo WHERE category = @category;',
N'@category int', @category = 2;
EXEC sys.sp_executesql N'SELECT id FROM #PlanDemo WHERE category = @category;',
N'@category int', @category = 1;Run the block with Include Actual Execution Plan turned on. The second call reuses the plan compiled for the rare value. On its seek, compare Estimated Number of Rows with Actual Number of Rows. The gap is large, and the cost percentage does not show it.
Find the First Wrong Assumption
An estimate problem can start at a filter, join, table variable, stale statistic, or parameter-sensitive predicate. Follow the plan from data access toward the root and find the first large estimated-versus-actual row mismatch. Later operators inherit that mistake. Updating statistics, changing a predicate, or choosing an index can help, but confirm the cause before changing anything. I have seen teams rebuild the most expensive-looking index while the real issue was a missing join condition two operators earlier. The rebuild was busy work with excellent formatting.
Read the predicate and seek predicate separately. An index seek can read far more rows than it returns when a residual predicate filters afterward. Its friendly name does not make its workload small.
Treat Time Carefully
Operator elapsed times in an actual plan need context. Parallel operators have per-thread counters, and child operator time contributes to parent work. Do not add all displayed times and call that query duration. Waits and client result consumption can also make the request's elapsed time differ from CPU time. For a quick triage, compare actual rows, executions, reads, spills, and the longest-running areas. Then test a specific fix against total query duration and logical reads under comparable conditions.
One execution is a clue, not a baseline. Repeat with the parameter set users actually send. Query Store can show whether a plan is consistently slow or only slow for one shape of input. Record the plan_id and time window before comparing.
Read the Plan in Execution Order
The graphical plan is drawn in a convenient layout, but data usually flows from leaf operators toward the root. Start at the accesses, follow the rows, and note where estimated and actual counts diverge. A lookup with a small estimated cost can run once per outer row. A sort with a large estimated slice can finish quickly if the actual row count is tiny. I use the Properties pane to compare actual executions and rows at both ends of a join. This is more reliable than scanning for the largest percentage label.
Check warnings and runtime counters at the same time. A spill suggests a memory grant problem; an implicit conversion can turn a seek into a wider read. A large Actual Rows Read versus Actual Rows gap suggests a residual predicate. Each observation gives a different next question. Do not change three indexes at once. Pick the earliest incorrect assumption or repeated operation and test one correction.
Explain the Result to the Reviewer
A useful plan note names the operator, the estimated and actual row behavior, the number of executions, the table reads, and the user symptom. It also names the test parameters. I avoid saying "the 80 percent sort is slow" unless actual runtime evidence supports that statement. The optimizer cost model is a planning estimate, not a performance trace. On a parallel plan, add the thread context so counters are interpreted correctly.
What would disprove your hypothesis? If removing a proposed index hint leaves the same reads and duration, the hint was not the cause. If a statistics update corrects the row estimate but the query is still slow, follow the new evidence. I leave the before and after actual plans with the test record. A plan picture is useful when the accompanying sentence says exactly what it proves and what it does not.
Prove the Fix Beyond Operator Cost Percentages
Change one thing in a restored copy: a statistic, an index, or a query predicate. Compare actual plans, rows, logical reads, CPU, and elapsed time. Check writes if the change adds an index. A plan that improves one selective lookup can hurt a broad report. I also look for blocking or memory grants that moved rather than disappeared. The goal is lower work for the real workload, not a prettier percentage.
What would persuade you that the hot spot moved? Name that evidence before the change. If the only improvement is a lower graphical cost number, keep investigating. The optimizer's estimate is useful context, but the users wait for actual work to finish.
Related reading on this blog: Why Query Cost Percentages in a Plan Mislead You and Execution Plan: Estimated vs Actual: SQL in Sixty Seconds #113.
A cost percentage is not a stopwatch, it is an optimizer estimate.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
