Execution Plans: Scan vs Seek

You wrote a query and it's slow. Why? Guessing is useless — the database will tell you exactly what it did if you look at the execution plan. Every performance tip in this chapter should be checked against the plan: never guess, measure.

Definition

An execution plan is the step-by-step strategy the database's query optimizer chooses to run a query — which tables and indexes it reads, in what order, how it joins them and how it sorts or aggregates — together with the estimated (or actual) cost of each step.

• Query optimizer — the part of the engine that considers many possible strategies and picks the cheapest one, using statistics about your data.
• Estimated plan — what the optimizer plans to do (query not run).
• Actual plan — what really happened, including the real row counts (query is run).

Table Scan vs Index Scan vs Index Seek

Each step down this list is roughly 10–100× less work. Your goal for selective queries: SEEK > SCAN.

Note: A scan is not always bad. If a query needs most of the table (e.g. SUM(runs) of every row), a scan is the cheapest plan. Scans are a problem when you want a few rows and still read everything.

How to see a plan

-- SQL Server: measure pages read and time
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT *
FROM players
WHERE name = 'Hardik Pandya';
-- Messages tab: Table 'players'. Scan count 1, logical reads 1250 ...

Common SQL Server plan operators

Example — the same query before and after an index (playground)

EXPLAIN QUERY PLAN
SELECT *
FROM players
WHERE name = 'Virat Kohli';

CREATE INDEX IX_players_name ON players (name);

EXPLAIN QUERY PLAN
SELECT *
FROM players
WHERE name = 'Virat Kohli';

In SQLite words: SCAN = read every row, SEARCH = index seek, jump to the row. In a 50,000-row table this is the difference between reading the whole table and reading a handful of pages.

Example — a plan reveals a hidden sort

EXPLAIN QUERY PLAN
SELECT DISTINCT team
FROM players
ORDER BY team;

The second line is an extra sorting/de-duplication step that only exists because of DISTINCT. Plans like this are how you discover that a "harmless" keyword costs work (performance tip #2).

Example — a join plan

EXPLAIN QUERY PLAN
SELECT t.team_name, p.name
FROM players AS p
INNER JOIN teams AS t
    ON p.team = t.team;

How it works: this is a nested loop: scan every player, and for each one seek the matching team through the primary-key index on teams.team. That's why indexing join columns matters (tip #10).

The plan-driven workflow

1. Write the query.
2. Read the plan — scan or seek? lookups? sorts? nested loops over big inputs?
3. Apply a tip — add an index, rewrite, filter earlier.
4. Compare plans (and logical reads / time) — is it really faster?

Common mistakes

• ❌ Judging speed by one run on your laptop. ✅ Compare plans and logical reads; timings vary with cache.
• ❌ Panicking at every scan. ✅ Scans are right when you need most rows.
• ❌ Reading the SSMS graphical plan left to right. ✅ Data flows right to left; the leftmost operator returns the result.

Interview tip

Q: What is the difference between an index scan and an index seek? A: An index seek uses the B-Tree to jump directly to the matching key range, reading only a few pages. An index scan reads every leaf page of the index from start to end. Seeks are what you want for selective filters; a scan usually means there's no usable index, the filter isn't sargable, or the query needs most of the rows.