Execution Plans and Query Tuning – SQL Server | Lesson 27

Published / updated4 Oct 2026

Module 5 · Lesson 27Advanced45 minInterview:FMSA

Before this lesson: Indexes: Clustered, Nonclustered and Covering – SQL Server

Introduction

In Lesson 26, an index made a query 88 times cheaper. But an index only helps if the query lets SQL Server use it. Small differences in how a query is written can hide a column from every index.

An execution plan is SQL Server's step-by-step recipe for running a query. In this lesson, you read plans, find three classic problems, and fix each one with a measured change.

By the end of this lesson, you will be able to:

• Show the actual execution plan in SSMS and read it from right to left.
• Recognize the main operators: seek, scan, key lookup, nested loops, and aggregate.
• Rewrite non-SARGable conditions so they can use an index.
• Spot implicit conversions caused by mixed data types.
• Explain when SQL Server prefers a scan over many key lookups, and fix it with a covering index.
• Follow a repeatable tuning method.

Why this matters

Queries that look correct can still be slow, and slow queries cause the locking problems from Lesson 25. Guessing at fixes, such as adding random indexes or NOLOCK, usually makes things worse.

The plan tells you exactly where the time goes. Reading plans is the skill that separates "I think the database is slow" from "this filter scans 600,000 rows because of YEAR()".

The concept

Example: estimated and actual plans

Plan	How to get it in SSMS	Runs the query?	Shows
Estimated plan	Ctrl+L	No	What SQL Server expects to do
Actual plan	Ctrl+M, then run	Yes	What it did, with real row counts

Read a graphical plan from right to left: data starts at the right, in seeks and scans, and flows left through joins and aggregates to the result. Thicker arrows mean more rows.

Think of a recipe card. The ingredients on the right are gathered first, then mixed, and the finished dish appears on the left.

Example: the operators you will meet most

Operator	Meaning	Good or bad?
Index Seek	Jumps to matching rows in a sorted index	Usually good
Index Scan / Clustered Index Scan	Reads a whole index or table	Fine for small tables or large results, a warning on big tables
Key Lookup	Fetches missing columns for each row found	Fine for a few rows, expensive for many
Nested Loops	For each row from one input, looks up rows in the other	Good for small inputs
Hash Match	Builds a hash table to join or group	Good for large, unsorted inputs
Stream Aggregate	Counts or sums rows arriving in order	Cheap

Example: SARGable conditions

A condition is SARGable (Search ARGument-able) when SQL Server can use an index to seek on it. The rule: leave the column alone on one side of the comparison.

Not SARGable	SARGable
YEAR(AttendanceDate) = 2026	AttendanceDate >= '2026-01-01' AND AttendanceDate < '2027-01-01'
LEFT(LastName, 2) = N'Sh'	LastName LIKE N'Sh%'
MarksObtained + 5 > 40	MarksObtained > 35

Apply it to the SMS

Press Ctrl+M in SSMS to include the actual plan, then run this script. It needs the indexes from Lesson 26.

Show queries/027-tuning.sql

queries/027-tuning.sql

-- Lesson 27: read execution plans and fix three common problems.
-- In SSMS, press Ctrl+M (Include Actual Execution Plan) before running.
-- Compare the logical reads in the Messages tab for each pair of queries.
-- Needs the indexes from database/026-indexes.sql.
USE SchoolDb;
GO

SET STATISTICS IO ON;

-- 1a. A function wrapped around the column: SQL Server must check every row (scan).
SELECT COUNT(*) AS RecordsOn1July
FROM dbo.AttendanceRecords
WHERE YEAR(AttendanceDate) = 2026
  AND MONTH(AttendanceDate) = 7
  AND DAY(AttendanceDate) = 1;

-- 1b. The same question with the bare column: the index can jump to the date (seek).
SELECT COUNT(*) AS RecordsOn1July
FROM dbo.AttendanceRecords
WHERE AttendanceDate = '2026-07-01';

-- 2a. Comparing a VARCHAR column with NVARCHAR text: the column is converted on every row.
SELECT Id, FirstName
FROM dbo.Students
WHERE AdmissionNumber = N'ADM-2017-0001';

