SQL for .NET developers Advanced

Reading execution plans

An execution plan shows how SQL Server actually runs your query: which indexes it reads, how it joins tables and where the time goes. This lesson shows how to get a plan, the operators to recognise, and the warning signs that point straight to a fix.

Reading a plan, right to left
  1. StartData accessSeek, scan or lookup on each table
  2. Step 2JoinNested loops, hash match or merge join
  3. Step 3Sort or aggregateORDER BY, GROUP BY, DISTINCT
  4. Step 4SELECTRows returned to your app
  5. EndCheckThick arrows, warnings, estimated vs actual rows

Getting a plan

In SQL Server Management Studio or Azure Data Studio, press Ctrl+M (Include Actual Execution Plan) and run the query. The plan tab shows operators as boxes connected by arrows; read it from right to left, because data flows from the right to the SELECT on the left.

SET STATISTICS IO, TIME ON adds the number of pages read per table, which is the most reliable way to compare two versions of a query. Query Store keeps plans of past queries so you can find slow ones later.

Operators to recognise

  • Index Seek: jumps straight to matching rows. Usually what you want.
  • Index Scan or Table Scan: reads the whole index or table. Fine on small tables, a red flag on big ones.
  • Key Lookup: found the row in a nonclustered index but went back to the table for more columns, once per row. Many lookups are slow; a covering index removes them.
  • Nested Loops, Hash Match and Merge Join: the three ways to join. Nested Loops suits few rows, Hash Match suits big unsorted inputs.
  • Sort: can be expensive and may spill to tempdb; an index in the right order can avoid it.

Example: fixing a key lookup

The plan for this query showed an Index Seek on IX_Orders_CustomerId plus a Key Lookup for OrderedAt and Total on thousands of rows. Including those columns in the index makes it covering, and the lookup disappears.

SQL
SET STATISTICS IO ON;

SELECT OrderId, OrderedAt, Total
FROM Orders
WHERE CustomerId = @CustomerId;
-- Before: logical reads 3,412 (seek + key lookups)

CREATE INDEX IX_Orders_CustomerId
    ON Orders (CustomerId) INCLUDE (OrderedAt, Total)
    WITH (DROP_EXISTING = ON);
-- After:  logical reads 6 (seek only)

Warning signs

  • Estimated rows far from actual rows: statistics are stale or the query is not sargable. Update statistics or rewrite the filter.
  • Yellow warning triangles: implicit conversions (often varchar vs nvarchar), spills to tempdb or missing statistics.
  • Missing index suggestions are hints, not orders; check they do not duplicate existing indexes.
  • Parameter sniffing: a plan built for one rare value is reused for common values. OPTION (RECOMPILE) or better indexing can help.

Interview question

What is a key lookup and how do you remove it?

A key lookup happens when SQL Server finds rows through a nonclustered index but needs columns that the index does not contain, so it goes back to the clustered index for each row. With many rows this is slow. Add the missing columns to the index as INCLUDE columns, making it a covering index, or select fewer columns.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.