Performance Tuning & Execution Plans

Learn how to read a basic execution plan in SSMS, spot a missing index, and measure query cost with SET STATISTICS IO.

Introduction

When a query feels slow, guessing at the fix rarely works - you need to see what SQL Server is actually doing. An execution plan shows the exact steps the query optimizer chose to retrieve your data, and SSMS can display it visually. This lesson covers how to read a basic execution plan, spot the tell-tale signs of a missing index, and use SET STATISTICS IO to measure the real cost of a query in terms of pages read.

What You Will Learn

What an execution plan is and how to view one in SSMS.
The difference between estimated and actual execution plans.
How to distinguish a scan from a seek.
How to spot a missing index warning.
How to read SET STATISTICS IO output.

What Is an Execution Plan?

Before running a query, SQL Server's optimizer evaluates multiple possible strategies for retrieving the requested data - which indexes to use, which join algorithm to apply, in what order to touch each table - and picks the one it estimates will be cheapest. The execution plan is a visual or textual representation of that chosen strategy, and it is the single most useful tool for understanding why a query is slow.

Estimated vs Actual Plans

In SSMS, you can view an Estimated Execution Plan without running the query at all (Ctrl+L), based purely on statistics. An Actual Execution Plan (Ctrl+M, then run the query) executes the query and shows the real plan along with actual row counts, which is more reliable when statistics are out of date.

Plan Type	SSMS Shortcut	Runs the Query?
Estimated	Ctrl+L	No
Actual	Ctrl+M, then execute	Yes

Scans vs Seeks

Two of the most important operators to recognize are Index Seek and Index Scan (or Table Scan on a heap). A seek means SQL Server navigated directly to the rows it needed using an index - fast. A scan means it read through every row of the table or index looking for matches - slow on large tables, and often a sign that a useful index is missing or not being used.

-- Enable Actual Execution Plan in SSMS (Ctrl+M), then run:
SELECT FirstName, LastName, City
FROM dbo.Customers
WHERE City = 'Seattle';

Reading the Percentages

Each operator in a graphical plan shows a percentage representing its estimated relative cost within the overall query. Start by looking at the most expensive operator (often the largest percentage) - that is usually where tuning effort pays off most.

Spotting a Missing Index

When SSMS's plan detects that a different index would have made a query cheaper, it displays a green, dashed 'Missing Index' suggestion above the plan, including the exact CREATE INDEX statement to fix it. This is a genuinely useful starting point, though it should be verified rather than applied blindly - the suggestion does not know about every query that touches the table, only the one you just ran.

-- SSMS might suggest something like this after running a query
-- that filters on City without a supporting index:
CREATE NONCLUSTERED INDEX IX_Customers_City
ON dbo.Customers (City)
INCLUDE (FirstName, LastName);

Do Not Apply Every Suggestion Blindly

SSMS's missing index suggestions are based on a single query's needs. Before adding an index, check whether an existing index already covers most of the same need, and consider whether the table's write volume can absorb the extra index maintenance cost, as discussed in the indexes lesson.

SET STATISTICS IO

SET STATISTICS IO ON reports the number of logical page reads a query performed against each table it touched - a very direct measure of how much work SQL Server did, independent of how fast or slow the server happens to be at any given moment.

SET STATISTICS IO ON;

SELECT FirstName, LastName, City
FROM dbo.Customers
WHERE City = 'Seattle';

SET STATISTICS IO OFF;

Result (Messages tab)

Click Run to see what this code prints.

842 logical reads means SQL Server touched 842 8KB pages to answer this query. After adding IX_Customers_City from the example above, re-running the same query and checking STATISTICS IO again would typically show a dramatically lower logical read count, since an index seek only has to touch the relevant pages instead of the whole table.

A Before-and-After Example

-- Before: no index on City, logical reads = 842 (Table Scan)
-- After creating IX_Customers_City and re-running the same query:
SET STATISTICS IO ON;

SELECT FirstName, LastName, City
FROM dbo.Customers
WHERE City = 'Seattle';

SET STATISTICS IO OFF;

Result (Messages tab, after adding the index)

Click Run to see what this code prints.

Common Mistakes

Avoid These Mistakes

Tuning based on wall-clock time alone, which is affected by server load, caching, and network - logical reads are more consistent.
Blindly applying every missing-index suggestion without checking for overlap with existing indexes.
Only ever looking at estimated plans on a server with stale statistics, missing what actually happens at runtime.
Ignoring the most expensive operator in the plan and tuning something that barely matters.
Forgetting that the first run of a query is often slower due to cold cache, skewing before/after comparisons.

Best Practices

Use Actual Execution Plans when diagnosing a real, currently slow query.
Compare SET STATISTICS IO output before and after a tuning change for an objective before/after measurement.
Focus first on the operator with the highest relative cost in the plan.
Verify missing-index suggestions against existing indexes before creating new ones.
Keep statistics up to date (UPDATE STATISTICS) so the optimizer's row estimates stay accurate.

Frequently Asked Questions

What is the difference between logical reads and physical reads?

Logical reads count every page read from the buffer cache (memory); physical reads count only pages that had to be pulled from disk because they weren't already cached. Logical reads is the more stable, repeatable number to tune against.

Is a Table Scan always bad?

Not always - for a small table, or a query that genuinely needs most of the rows, a scan can be perfectly efficient, sometimes even cheaper than a seek plus many lookups. It becomes a concern mainly on large tables with selective filters.

What tool shows execution plans besides SSMS?

Azure Data Studio and various third-party tools can also display SQL Server execution plans; the concepts (seeks, scans, operator costs) are the same across tools.

Does adding an index guarantee SQL Server will use it?

No - the optimizer decides whether an index is actually cheaper for a given query based on statistics. An index that exists but is never chosen by the optimizer is just overhead with no benefit.

Key Takeaways

An execution plan shows exactly how SQL Server chose to run a query.
Seeks are generally efficient; scans on large tables often signal a missing or unused index.
SSMS's missing-index suggestions are a useful starting point, not a guarantee.
SET STATISTICS IO measures logical page reads, a stable metric independent of server load.
Always verify a tuning change with a real before/after comparison, not assumptions.

Summary

Execution plans and SET STATISTICS IO turn query tuning from guesswork into measurement. Next, you will connect everything you have learned to a real .NET application, seeing how C# code talks to SQL Server in practice.

Next Lesson →