-- 2b. Matching types: a direct seek on the unique index.
SELECT Id, FirstName
FROM dbo.Students
WHERE AdmissionNumber = 'ADM-2017-0001';

-- 3a. The index finds the rows, but DateOfBirth is not in it: one extra lookup per row.
SELECT FirstName, LastName, DateOfBirth
FROM dbo.Students
WHERE LastName = N'Sharma';

SET STATISTICS IO OFF;
GO

-- 3b. A covering index stores DateOfBirth too, so no lookups are needed.
--     Created and dropped inside a transaction, so the database is left as it was.
BEGIN TRANSACTION;

CREATE INDEX IX_Students_LastName_Covering
    ON dbo.Students (LastName)
    INCLUDE (FirstName, DateOfBirth);

SET STATISTICS IO ON;

SELECT FirstName, LastName, DateOfBirth
FROM dbo.Students
WHERE LastName = N'Sharma';

SET STATISTICS IO OFF;

ROLLBACK TRANSACTION;
GO

Example: problem 1, a function on the column

Both queries return 10012: the whole school's records for 1 July.

Query	Plan operator	Logical reads
YEAR(...) = 2026 AND MONTH(...) = 7 AND DAY(...) = 1	Index Scan, then a filter on every row	1,567
AttendanceDate = '2026-07-01'	Index Seek on IX_AttendanceRecords_AttendanceDate	38

The plan of the first query says it plainly:

Output

|--Index Scan(OBJECT:([SchoolDb].[dbo].[AttendanceRecords].[UQ_AttendanceRecords_EnrollmentId_AttendanceDate]),
   WHERE:(datepart(year,[SchoolDb].[dbo].[AttendanceRecords].[AttendanceDate])=(2026)
      AND datepart(month,[SchoolDb].[dbo].[AttendanceRecords].[AttendanceDate])=(7)
      AND datepart(day,[SchoolDb].[dbo].[AttendanceRecords].[AttendanceDate])=(1)))

SQL Server cannot seek on datepart(year, AttendanceDate), because the index is sorted by the date, not by its year. It scans the narrowest index that contains the date and checks every row. The fix is to compare the bare column.

Example: problem 2, a hidden conversion

AdmissionNumber is VARCHAR(15). Searching it with N'...', an NVARCHAR value, makes SQL Server convert the column on every row:

Query	Plan operator	Logical reads
AdmissionNumber = N'ADM-2017-0001'	Index Scan with CONVERT_IMPLICIT	39
AdmissionNumber = 'ADM-2017-0001'	Index Seek on UQ_Students_AdmissionNumber	4

Output

|--Index Scan(OBJECT:([SchoolDb].[dbo].[Students].[UQ_Students_AdmissionNumber]),
   WHERE:(CONVERT_IMPLICIT(nvarchar(15),[SchoolDb].[dbo].[Students].[AdmissionNumber],0)=N'ADM-2017-0001'))

This happens most often from application code that sends every string as NVARCHAR. In Module 9, you will set the parameter type to match the column. In the graphical plan, SSMS marks the operator with a yellow warning triangle.

Example: problem 3, too many key lookups

IX_Students_LastName_FirstName can find every Sharma quickly, but it does not hold DateOfBirth. For a few rows, SQL Server would seek and then do one key lookup per row. There are 668 Sharmas among 10,012 students, so SQL Server decided that 668 lookups cost more than reading the whole table:

Query	Plan operator	Logical reads
Sharmas with date of birth, index without DateOfBirth	Clustered Index Scan	126
Same query, with a covering index INCLUDE (FirstName, DateOfBirth)	Index Seek	6

This is the tipping point: past a certain number of rows, a scan beats seek plus lookups. The fix is to make the index cover the query. The demo creates the covering index inside a transaction and rolls it back, so your database keeps Lesson 26's indexes only.

Example: a tuning method

Use the same steps every time:

1. Measure: SET STATISTICS IO ON, and note logical reads and duration.
2. Look: open the actual plan; find the operator with the highest cost and the thickest arrow.
3. Explain: scan on a big table? lookups on many rows? a warning triangle?
4. Change one thing: rewrite the condition, fix a type, or add or adjust an index.
5. Measure again: same query, same data. Keep the change only if it helped.

Example: parameter sniffing

