# SQL Server Interview Questions: Reading an Execution Plan Under Pressure

CareerCTO · 2026-08-07 · 14 min read · Interviews

Most SQL Server interviews have a moment like this. The interviewer pastes a
slow query, opens the execution plan, and goes quiet. You are expected to
talk through what you see, not recite definitions.

Panels do not usually score you on remembering the syntax for a windowing
function. They score you on whether you can look at a plan, point at the
expensive operator, and explain why it is expensive. Everything else -
indexes, joins, statistics, isolation levels - is background knowledge that
feeds into that one moment.

This guide is built around that spine. Each section adds one more piece you
need to read a plan competently, in the order an interviewer is likely to
build up the conversation. If you are also preparing for adjacent rounds, our
[C# interview questions for experienced developers](/blog/c-sharp-interview-questions-for-experienced)
and [ASP.NET Core interview questions](/blog/asp-net-core-interview-questions)
cover the layers that usually sit on top of this database work.

## Why "read the plan out loud" is the real test

An execution plan is SQL Server's explanation of how it will run your query -
which tables it touches first, which indexes it uses, and how it joins rows
together. You can see it before running the query (estimated plan) or after
(actual plan, with real row counts).

Interviewers use it because it exposes bluffing fast. Someone who has
memorized "add an index" cannot improvise when the actual plan shows the
index is there but unused. Someone who has actually debugged slow queries
can look at the same plan and start asking the right questions.

The skill has three layers, and interviews tend to test them in order:

1. Can you name what an operator does (scan, seek, lookup, join type).
2. Can you say which operator is expensive and why, using cost percentages
   and row counts.
3. Can you propose a fix and explain the trade-off it creates.

Most candidates get stuck at layer one. Getting comfortable at layer three is
what separates a mid-level answer from a senior one.

![Line drawing of a magnifying glass hovering over a branching query execution plan diagram](/blog/sql-server-interview-questions-1.webp)

## Scan versus seek: the first thing to name

A **table scan** or **index scan** reads every row in the table or index. A
**seek** uses the index's sorted structure to jump straight to matching rows.
Scans are not automatically bad - on a small table, or when a query needs
most of the rows anyway, a scan can be cheaper than a seek.

The interview question that trips people up: "Is a scan always a problem?"
The honest answer is no. A scan against a 2,000-row lookup table is fine. A
scan against a 20-million-row transaction table, on a query that only needs
50 rows, is the smell you are looking for.

What you should be able to say, concretely, when you see a scan:

- What is the table's row count, roughly, from the plan's actual rows.
- Does the `WHERE` clause have a column that could be indexed and is not.
- Is the predicate **sargable** - written so SQL Server can use an index on
  it directly, instead of computing something on every row first.

A predicate like `WHERE YEAR(OrderDate) = 2024` is not sargable, because
SQL Server has to evaluate `YEAR()` on every row before it can compare.
Rewriting it as `WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'`
lets an index on `OrderDate` be used directly. This single rewrite is one of
the most common "show me you understand indexes" questions.

## Indexes: what they are and what they are not

A **clustered index** determines the physical order rows are stored in on
disk. A table has at most one. A **nonclustered index** is a separate,
smaller structure that points back to the clustered index (or the heap, if
there is no clustered index).

| Index type | Stores | Typical use |
|---|---|---|
| Clustered | The actual table rows, in key order | Primary key, range queries |
| Nonclustered | Key columns plus a pointer back to the row | Filtering, sorting on other columns |
| Covering (nonclustered with `INCLUDE`) | Key columns plus extra included columns | Avoids a lookup back to the table |

The follow-up question that separates candidates: "What happens when a
nonclustered index does not have every column the query needs?" The answer
is a **key lookup** (sometimes shown as RID lookup on a heap) - SQL Server
finds the row via the index, then goes back to the clustered index to fetch
the remaining columns.

On a query returning many rows, thousands of tiny lookups can cost more than
the original scan would have.

The fix is a **covering index** - adding the missing columns to the index's
`INCLUDE` list so the lookup is never needed. Interviewers like this question
because it tests two things at once: do you understand what an index stores,
and do you understand that adding more indexes is not free.

Every index speeds up reads on the columns it covers and slows down every
insert, update and delete that touches those columns. If you are building
your understanding of the wider stack around this, the
[dotnet full-stack developer roadmap](/blog/dotnet-full-stack-developer-roadmap)
covers where database tuning fits against the rest of the job.

## Joins: nested loops, hash match, merge join

SQL Server picks one of three physical join operators, and the choice tells
you a lot about the data:

- **Nested loops**: for each row on one side, scan or seek the other side.
  Cheap when one side is small.
- **Hash match**: build an in-memory hash table from one side, probe it with
  the other. Used when both sides are large and unsorted.
- **Merge join**: walk both sides in sorted order at the same time. Used when
  both inputs are already sorted on the join key, usually because an index
  provides that order.

A common interview prompt: "The plan shows a hash match joining two tables
that both have indexes on the join column. Why didn't SQL Server use a merge
join?" A reasonable answer covers a few possibilities: the indexes are not
sorted on the same key order, or one side needed a sort operator that made a
hash cheaper overall.

Another possibility: the optimizer's cost estimate simply favored a hash
match given the row counts it expected.

That last phrase - "given the row counts it expected" - is the bridge to the
next topic, and it is usually where an interviewer pushes next.

![Line drawing of two database tables connected by a highlighted join line with a lock icon nearby](/blog/sql-server-interview-questions-2.webp)

## Statistics: why the optimizer's row-count guess matters

SQL Server does not know how many rows a query will return before running
it. It estimates, using **statistics** - a summary of the distribution of
values in a column, stored separately from the data itself.

When statistics are stale (because a lot of rows changed since they were
last updated) or the estimate is simply wrong for a skewed distribution, the
optimizer can pick a join type or memory grant that is wrong for the actual
data.

This shows up in an actual execution plan as a gap between **estimated
rows** and **actual rows** on an operator - sometimes by a factor of 100 or
more on a badly estimated query.

How to talk about this in an interview:

- Point to the operator where estimated and actual rows diverge most.
- Explain that a bad estimate upstream compounds: a join that expects 10 rows
  but gets 10,000 will pick nested loops when a hash match would have been
  cheaper.
- Name the fix: `UPDATE STATISTICS`, or checking whether auto-update
  statistics is enabled and firing often enough for a fast-changing table.

You do not need to recite the sampling algorithm SQL Server uses internally.
You need to show you know statistics exist, that they drive every decision
in the plan, and that stale statistics are a common, boring, real-world cause
of a query that "used to be fast."

## Reading cost percentages without being misled

Every operator in a plan shows an estimated cost as a percentage of the
total query. New candidates treat this number as gospel. It is a useful
pointer, not a verdict, for two reasons.

First, it is based on the optimizer's own estimates, which can be wrong for
the same statistics reasons above. Second, cost percentage does not always
match actual elapsed time - an operator with a small cost percentage can
still be the slowest part of the query if it is waiting on locks or disk.

A stronger answer than "the biggest percentage is the problem" is: "I would
look at the highest-cost operator first, then check its actual row count
against its estimate, and cross-check with `SET STATISTICS TIME ON` and
`SET STATISTICS IO ON` to see real duration and real page reads, because the
plan's percentages are estimates, not measurements."

```sql
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT o.OrderId, o.OrderDate, c.CustomerName
FROM Orders o
JOIN Customers c ON c.CustomerId = o.CustomerId
WHERE o.OrderDate >= '2024-01-01' AND o.OrderDate < '2025-01-01';
```

`STATISTICS IO` reports logical reads per table, which tells you how much
data actually had to be touched. A query that reads far more pages than its
row count would suggest is usually missing an index or hitting a scan it
should not be.

## Parameter sniffing: when a cached plan stops fitting the data

SQL Server caches a query plan the first time a parameterized query runs, and
reuses that same plan for later calls with different parameter values. This
is usually a good thing - compiling a plan is not free, and reuse saves work.

**Parameter sniffing** is what happens when the first call has an unusual
parameter value, the optimizer builds a plan shaped around that value, and
every later call with a typical value gets the wrong plan. A stored procedure
that runs in milliseconds for most customers and takes ten seconds for one
specific customer ID is the classic symptom.

This is a favorite interview question because the fix depends on diagnosis,
not memorization. A candidate who jumps straight to `OPTION (RECOMPILE)`
without explaining the trade-off - recompiling on every execution costs CPU -
sounds like they read a blog post rather than debugged the problem. A
stronger answer walks through the options in order:

- `OPTION (RECOMPILE)` if the query runs rarely enough that recompiling every
  time is cheap relative to running with a bad plan.
- `OPTION (OPTIMIZE FOR UNKNOWN)` if you want the optimizer to use average
  statistics instead of sniffing the actual parameter, trading precision for
  consistency.
- Splitting the procedure into variants for genuinely different data shapes,
  if the underlying data really does need two different plans.

The interview signal here is the same one from earlier sections: naming the
mechanism and its cost, not just the keyword.

## Isolation levels: the question that separates concurrency understanding

Interviewers often pivot from plans to concurrency, because both come from
the same instinct: understanding what SQL Server is actually doing, not just
what the syntax looks like. The isolation level controls what a transaction
is allowed to see while other transactions are running.

| Isolation level | Dirty reads | Typical trade-off |
|---|---|---|
| Read Uncommitted | Allowed | Fastest, can read uncommitted (wrong) data |
| Read Committed (default) | Blocked | Standard behavior for most OLTP workloads |
| Repeatable Read | Blocked | Holds read locks longer, more blocking |
| Serializable | Blocked | Strongest guarantee, most blocking |
| Read Committed Snapshot / Snapshot | Blocked | Uses row versioning instead of locks |

The question worth preparing for: "Why would you turn on Read Committed
Snapshot Isolation?" The honest answer is that it reduces reader-writer
blocking by giving readers a versioned copy of the row instead of making
them wait for a lock.

The cost is extra work in tempdb and the possibility of update conflicts
under snapshot isolation specifically. This is a genuine trade-off, not a
free performance switch, and saying that out loud is usually what an
interviewer is listening for.

## Deadlocks: the other concurrency question

Once isolation levels come up, a deadlock question usually follows. A
**deadlock** happens when two transactions each hold a lock the other one
needs, and neither can proceed. SQL Server detects this automatically and
kills one transaction (the "deadlock victim") to let the other continue.

The interview question is rarely "what is a deadlock" - it is "how would you
find out why one happened." The honest answer involves the deadlock graph,
either captured through Extended Events or the system health session, which
shows exactly which two queries, which resources, and which lock modes were
involved.

A useful pattern to mention: many deadlocks come from two queries touching
the same tables in a different order. If procedure A updates `Orders` then
`Customers`, and procedure B updates `Customers` then `Orders`, running them
concurrently is a deadlock waiting to happen. The fix is usually consistent
access order across the codebase, not a magic isolation level setting.

## A worked example: walking through one plan end to end

Put the pieces together the way an interview actually runs. Say the query
is a report joining orders to customers, filtered by date, and it used to
run in under a second but now takes several seconds.

A structured answer sounds like this:

1. Open the actual plan and find the operator with the highest cost
   percentage - say it is a clustered index scan on `Orders`.
2. Check whether the `WHERE` clause on `OrderDate` is sargable, and whether
   an index on `OrderDate` exists at all.
3. Compare estimated versus actual row counts on that scan. A large gap
   points at stale statistics rather than a missing index.
4. Look at the join operator. If it is a hash match where a merge join would
   make sense, check whether both sides are sorted on the join key.
5. Propose one change at a time - update statistics first, since it is
   cheap and reversible, then consider a new or adjusted index - and say
   you would re-run the plan after each change rather than guessing at all
   of them at once.

That last step matters. Interviewers notice candidates who propose five
changes at once versus candidates who change one thing, measure, and decide
whether to continue.

## Questions to expect and how to structure your answer

- "What is the difference between a clustered and nonclustered index?"
  Answer with what each stores, not just a definition.
- "When would a table scan be the right choice?" Answer with a concrete
  scenario, such as a small table or a query needing most of its rows.
- "What is a key lookup and when does it become a problem?" Answer with the
  covering-index fix and its write-side cost.
- "How would you find out if statistics are stale?" Answer by naming
  `sys.dm_db_stats_properties` or simply comparing estimated to actual rows
  in a plan.
- "What isolation level would you pick for a reporting query that must not
  block writers?" Answer with Read Committed Snapshot Isolation and its
  tempdb trade-off, not just the name.

Notice the pattern: every strong answer names a mechanism and a trade-off.
Every weak answer stops at the vocabulary word.

![Line drawing of a bar chart of row counts next to a small gauge labeled statistics](/blog/sql-server-interview-questions-3.webp)

## Where this fits with the rest of the interview loop

A SQL Server round rarely stands alone. On most .NET teams it sits alongside
questions on the application layer, and interviewers often move straight
from a query plan into how that query gets called from code - connection
handling, async patterns, or how a repository layer builds the SQL in the
first place.

Our [C# interview questions for experienced developers](/blog/c-sharp-interview-questions-for-experienced)
guide covers the layer just above this one, and the
[Angular interview questions](/blog/angular-interview-questions) guide is
useful if the role also touches the front end that eventually renders this
data.

If you are early in your career and the SQL round feels like the hardest
part of every interview, that is normal - it is one of the few topics where
you cannot fake experience, because the interviewer is watching you reason
in real time rather than checking a memorized answer.

## Practicing this without a live interviewer

You do not need a mock interview to build this skill. Take a query you
actually run at work or on a side project, turn on the actual execution
plan, and narrate it to yourself the way you would to an interviewer -
out loud, not in your head. Saying it forces you to notice the gaps in your
own reasoning before someone else does.

Do this with three or four different queries - one with a missing index, one
with a bad join, one with stale statistics if you can force it - and you
will have real examples to draw on instead of memorized theory.

Real examples are also what a reviewed job listing on CareerCTO expects to
hear when a hiring manager asks you to walk through past work, rather than
recite definitions.

## What to do next

Pick one slow query you have access to right now, open its actual execution
plan, and write down, in your own words, what the most expensive operator is
doing and why. If you can also explain what you would change and what that
change would cost you, you already have a stronger answer than most
candidates give in the room.

When you are ready to put this in front of real hiring managers, browsing
[reviewed job openings](/jobs) on CareerCTO shows you the kind of roles that
ask these questions, and a [verified profile](/build-a-profile) lets you lead
with proof of your training background instead of asking an interviewer to
take your word for it.

Companies posting roles that need this kind of database depth can
[post a reviewed job](/post-a-job) or [browse verified developer profiles](/graduates)
directly - and if you want to understand exactly what a CareerCTO badge does
and does not claim, our [about page](/about) spells it out plainly.

---

Source: https://careercto.dev/blog/sql-server-interview-questions