An Index Seek That Reads Millions of Rows: Residual Predicates

The plan icon looks reassuring until you open its properties. An index seek can read thousands of rows and return only a handful when a residual predicate filters after navigation. Compare rows read with rows returned before trusting the label.

Build an Index Seek With a Residual Filter

Use a controlled table to make the difference visible. The date predicate below can find a range in the index. RegionID is included only as a payload column, so it cannot narrow the date seek. The engine can test RegionID row by row after reading the date range. In a production table, use existing indexes and representative parameters instead of creating these sample objects.

CREATE TABLE dbo.SeekDemo
(
    OrderID int IDENTITY PRIMARY KEY,
    OrderDate date NOT NULL,
    RegionID int NOT NULL,
    Amount money NOT NULL
);
WITH n AS
(
    SELECT TOP (200000)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
INSERT dbo.SeekDemo(OrderDate,RegionID,Amount)
SELECT DATEADD(day,CONVERT(int,rn % 3650),'20150101'),
       CONVERT(int,rn % 100), 10.00
FROM n;
CREATE INDEX IX_SeekDemo_Date
ON dbo.SeekDemo(OrderDate) INCLUDE (RegionID,Amount);

The cross join supplies enough rows on a typical instance; verify the inserted count because system catalog size varies. I use a disposable database for this example. The point is not to create a million-row benchmark with a fixed score, but to produce a wide date range and a selective region condition.

Read the Index Seek Operator Properties

Run the query with an actual plan and logical reads. The query requests one region across a broad date span. Look at the Index Seek operator, not only the graphical icon. Seek Predicates should describe the navigated date range. Predicate can list the RegionID test, which is applied to candidate rows from the range.

SET STATISTICS IO, TIME ON;
SELECT OrderID,OrderDate,RegionID,Amount
FROM dbo.SeekDemo
WHERE OrderDate >= '20230101'
  AND OrderDate < '20240101'
  AND RegionID = 7;
SET STATISTICS IO, TIME OFF;

Check Actual Number of Rows and Actual Number of Rows Read in the operator properties. If tens of thousands of rows are read and only a small fraction survive, the seek did substantial scanning inside its chosen range. The exact plan can vary with row count and statistics. If the optimizer picks another access path, record that choice rather than forcing an icon to match the example.

Separate Index Seek and Residual Conditions

Seek Predicates constrain the traversal using key order. Predicate on the same operator is a residual test on rows already visited. A separate Filter operator can also apply a residual condition; the plan shape depends on the query. Included columns can cover output and prevent lookups, but they do not become navigable key columns. That distinction is why a covering index can still have high read counts.

I compare estimates as well as actuals. A bad estimate can make a broad seek look cheap to the optimizer and cause a plan choice that performs poorly at runtime. What portion of the selected date range belongs to region 7? If it is tiny, the current key order puts the less selective condition first for this workload.

Change the Leading Key

For this query, put equality on RegionID first, then the date range. SQL Server can navigate to a specific region and then seek the date interval within it. Keep output-only columns in INCLUDE if that covers the query. Create the alternate index and compare plans with the same values and warm-cache conditions. Do not immediately drop the old date-first index; other queries can depend on it.

CREATE INDEX IX_SeekDemo_Region_Date
ON dbo.SeekDemo(RegionID,OrderDate)
INCLUDE (Amount);
SELECT OrderID,OrderDate,RegionID,Amount
FROM dbo.SeekDemo
WHERE OrderDate >= '20230101'
  AND OrderDate < '20240101'
  AND RegionID = 7;

Now inspect Seek Predicates and Actual Rows Read again. RegionID and the date interval should participate in the navigation when this index is selected. With a clustered primary key, OrderID is available as the clustering key in a nonclustered index; a different table design can need another included column. The optimizer still chooses among available indexes, so compare observed reads rather than assuming it must take the newest one. In my run, the date-first seek read about a hundred rows for every row it returned; the region-first seek read only the rows it returned.

Measure the Tradeoff Beyond One Query

Index key order is workload-specific. (RegionID, OrderDate) helps queries that filter by region, then date. A date-only report can prefer the original (OrderDate) index. The new index consumes storage, adds write maintenance, and creates its own statistics. Check insert and update costs, page usage, and other important plans before retaining both. A narrow filtered index can be better when one region or status dominates the workload.

Do not use a hint to force an index as the first repair. A hint can hide that the new key is not generally useful. I gather logical reads, CPU, elapsed time, rows read, and returned rows for representative parameter values. One execution with an empty region tells little about a busy region whose selectivity is very different.

Watch Implicit Conversions and Expressions

A residual predicate can also appear because the column is wrapped in a function or compared to an incompatible type. CONVERT(date, OrderDateTime) and a mismatched Unicode parameter can change the usable seek range. Inspect warnings and Scalar Operator expressions in the plan. Rewriting a predicate as a half-open range and matching parameter types can help before adding an index.

DECLARE @start_date date = '20230101', @end_date date = '20230131';
SELECT OrderID FROM dbo.Orders
WHERE OrderDateTime >= @start_date
  AND OrderDateTime < DATEADD(day,1,@end_date);

The example requires an Orders table with a datetime column; it illustrates a sargable range. Check time-zone meaning and inclusive end-date requirements before using it. A plan should be judged by the work it performs, not by the comforting name of one operator.

Keep the Before and After Evidence

Rows-read counters depend on actual execution plan collection. An estimated plan has estimated rows and access predicates, but no measured Actual Rows Read for that execution. Collect the actual plan in a safe test or use a supported runtime capture method when production overhead matters. Check whether a parallel plan reports counters per thread and look at the aggregated value before comparing executions.

Save both actual plans, statistics IO output, query parameters, index definitions, and row counts. If the rows-read gap closes but elapsed time does not improve, another part of the query can dominate. If the new index helps one parameter and harms another, revisit statistics and the broader query pattern. I keep the old plan until the change has been tested under normal load.

An Index Seek is a starting clue. Its seek predicates reveal how far the index navigation goes; Actual Rows Read reveals what the navigation costs. The difference between rows read and rows returned is where a residual predicate can hide a large amount of work.

Related reading on this blog: Removing Key Lookup: Seek Predicate: Predicate: An Interesting Observation Related to Datatypes and Avoid Functions in the WHERE Clause for Performance.

An Index Seek label is not proof of low reads, it is a plan operator whose predicates need inspection.

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