Scans, Seeks, and Lookups

Most of the operators you will see in a report plan are one of five things. Knowing what each implies about the data lets you read a plan in about twenty seconds.

Table Scan

The table has no clustered index — it is a heap — and SQL Server is reading every page. On a small lookup table this is fine and often optimal. On a fact table it is the whole cost of the query.

Clustered Index Scan

Reading every row of the table in clustered-key order. Despite the name, this is a scan: it touches everything. SSMS drawing "Clustered Index Scan" makes people think an index is being used efficiently; it is not, it is a full read of the table.

A scan is not automatically wrong. If the query genuinely needs 60% of the rows, a scan is the cheapest way to get them and an index seek repeated a million times would be slower. Scans are a problem when the query needs a small fraction of the table and reads all of it anyway.

Index Seek

The engine navigated the B-tree directly to the rows that match. Cost is roughly proportional to rows returned rather than table size. This is what you want for a selective predicate.

Watch for one variation: a seek with a Seek Predicate plus a separate Predicate in the tooltip. The Seek Predicate is what narrowed the B-tree traversal; the plain Predicate is a filter applied to every row the seek returned. A seek that returns 800,000 rows and then filters them down to 300 is doing most of the work of a scan. Check both properties, not just the operator name.

Key Lookup and RID Lookup

This is the one that quietly dominates slow reports.

A nonclustered index contains its key columns, its INCLUDE columns, and a pointer to the base row. If your query asks for a column the index does not contain, SQL Server must go fetch the rest of the row — a Key Lookup against the clustered index, or a RID Lookup against a heap. It does this once per row, via a Nested Loops join.

At 50 rows this costs nothing. At 200,000 rows it is 200,000 separate random reads, and it is frequently the entire runtime of a report.

The behavior that makes this worth watching: the optimizer knows lookups are expensive, so past a tipping point (often a few percent of the table) it abandons the index and scans instead. That means you can see a query flip from fast seek+lookup to full scan just because the date range widened — a sudden, dramatic slowdown with no code change.

The fix is to make the index cover the query by adding the missing columns as INCLUDE columns. Module 3 does this properly. To find lookups worth fixing:

-- Cached plans containing Key or RID Lookups, most expensive first
SELECT TOP (25)
       qs.execution_count,
       qs.total_elapsed_time / qs.execution_count / 1000 AS avg_ms,
       qs.total_logical_reads / qs.execution_count       AS avg_reads,
       SUBSTRING(t.text, 1, 300) AS query_snippet,
       p.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)    AS t
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
WHERE CAST(p.query_plan AS nvarchar(max)) LIKE '%Lookup%'
ORDER BY qs.total_elapsed_time DESC;

Seeing the difference yourself

Runnable on any database, against a table you can create in tempdb:

-- Build a demo table (safe: it lives in tempdb and disappears on restart)
DROP TABLE IF EXISTS #Orders;
CREATE TABLE #Orders (
    OrderId     int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    CustomerId  int          NOT NULL,
    OrderDate   date         NOT NULL,
    Total       decimal(18,2) NOT NULL,
    Notes       nvarchar(400) NULL
);

INSERT INTO #Orders (CustomerId, OrderDate, Total, Notes)
SELECT TOP (200000)
       ABS(CHECKSUM(NEWID())) % 5000,
       DATEADD(DAY, -(ABS(CHECKSUM(NEWID())) % 1500), CAST(SYSDATETIME() AS date)),
       (ABS(CHECKSUM(NEWID())) % 100000) / 100.0,
       REPLICATE(N'x', 200)
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId ON #Orders (CustomerId);

SET STATISTICS IO ON;

-- 1. Seek + Key Lookup: index has CustomerId, query wants Total and Notes too
SELECT OrderId, OrderDate, Total, Notes
FROM   #Orders
WHERE  CustomerId = 42;

-- 2. Covered: everything the query needs is in the index
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_Covering
    ON #Orders (CustomerId) INCLUDE (OrderDate, Total);

SELECT OrderId, OrderDate, Total
FROM   #Orders
WHERE  CustomerId = 42;

-- 3. Non-SARGable: function on the column forces a scan (Module 4)
SELECT OrderId, Total
FROM   #Orders
WHERE  YEAR(OrderDate) = 2026;

SET STATISTICS IO OFF;

Read the Messages tab. Query 1 and query 2 return the same rows; query 2 does a fraction of the logical reads. Query 3 reads the whole table for what should be a range.

Nested Loops paired with a Lookup

When you see Nested Loops whose inner side is a Key Lookup, read the Number of Executions on the lookup, not the cost percentage. Executions equals the number of outer rows, and it is the honest measure of how much work the lookup is doing.

The short version

Scan versus seek is only half of it. The operator to look for is a Key Lookup, because it runs once per row and it is usually where a report's time goes. If the outer side produces two hundred thousand rows, that is two hundred thousand random reads, and the fix is INCLUDE columns so the index covers the query. It also explains queries that fall off a cliff when the date range widens, because past a tipping point the optimizer gives up on the index and scans.