SQL Server reuses a plan for a stored procedure, built for the first parameter values it saw. If usp_GetClassList is first called for a tiny section, its plan suits tiny sections.

A later call for a huge section reuses it, and can be slow. This is called parameter sniffing.

It is normal and usually helpful. When it hurts, the usual fixes are better indexes, or for a specific query, the hint OPTION (RECOMPILE) so the plan is built fresh each time.

Common mistakes

1. Functions on indexed columns in WHERE. Scans instead of seeks. Fix: compare the bare column, using ranges for dates.
2. Mixing VARCHAR columns with NVARCHAR values. Hidden conversions. Fix: match parameter types to column types.
3. Judging by duration alone. Cached data makes the second run look fast. Fix: compare logical reads.
4. Changing several things at once. You cannot tell which change helped. Fix: one change, then measure.
5. Trusting estimated plans for row counts. Fix: use the actual plan when you can run the query.

In real projects

From 16 years of .NET projects

The single most common slow query I find in reviews is a date filter wrapped in a function: WHERE CONVERT(DATE, CreatedAt) = @Today or YEAR(PaidOn) = 2026. The rewrite to a range takes one minute and often cuts a report from minutes to seconds.

The second is the hidden conversion from application code. One team's student search was slow only from the website, never in SSMS: the website sent NVARCHAR, while SSMS tests used plain text.

The plan's yellow triangle explained in seconds what the team had argued about for a week.

Best practices

• Read the actual plan for every slow query before changing anything.
• Keep columns bare in WHERE; move calculations to the other side.
• Match the data types of parameters and columns.
• Cover frequent queries that return many rows, to avoid key lookups.
• Change one thing at a time, and compare logical reads.

Recap

• An execution plan is SQL Server's recipe; read it right to left.
• Seeks are targeted; scans read everything; key lookups cost one read per row.
• Functions on columns make conditions non-SARGable: 1,567 reads instead of 38.
• VARCHAR against NVARCHAR causes CONVERT_IMPLICIT: 39 reads instead of 4.
• Past the tipping point, SQL Server scans instead of looking up; covering indexes fix it.
• Tune by measuring, changing one thing, and measuring again.

Homework

Library module task: Rewrite this query so that it could use an index on IssuedOn: WHERE YEAR(IssuedOn) = 2026 AND MONTH(IssuedOn) = 7. Both versions must return the same rows.

A correct result looks like: a date range from 1 July 2026, included, up to 1 August 2026, excluded, returning the same two issues.

Show the solution

library-assignment/027-library-tuning.sql

-- Library homework, Lesson 27: the same question, written two ways.
USE SchoolDb;
GO

-- Before: a function on the column hides it from any index on IssuedOn.
SELECT Id, BookCopyId, StudentId
FROM dbo.BookIssues
WHERE YEAR(IssuedOn) = 2026
  AND MONTH(IssuedOn) = 7;

-- After: a date range on the bare column, so an index can seek.
SELECT Id, BookCopyId, StudentId
FROM dbo.BookIssues
WHERE IssuedOn >= '2026-07-01'
  AND IssuedOn < '2026-08-01';
GO

Using < '2026-08-01' instead of <= '2026-07-31' stays correct even if the column later becomes a date with a time.

Quiz

1. In which direction do you read a graphical execution plan?

   1. Left to right
   2. Right to left
   3. Top to bottom only
   4. Bottom to top only
   Show answer

   Answer: B. Right to left Data starts in the operators on the right and flows left to the result.

2. Why does WHERE YEAR(AttendanceDate) = 2026 cause a scan?

   1. YEAR is not a real function
   2. The index is sorted by date, not by the result of YEAR()
   3. Dates cannot be indexed
   4. Scans are always faster
   Show answer

   Answer: B. The index is sorted by date, not by the result of YEAR() A function on the column hides it from the index; use a date range.

3. What does CONVERT_IMPLICIT in a plan usually mean?

   1. A data type mismatch between a column and a value
   2. A missing index
   3. A deadlock
   4. A parallel plan
   Show answer

   Answer: A. A data type mismatch between a column and a value SQL Server converts one side to match the other, often blocking seeks.

