PL/SQL & T-SQL (Oracle and SQL Server) Module 7
Execution plans and performance in SQL Server interview questions
Interview questions on execution plans and performance in SQL Server: warm-ups, then what each harder question tests and how a good answer starts.
Warm-up questions
Short questions with short answers, each answered in full.
-
Fresher to mid
How do estimated and actual execution plans differ?
An estimated plan is what SQL Server compiles before a query runs. It shows the operators and the row counts the optimizer expects from statistics, but the query does not execute, so there are no actual row counts. An actual plan is the same plan after the query runs, with the real row counts added. The gap between estimated and actual rows is the first thing to look for: a large gap means the optimizer's decisions may have been wrong.
From the lesson Reading SQL Server execution plans
-
Fresher to mid
What makes a predicate sargable?
-
Fresher to mid
When does SQL Server sniff a parameter?
While it compiles or recompiles a stored procedure, a query sent through sp_executesql or a prepared query, it reads the current parameter values, estimates the matching rows from the statistics and caches the plan, which later calls reuse until it is evicted or recompiled. That helps when the values look alike and hurts when one plan cannot suit both a small and a large value.
From the lesson Parameter sniffing, PSP and Query Store
Questions with model answers
What each question tests, the first step of a good answer and the follow-ups to expect.
-
Mid to senior
A report query filters by region and returns the customer name and order total. An index on region exists, but the plan shows 45,000 key lookups and runs slowly. The table has 500,000 rows and 50 regions. Explain the lookups, compute when they cost more than a scan, and decide whether to add a covering index.
- What it tests
- Understanding that key lookups traverse the clustered index per row, computing the break-even point from page counts, and weighing the write cost of a wider index against the read cost of lookups.
- First step
- Count the pages of a full scan and the reads per lookup (clustered index levels plus one). At what row count do the lookups exceed the scan?
- Follow-ups
-
- How do you compute the pages a clustered index scan reads?
- What columns would the covering index include, and how much wider does it make the index?
- When would you choose a scan hint instead of a covering index?
From the lesson Reading SQL Server execution plans
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
- Pro pass · 1 week₹79$2.99
- 30 days₹179$5.99
- 1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 4 minutes
-
Mid to senior
A search procedure filters a varchar(20) mobile_number column. After a driver upgrade, the same queries run slowly and the plan shows an implicit conversion. The driver now sends nvarchar parameters. Explain the data-type precedence rule, why the conversion forces a scan, and how to fix it without changing the driver.
- What it tests
- Knowing that nvarchar has higher precedence than varchar, so the column is converted; and choosing a fix such as CAST in the procedure, a computed column, or altering the column type.
- First step
- Look up which type has higher precedence, varchar or nvarchar, in Microsoft's data-type precedence table.
- Follow-ups
-
- Why does converting the column force a scan while converting the parameter would allow a seek?
- What are the trade-offs of changing the column to nvarchar versus adding a computed column?
- How would you prove the fix worked?
From the lesson Reading SQL Server execution plans
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Mid to senior
An orders search procedure takes five optional filters, each written as (col = @p OR @p IS NULL). After every restart its speed depends on which filters the first caller sends. Explain why one plan struggles, then compare three fixes on SQL Server 2025 and say which you would ship.
- What it tests
- Seeing that the best plan depends on which parameters are NULL, comparing a recompile hint, dynamic SQL through sp_executesql and the database's own optional-parameter feature, and choosing with the costs in mind.
- First step
- Write down the best plan for a call that sends one selective filter and the best plan for a call that sends none. A single cached plan has to be valid for both, so ask what the optimizer must assume about each filter.
- Follow-ups
-
- Which cases does the database's own feature not cover?
- What does dynamic SQL cost you, and what must it never contain?
- How do you test all the combinations of filters?
From the lesson Parameter sniffing, PSP and Query Store
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 4 minutes
-
Senior to staff
After a weekend that rebuilt indexes and updated statistics, a dashboard query went from about 80 ms to about 4 s. Query Store is on. Show how you find what changed, decide between forcing the earlier plan and adding a hint, and say what you watch afterwards and when you undo it.
- What it tests
- Reading plans and runtime statistics from the Query Store views, weighing a forced plan against a hint, watching force failures, and unforcing when the data or the schema moves on.
- First step
- Find the query_id of the dashboard statement, list its plans with the average logical reads weighted by executions, and check whether a second plan appeared at the time of the weekend work.
- Follow-ups
-
- What if the old plan needs an index that no longer exists?
- Why might the earlier plan be wrong for some values?
- What would automatic plan correction do here, and what does it check?
From the lesson Parameter sniffing, PSP and Query Store
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 4 minutes
-
Mid to senior
A colleague stops an intermittent slowdown by copying each parameter into a local variable at the top of the procedure. The runtime is now steady but slower for the most common calls. On 100,000 rows where the filter column has 4 distinct values, work out the estimate the optimizer now uses and say what you would do instead.
- What it tests
- Knowing why a local variable cannot be sniffed, working out the estimate from the statistics, seeing when that estimate hurts, and choosing a better fix than hiding the parameter.
- First step
- Ask what value the optimizer has when it compiles a statement that uses a local variable, then look at the density vector of the column's statistics to see what it can use instead.
- Follow-ups
-
- When is the same estimate harmless?
- How does OPTION (RECOMPILE) change what a local variable means?
- What would you check in the plan to prove your estimate?
From the lesson Parameter sniffing, PSP and Query Store
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
All PL/SQL & T-SQL (Oracle and SQL Server) interview questions · The PL/SQL & T-SQL (Oracle and SQL Server) track