How to View Execution Plans in SQL Server

How do I view an execution plan in SQL Server?

Execution plans reveal how SQL Server will or did execute a query so you can troubleshoot and optimize performance.

Description

What is an execution plan in SQL Server?

An execution plan is a graphical or textual description of the steps SQL Server takes to retrieve or modify data. It exposes operators, index usage, joins, and estimated vs. actual costs, helping you pinpoint bottlenecks quickly.

How do I generate an estimated execution plan?

Turn on the estimated plan without running the query by choosing Query → Display Estimated Execution Plan (SSMS) or by running SET SHOWPLAN_XML ON;. SQL Server returns XML that SSMS renders visually.

How do I get the actual execution plan?

Include the plan while executing the statement. In SSMS, click Include Actual Execution Plan (Ctrl + M) or run SET STATISTICS XML ON; before your query. SQL Server executes the query and returns the real operator costs and row counts.

How do I read key metrics in the plan?

Focus on operator cost percentage, estimated vs. actual rows, and index seeks vs. scans. High cost percentages or large row count mismatches usually indicate missing indexes or bad statistics.

When should I add an index?

If the plan shows frequent Clustered Index Scan or Table Scan on large tables, consider adding a non-clustered index on the filter or join columns. Verify improvement by rerunning the plan.

Tip: save plans for later review

Right-click inside the plan window and choose Save Execution Plan As…. Store .sqlplan files in version control for performance regression tracking.

Why How to View Execution Plans in SQL Server is important

How to View Execution Plans in SQL Server Example Usage


-- Investigate slow customer order lookup
SET STATISTICS XML ON;
GO
SELECT o.id, o.order_date, o.total_amount
FROM Orders o
JOIN Customers c ON c.id = o.customer_id
WHERE c.email = 'jane.doe@example.com';
GO
SET STATISTICS XML OFF;
-- Review the returned plan for scans and missing index hints

How to View Execution Plans in SQL Server Syntax


-- Turn estimated plan ON (no execution)
SET SHOWPLAN_XML ON;
GO
SELECT *
FROM Orders o
JOIN Customers c ON c.id = o.customer_id
WHERE o.order_date >= '2023-01-01';
GO
SET SHOWPLAN_XML OFF;

-- Turn actual plan ON (executes query)
SET STATISTICS XML ON;
GO
SELECT p.name, SUM(oi.quantity) AS total_units
FROM OrderItems oi
JOIN Products p  ON p.id = oi.product_id
GROUP BY p.name
ORDER BY total_units DESC;
GO
SET STATISTICS XML OFF;

Common Mistakes

Neglecting actual plans: Developers often rely only on estimated plans. Estimated plans ignore runtime parameter sniffing and statistics issues. Always capture the actual plan with SET STATISTICS XML ON to diagnose real-world performance.
Reading cost percentages in isolation: A high operator cost inside a fast query can be misleading. Compare absolute runtime, row counts, and I/O statistics before rewriting queries.

Frequently Asked Questions (FAQs)

Does capturing a plan slow my query?

Collecting estimated plans adds no runtime cost. Actual plans add minor overhead—usually negligible unless the query processes millions of rows.

Can I get plans without SSMS?

Yes. Use SET SHOWPLAN_XML or SET STATISTICS XML in any client, or query sys.dm_exec_query_stats and sys.dm_exec_query_plan for cached plans.

How do I spot parallelism issues?

Look for Parallelism operators and the Actual Number of Rows vs. Estimated Number of Rows. Excessive parallelism may indicate outdated statistics or skewed data.