4. Why did SQL Server scan Students for the 668 Sharmas instead of seeking?

   1. The index was broken
   2. 668 key lookups would cost more than reading the table
   3. Scans are required for LIKE
   4. Statistics were missing
   Show answer

   Answer: B. 668 key lookups would cost more than reading the table Past the tipping point, a scan is cheaper; a covering index removes the lookups.

5. Which measurement is most reliable for comparing two versions of a query?

   1. Duration on the second run
   2. Logical reads
   3. Number of joins
   4. Length of the query text
   Show answer

   Answer: B. Logical reads Logical reads count the work regardless of caching.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)

F What is an execution plan? concept

The step-by-step recipe SQL Server chooses to run a query: which indexes to read, in what order, and how to join the results. You read it in SSMS to see why a query is slow.

See the example · Permalink

F What is the difference between an estimated and an actual execution plan? concept

The estimated plan is built without running the query and shows guessed row counts. The actual plan is captured while the query runs and adds real row counts. Use the actual plan when you can, because big gaps between estimated and actual rows point to problems.

See the example · Permalink

F What does SET STATISTICS IO ON show, and why is it useful? concept

It reports the logical reads (8 KB pages read) for each table a query touches. Logical reads do not change with caching, so they are a fairer before-and-after measure than duration.

See the example · Permalink

Mid (3-5 yrs)

M What does SARGable mean? Give an example of a non-SARGable condition. concept

A SARGable condition lets SQL Server seek in an index. YEAR(AttendanceDate) = 2026 is not SARGable because the function hides the column; in the SMS it read 1,567 pages, while AttendanceDate = '2026-07-01' read 38. Compare the bare column, using a date range if needed.

See the example · Permalink

M What is an implicit conversion, and how does it hurt performance? concept

When a value's type differs from the column's, SQL Server converts one of them. If it must convert the column, as with a VARCHAR column compared to an NVARCHAR value, it cannot seek. In the SMS, N'ADM-2017-0001' caused a scan of 39 reads; 'ADM-2017-0001' was a 4-read seek.

See the example · Permalink

M What is a key lookup, and why might SQL Server ignore your index? concept

A key lookup fetches columns missing from a nonclustered index, one row at a time. For many rows, SQL Server decides a full scan is cheaper: 668 matching Sharmas led to a scan of 126 reads. A covering index with INCLUDE turned it into a 6-read seek.

See the example · Permalink

Senior (6-9 yrs)

S A query ran in 1 second yesterday and takes 40 seconds today. Nothing was deployed. What do you check? scenario

Check for blocking first, then compare today's plan with the old one, using Query Store if it is on. Common causes are parameter sniffing (a plan built for unusual values), outdated statistics after a big data load, and data growth past a tipping point.

How to answer: Give a calm, ordered checklist: blocking, plan change, statistics, data volume. Explain how you would prove the cause, then the fix, such as updating statistics, forcing a good plan, or adjusting an index. Mention measuring after the fix.

See the example · Permalink

S What is parameter sniffing, and how do you deal with it? concept

SQL Server builds a procedure's plan for the first parameter values it sees and reuses it. That plan can be poor for very different values, such as a tiny section versus a huge one. Fixes include better indexes, OPTION (RECOMPILE) for a specific query, or forcing a good plan with Query Store.

How to answer: Explain that sniffing is normally helpful, then show when it hurts with the SMS class list example. Compare fixes by cost: RECOMPILE costs CPU per call, while a better index helps every value.

See the example · Permalink

Architect / Lead (10+ yrs)

A How would you set up ongoing query performance monitoring for a production database? design

Turn on Query Store to keep plan and runtime history, and track the top queries by total CPU and reads. Alert on regressions, blocking, and deadlocks, and review the top queries regularly, tuning with the measure, change, measure method.

How to answer: Describe the tools (Query Store, DMVs, alerts), the routine (weekly top-N review), and the ownership (who acts on alerts). Link it to business outcomes, such as the attendance peak at the start of the school day.

See the example · Permalink

Resources

• Code for this lesson: git tag lesson-27-execution-plans (code repository coming soon)
• Files: queries/027-tuning.sql, library-assignment/027-library-tuning.sql
• Video: coming soon

What's next

The database is fast and correct. Next, you make it safe: who may do what, and how to recover from disaster. Lesson 28: Security and backup

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.