An execution plan is evidence about how SQL Server intends to run, or actually ran, a query. It is not a list of universal tuning rules. Start with the query's required result, inspect the plan and compare relevant measurements before recommending an index.

The example uses a disposable local temporary table. Its six rows are suitable for learning plan inspection, not for proving a production performance improvement.

What can a small plan exercise show?

CREATE TABLE #PlanOrders (
 OrderId int PRIMARY KEY,
 CustomerId int NOT NULL,
 Amount decimal(10,2) NOT NULL
);
INSERT #PlanOrders VALUES
 (1, 10, 12.00), (2, 10, 18.00), (3, 20, 7.00),
 (4, 30, 22.00), (5, 30, 11.00), (6, 40, 4.00);

SELECT OrderId, Amount
FROM #PlanOrders
WHERE CustomerId = 30
ORDER BY OrderId;

CREATE INDEX IX_PlanOrders_Customer ON #PlanOrders(CustomerId) INCLUDE (Amount);

SELECT OrderId, Amount
FROM #PlanOrders
WHERE CustomerId = 30
ORDER BY OrderId;
DROP TABLE #PlanOrders;

Both queries should return order 4 with 22.00 and order 5 with 11.00. Capture plans using the supported tool in your SQL Server environment. The optimizer may still choose a scan on a tiny table. That is not automatic proof that the index failed.

How are estimated and actual plans different?

An estimated plan is built without executing the query. An actual plan includes execution information from a run. Compare estimated and actual row counts where available, but remember that the plan's estimates are based on the information and settings available when it was compiled.

A large mismatch can suggest that an assumption about selectivity deserves investigation. It does not, by itself, identify a single correct fix. Statistics, parameters, expressions and data distribution can all be relevant.

Is an index seek always better than a scan?

No. The useful comparison includes how much data is needed, how many lookups occur and what other work follows. A scan over a small or mostly required input can be reasonable. A seek that leads to many expensive extra operations may not be the best overall plan.

Read the data flow and predicates. Do not stop at an operator's name and label it good or bad in isolation.

What should be measured?

Use a representative workload and record the measurement conditions. Logical reads, elapsed time, CPU work and blocking can answer different questions. Check that two candidate queries produce equivalent results before comparing their costs.

Repeat observations with a suitable method and account for caching, concurrent work and compilation. This article supplies no benchmark numbers. A measured improvement on one dataset is not a guarantee for every customer, parameter or write workload.

What is the cost of adding an index?

An index consumes storage and can add work to changes in the underlying data. It can help one access pattern while being irrelevant to another. The decision should include the read/write workload and existing indexes, not just one attractive plan screenshot.

Do not add indexes to a live application during an interview exercise. Use an isolated database and an appropriate change-review process for any real production recommendation.

A clear diagnosis sequence

  1. Confirm the required result and slow scenario.
  2. Capture the relevant plan and actual measurement.
  3. Find the work or estimate that seems important.
  4. Form one testable explanation.
  5. Change one relevant factor and compare correct results.

For practice, replace the equality filter with a query that needs every row. Predict why the same index may be less useful. This tests whether your recommendation follows the workload rather than an operator-name rule.

References and next practice

learn.microsoft.com — sql plans

sql question collection · Browse interview topics · Practise a question in Interview Challenge · How to Answer Technical Interview Questions Out Loud