The Tipping Point: Why SQL Server Skips Your Nonclustered Index

You create a nonclustered index, run the query, and see a table scan. The tipping point is where many key lookups cost more than reading the table once. SQL Server is choosing between two bills, and the new index is not always the cheaper one.

Where the Tipping Point Comes From

A narrow nonclustered index stores its key and a row locator. If a query asks for columns outside that index, SQL Server can seek the index and fetch the missing values from the clustered index or heap. One lookup is cheap. Thousands of lookups can mean thousands of scattered reads. At some estimated row count, the optimizer chooses a scan instead. That change point is called the tipping point, but it is not a fixed percentage of a table.

I see a new index blamed when a broad report still scans. The index is working as designed for a small slice. The report asks for too much data or too many extra columns. The first question is not how to force the index. It is how many rows and lookups the actual query needs.

Build a Reproducible Sample

The following script creates a temporary table with a clustered key, a narrow nonclustered index, and a payload that the nonclustered index does not contain. It is safe for a test session and leaves no permanent object. The exact plan depends on row counts, statistics, and server settings, so use the example to inspect a mechanism rather than memorize a cutoff.

CREATE TABLE #LookupDemo
(
    OrderID int NOT NULL PRIMARY KEY CLUSTERED,
    GroupID int NOT NULL,
    IsActive bit NOT NULL,
    Payload char(120) NOT NULL
);
WITH n AS
(
    SELECT TOP (100000)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b
)
INSERT #LookupDemo (OrderID, GroupID, IsActive, Payload)
SELECT CONVERT(int, rn), CONVERT(int, rn % 1000),
       CONVERT(bit, CASE WHEN rn % 20 = 0 THEN 1 ELSE 0 END),
       REPLICATE('X', 120)
FROM n;
CREATE INDEX IX_LookupDemo_GroupID ON #LookupDemo (GroupID);

Tempdb needs room for the sample. On a small test instance, reduce the row count while keeping the ratio between the two filters. Do not run an artificial load on a busy production server. The nonclustered index can locate GroupID values, but Payload still lives in the clustered row. That missing column creates the lookup choice.

Watch the Tipping Point Move as the Range Grows

Turn on STATISTICS IO and actual execution plans. Run a selective range and a broad range with OPTION (RECOMPILE), which lets each statement be optimized for its own literal. Summing a checksum of Payload keeps the output small while still requiring the payload value. Read logical reads and inspect whether the plan uses Key Lookup or a clustered index scan.

SET STATISTICS IO ON;
SELECT SUM(CHECKSUM(Payload)) AS payload_check
FROM #LookupDemo
WHERE GroupID BETWEEN 1 AND 5
OPTION (RECOMPILE);
SELECT SUM(CHECKSUM(Payload)) AS payload_check
FROM #LookupDemo
WHERE GroupID BETWEEN 1 AND 800
OPTION (RECOMPILE);
SET STATISTICS IO OFF;

The first range should touch a small slice. The second asks for most groups. If your plan does not switch, widen or narrow the ranges and compare again. The point moves with row width, cache, table size, and estimated distribution. SQL Server cannot see your stopwatch in advance; it compares estimated operator costs. A poor estimate can put the switch in the wrong place.

Try several ranges between the two extremes. For each, record the qualifying rows, logical reads, and whether the plan uses lookups or a scan. Narrow the interval where the choice changes. This is a local observation for this table and projection, not a threshold to carry to another database. Repeat after updating statistics to see whether the estimate moved the decision. Also compare the actual rows at the index access and lookup operators. If the estimate says ten rows and the operator returns ten thousand, the problem is not simply the cost of a correct lookup estimate. The optimizer is pricing the wrong quantity.

Try a Covering Index

INCLUDE places columns in the leaf level without making them key columns. Add Payload as an included column, then repeat the two queries. The optimizer can satisfy the query from that index without returning to the base table for Payload. The new index is larger and costs more to maintain on writes. That trade is justified only if important queries save enough work.

CREATE INDEX IX_LookupDemo_GroupID_Cover
ON #LookupDemo (GroupID) INCLUDE (Payload);
SET STATISTICS IO ON;
SELECT SUM(CHECKSUM(Payload)) AS payload_check
FROM #LookupDemo
WHERE GroupID BETWEEN 1 AND 800
OPTION (RECOMPILE);
SET STATISTICS IO OFF;

Do not keep both sample indexes in a real design without checking for overlap. The wider index can make the narrow one redundant, but another workload can still need the small version. Measure reads and writes before dropping anything. I test the covering index with the narrow query too, because one fix should not make a common query worse by forcing a larger scan.

Use a Filtered Index for a Small Active Slice

If queries consistently ask for active rows, a filtered index can hold only that slice. It saves space and write work compared with covering every row. The filter must match the query predicate in a way the optimizer can prove. A parameterized predicate can complicate that proof, so test the application statement, not just a literal from a demonstration.

CREATE INDEX IX_LookupDemo_ActiveGroup
ON #LookupDemo (GroupID) INCLUDE (Payload)
WHERE IsActive = 1;
SELECT SUM(CHECKSUM(Payload)) AS payload_check
FROM #LookupDemo
WHERE IsActive = 1 AND GroupID BETWEEN 1 AND 800
OPTION (RECOMPILE);

The sample marks only one row in twenty as active. The filtered index is useful for that workload, while the broad query still has a different access path. Check index size, estimated rows, actual rows, and logical reads. A filtered index is not a shortcut for queries that omit the filter or need the inactive rows.

Accept the Scan When It Wins

A broad report that reads most of a table can be best served by a scan. That is a valid outcome, even after careful indexing. Forcing a seek can replace one sequential read with thousands of random lookups. Check the result set and whether the report truly needs every payload column. Sometimes a narrower projection changes the economics more than another index.

I keep the test numbers beside the plan. Record the filter, qualifying rows, logical reads, elapsed time, and plan shape for both narrow and broad cases. If the range is parameterized, test values on both sides of the switch and examine parameter-sensitive plan behavior. The word seek is not a performance certificate. A scan can be the efficient route when the request is wide enough.

Where does your own workload stop benefiting from a seek and start benefiting from a scan?

Related reading on this blog: SQL SERVER Performance Tuning: Catching Key Lookup in Action and Index Scans are Not Always Bad.

The tipping point is not a command to seek, it is the measured trade between access paths.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.