What is an execution plan in SQL Server?
Quick Answer
An execution plan is the set of physical operations the query optimiser chose to run a query — which indexes to use, in what order to join tables, whether to seek or scan, how to sort and aggregate. The estimated plan is the optimiser's intention based on statistics; the actual plan also includes real row counts and runtime stats, so it's what you use to diagnose a slow query.
An execution plan is how the optimiser decided to run my query — which index it used, seek versus scan, the join order and join type, and any sorts or aggregates. I mostly look at the actual plan, because it shows real row counts next to the estimates. The things I check first are: is there a scan on a big table where I expected a seek, is there a pile of key lookups that a covering index would remove, and is there a large mismatch between estimated and actual rows, which usually means the statistics are out of date. The thick arrows show where the row volume is, and that's usually where the problem is.
SQL is declarative — you say what you want, and the cost-based optimiser decides how, using table/index statistics (row counts, data distribution histograms) to estimate the cheapest plan.
Operators to recognise:
- Index Seek — navigates the B-tree to matching rows. Usually good.
- Index Scan / Table Scan — reads the whole structure. Fine for small tables or when most rows are needed; a red flag on a large table with a selective filter.
- Key Lookup / RID Lookup — a non-clustered index seek fetching extra columns from the base table, once per row. Many of these = add a covering index.
- Nested Loops / Merge / Hash Join — join strategies; hash joins on large unsorted inputs, nested loops when one side is tiny.
- Sort — expensive; can indicate a missing index that could provide order.
What to look for: a big gap between estimated and actual rows (stale or missing statistics), fat arrows (many rows flowing), scans where a seek is expected, warnings (implicit conversions, spills to tempdb), and the operators with the highest relative cost.
-- see the plan without running the query
SET SHOWPLAN_XML ON;
GO
SELECT o.OrderId, o.Total, c.Name
FROM dbo.Orders o
JOIN dbo.Customers c ON c.CustomerId = o.CustomerId
WHERE o.Status = 1 AND o.OrderDate >= '2026-01-01';
GO
SET SHOWPLAN_XML OFF;
-- or run it and get the ACTUAL plan + IO/time stats
SET STATISTICS IO, TIME ON;
-- (enable 'Include Actual Execution Plan' in SSMS, then run the query)Practise the whole thread the way an interviewer walks it — each question builds on the last.
- What is an index in SQL Server?
- What is the difference between a clustered and a non-clustered index?
- When should you create an index?
- Can indexes hurt performance?
- What is an execution plan in SQL Server?You are here
- How do you optimise a slow SQL query?