SQL Code Smells: Unindexed Range Predicates

When a query filters a large table on a range, like a date window, and no index supports that range, SQL Server scans the table. Put that scan inside a subquery that gets joined to other inputs, and a query that should take seconds can run for hours.

In a recent case, one covering index took a query from almost two hours in the worst case down to about four seconds.

What is an unindexed range predicate?

A range predicate filters on a span of values instead of one exact value: >, <, >=, <=, and BETWEEN. With an index that leads on the range column, SQL Server seeks to the start of the range and reads only the rows inside it. Without one, it reads the entire table and tests every row.

The pattern usually looks harmless:

CREATE OR ALTER PROCEDURE dbo.GetRegionOrders    @Region    varchar(20),    @StartDate datetime2(0),    @EndDate   datetime2(0)ASBEGIN    SET NOCOUNT ON;    SELECT c.CustomerName, oh.OrderID, oh.OrderDate,           oh.OrderTotal, p.ProductName    FROM (SELECT *          FROM dbo.OrderHistory          WHERE OrderDate > @StartDate            AND OrderDate < @EndDate) AS oh    JOIN (SELECT CustomerID, CustomerName          FROM dbo.Customers          WHERE Region = @Region) AS c        ON c.CustomerID = oh.CustomerID    JOIN (SELECT ProductID, ProductName          FROM dbo.Products) AS p        ON p.ProductID = oh.ProductID;END;

Against a small OrderHistory table this is fast. Once the table grows into the millions of rows, with only a clustered primary key on OrderID, nothing lets SQL Server seek on OrderDate.

A note on SELECT *: SQL Server only reads the columns the outer query actually uses from a derived table, so SELECT * here does not pull every column by itself. What it does is hide which columns the query depends on, and that makes it harder to build an index that covers them.

Why does it exist in code?

• The table was small when the query was written. Scanning 50,000 rows takes milliseconds, so nobody noticed there was no index to seek on.
• Indexes were built for the primary key, not for how the table is queried. The table gets a clustered index on an identity column and nothing else.
• The date filter was added later. A report picks up a "last 90 days" requirement and the new predicate goes in without anyone revisiting the indexes.
• Development data hides it. Test databases are small, so the worst case never shows up until production.

Why is it a problem?

Scans scale with the table, not the result

Without a supporting index, the cost of the filter is tied to the size of OrderHistory, not the number of rows in the window. A one day window costs about as much to read as a one year window, and both get slower every month the table grows.

Joins can turn one scan into thousands

When the derived table is joined to other inputs, SQL Server may choose a Nested Loops join with OrderHistory on the inner side, pushing the join column down into it. With no supporting index, every outer row triggers another scan. Five thousand customers against a 20 million row table is on the order of 100 billion rows read.

Sniffed parameters make it unpredictable

The procedure caches the plan built for its first set of parameter values. A plan compiled for a small region or a narrow window may choose nested loops because it expects only a few rows. Reused for a large region or a wide window, the same plan scans thousands of times. That is how one procedure finishes in seconds on one call and runs for two hours on the next. Along the way, those scans push useful data out of the buffer pool and burn CPU that other queries need.

How to find it with Database Health Monitor

Start with Long Running History

The Long Running History instance report shows the longest running queries over the past 30 days. Its Missing Indexes column shows when the plan indicates an index could have helped, and the Warnings column flags plan warnings. A procedure that appears with wildly different durations is a strong hint that its plan works for some parameter values and falls apart for others.

Double-click into the Query Advisor

Double-clicking a row opens the Query Advisor, which shows the full query text along with any missing indexes or plan warnings from the stored plan, plus a query plan analysis view when the plan is available.

Read the plan in Plan Viewer

Open the plan in the new Plan Viewer and look for:

• A Clustered Index Scan or Table Scan on the large table, especially on the inner side of a Nested Loops join with a high number of executions.
• In an actual plan, Actual Rows Read far larger than the rows returned.
• An Eager Index Spool, which means the optimizer is building a temporary index in tempdb on every run because the one it wanted does not exist.

Confirm with Enterprise Index Review

The Enterprise Index Review lists missing index suggestions with a Critical, High, Medium, or Low impact label and includes the INCLUDE clause when SQL Server recommends one. Its Missing Index Overlaps section groups suggestions that share key columns, so you can create one index instead of several that overlap. Treat suggestions as a starting point and check them against your existing indexes before creating anything.

How to fix it

Add a covering index on the range column

CREATE NONCLUSTERED INDEX IX_OrderHistory_OrderDate    ON dbo.OrderHistory (OrderDate)    INCLUDE (CustomerID, ProductID, OrderTotal);

The range column leads the key so SQL Server can seek straight to the window. The INCLUDE list carries the other columns the query uses, so there are no key lookups. Without them, past a certain number of rows the optimizer decides lookups cost more than a scan and goes right back to scanning. OrderID is the clustered key, so it is already carried in every nonclustered index.

Replace SELECT * with the columns you need

FROM (SELECT OrderID, CustomerID, ProductID, OrderDate, OrderTotal      FROM dbo.OrderHistory      WHERE OrderDate > @StartDate        AND OrderDate < @EndDate) AS oh

Listing the columns documents exactly what the index has to cover, and keeps a future change to the outer query from quietly breaking the covering index.

Tip: If the plan still joins to OrderHistory row by row on CustomerID, an index keyed on (CustomerID, OrderDate) serves that seek better. Equality column first, range column second. Check the plan after the change rather than assuming.

Verify the fix

SET STATISTICS IO, TIME ON;EXEC dbo.GetRegionOrders     @Region    = 'West',     @StartDate = '2026-01-01',     @EndDate   = '2026-10-01';SET STATISTICS IO, TIME OFF;

Run it with the worst parameter values you know of, before and after. The Scan count and logical reads on OrderHistory should drop dramatically, and the actual plan should show an Index Seek where the scan used to be. Once the right index exists, the efficient plan is usually the same for narrow and wide windows, so parameter sniffing stops mattering. Reach for OPTION (RECOMPILE) only if durations are still erratic afterward.

Find these in your environment today

Unindexed range predicates do not throw errors. They work on day one and slow down a little more every month, until one set of parameter values turns a routine procedure into a two hour job. Long Running History, the Query Advisor, Plan Viewer, and the Enterprise Index Review in Database Health Monitor let you find them by their symptoms and see the index SQL Server is asking for.

Download Database Health Monitor free at DatabaseHealth.com and start finding code smells in your environment today.