SQL Server Execution Plan Operators: Costing, Plan Shape, and What to Ignore

You’ve seen individual operators already (Seek, Scan, Key Lookup). Now let’s read a whole plan the way someone diagnosing a real incident actually does.

Cost Percentages: Useful, But Not the Whole Story

The operator showing “87% cost” is a reasonable place to start looking — but it’s computed from the optimizer’s row estimates, which you now know can be badly wrong under parameter sniffing or stale statistics. Cross-check cost % against actual row counts, not just at face value.

Reading Plan Shape, Not Just Individual Icons

• Thick arrows between operators represent many rows flowing — trace these back to find where row counts balloon unexpectedly
• Warning icons (yellow triangle) flag things like implicit conversions or missing statistics directly in the plan — don’t skip past these
• Parallelism icons (yellow circle with arrows) show where the plan split across threads — useful context, not automatically good or bad

Estimated vs Actual: The Signal That Matters Most

-- Always use ACTUAL execution plan (Ctrl+M), not estimated — estimated never shows real row counts
SELECT * FROM dbo.OrderLog WHERE customer_id = 42;

Hover any operator and compare Estimated Number of Rows to Actual Number of Rows. A 10x+ gap anywhere in the plan is the single strongest signal something upstream (stale stats, a non-SARGable predicate, parameter sniffing) is misleading the optimizer — often more informative than the cost percentage itself.