Getting a Plan and Reading It Right to Left

An execution plan is the optimizer showing its work: the sequence of physical operations it chose to turn your SQL into rows. Learning to read one is the single highest-leverage skill in this course, because every other module ends up pointing back at a plan.

Getting a plan

Three ways, in increasing order of usefulness.

Estimated plan — compiles the query without running it. Ctrl+L in SSMS, or:

SET SHOWPLAN_XML ON;
GO
SELECT o.OrderId, o.OrderDate, c.CustomerName
FROM   dbo.Orders   AS o        -- placeholder tables: substitute your report's
JOIN   dbo.Customer AS c ON c.CustomerId = o.CustomerId
WHERE  o.OrderDate >= '2026-01-01';
GO
SET SHOWPLAN_XML OFF;
GO

Useful when the query is too slow or too destructive to run. It contains estimates only.

Actual plan — runs the query and returns the plan annotated with what really happened. Ctrl+M in SSMS, or:

SET STATISTICS XML ON;
GO
SELECT o.OrderId, o.OrderDate, c.CustomerName
FROM   dbo.Orders   AS o
JOIN   dbo.Customer AS c ON c.CustomerId = o.CustomerId
WHERE  o.OrderDate >= '2026-01-01';
GO
SET STATISTICS XML OFF;
GO

This is the one you want almost always. Next lesson explains why.

Live query statistics — SSMS shows operator progress while the query runs (Query menu → Include Live Query Statistics). Excellent for a query that never finishes: you can watch which operator is stuck and how many rows it has produced against its estimate.

You can also pull a cached or running plan without touching the application at all, which matters when you cannot reproduce the problem locally:

-- Cached plans matching your report
SELECT TOP (10)
       qs.execution_count,
       qs.total_elapsed_time / qs.execution_count / 1000 AS avg_ms,
       t.text,
       p.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)    AS t
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
WHERE t.text LIKE '%FactSalesReport%'     -- placeholder: a table or proc in your report
ORDER BY avg_ms DESC;

Click the query_plan cell and SSMS opens the graphical plan.

Reading right to left, top to bottom

Data flows right to left. The rightmost operators touch the tables; each operator consumes rows from its right and passes results to its left; the leftmost (SELECT) hands the final rows to the client.

Reading order matters practically: start at the right, find the operator producing an unreasonably large number of rows, and follow it left to see what the query then has to do with them. Almost every slow plan has one place where the row count explodes and everything after it is expensive because of that.

The arrows between operators are as informative as the operators. Arrow thickness represents rows flowing. A thick arrow feeding a Sort or a Hash Match means a lot of rows are being sorted or hashed. Hover any arrow for actual rows, estimated rows, and data size.

Cost percentages are estimates, and routinely wrong

Every operator shows "Cost: 43%". These numbers are always the optimizer's estimate, even on an actual plan. They are derived from the same cardinality estimates that may be the reason the plan is bad in the first place. A plan where a scalar UDF or a badly estimated seek shows "0%" while genuinely consuming 90% of the runtime is completely normal.

So: use cost percentages to orient yourself, never as proof. The trustworthy evidence in a plan is actual row counts, actual executions, and warnings. When you want real per-operator time, SQL Server 2016 SP1 and later record it — hover an operator and read Actual Elapsed Time and Actual CPU Time, or read them from the plan XML.

Reading the XML directly

The graphical plan is easier, but the XML is searchable and pasteable into a ticket. Useful patterns to grep for: <Warnings, ConvertIssue, MissingIndex, <Spill, UnmatchedIndexes.

-- Find cached plans containing implicit-conversion warnings anywhere on the instance
SELECT TOP (25)
       DB_NAME(p.dbid) AS database_name,
       t.text,
       p.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS p
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)   AS t
WHERE CAST(p.query_plan AS nvarchar(max)) LIKE '%ConvertIssue%';

That query is a slow full sweep of the plan cache — run it on a quiet moment, not at peak. It is worth it: it finds the .NET parameter-type problem from Module 4 across your whole application at once.

Saving plans

Right-click a plan → Save Execution Plan As → .sqlplan. Save the before and the after. They are small, they open on any machine with SSMS, and they are the most persuasive artifact you can attach to a pull request or share with a colleague.

The short version

Read plans right to left, looking for where the row count explodes, and trust actual rows and warnings over the cost percentages — those are estimates and they are often wrong, including on an actual plan. Save the before and after .sqlplan files so the change is documented.