The execution plan said Index Seek. The same query was doing about 8,000 logical reads against a 4-million-row table and burning roughly 330ms of CPU under load. Both were true at once, and the gap between them is what this post is about: a green seek icon in the plan does not mean the seek was cheap.

We found it while profiling the geoIP lookup inside a live SaaS platform, an older web application on Windows, IIS, and MSSQL. Every page that wants to geolocate a visitor fires a SELECT against one table to turn an IP into a location. At low traffic it is invisible. Under a bot swarm at several hundred requests a second it became a wall, and Query Store flagged it during a slowness incident the day before.

The table and the query

The table is dbo.GeoIPRanges, 3,955,984 rows, with a single clustered primary key on (startIpNumber, endIpNumber, locID). The rows are IP ranges, mostly non-overlapping, stored as bigints.

The hot lookup is a range-overlap check. Given a visitor IP converted to an integer, find the row whose interval contains it:

SELECT TOP 1 locID
FROM   dbo.GeoIPRanges
WHERE  startIpNumber <= @ip
  AND  endIpNumber   >= @ip;

This is a one-sided range against the leading key column, and that is the whole trap.

Why the seek lies

The clustered index leads on startIpNumber. The optimizer sees startIpNumber <= @ip, a sargable predicate on the leading column, and does what you would expect. It B-tree-seeks to the upper bound of startIpNumber and starts walking. Every row it touches on that walk gets its endIpNumber tested as a residual predicate.

The plan shows Index Seek. The estimated cost is low. The icon is green. The engine really did navigate the B-tree to an entry point. The cost is in what happens after the entry point.

For a visitor whose IP sits in the middle of the address space, the seek lands roughly halfway through the table. Then the engine walks the ~2 million rows below it, applying endIpNumber >= @ip to each one, until TOP 1 finally fires. That is a scan of half the table wearing a seek costume.

There is a difference between an index seek (navigating to a point) and the index seek range (the set of rows that pass the leading-column predicate). When the leading predicate is one-sided, with an upper bound and no lower bound, the seek range is enormous and everything else is a residual applied during the walk. The operator name reports the navigation. It says nothing about how big the range is afterward.

The read count shows what really happened. SET STATISTICS IO ON does not care what the operator is called. We saw 14,718 logical reads for the IP that lands deepest in the table, and that worst sample burned around 360ms of CPU on its own. Reads and CPU both scale with how high the IP sits in the address space, which is the signature of a scan the plan happened to label a seek.

The fix is a second seek path

There is no way to make startIpNumber <= @ip two-sided. The data is the data. But you can hand the optimizer a different B-tree whose seek range is small for the exact same query:

CREATE NONCLUSTERED INDEX IX_GeoIPRanges_endIp_startIp
    ON dbo.GeoIPRanges (endIpNumber, startIpNumber)
    INCLUDE (locID)
    WITH (DATA_COMPRESSION = PAGE);

Now the optimizer has two angles on the same predicate:

  1. Seek by startIpNumber <= @ip, walk through endIpNumber. This is the old half-table-scan path.
  2. Seek by endIpNumber >= @ip, walk through startIpNumber. This is the new path.

The second path is cheap for a reason that depends entirely on the data. In a well-formed geoIP set, almost no row has both endIpNumber greater than @ip and startIpNumber greater than @ip. Those would be ranges sitting entirely above the visitor. Because ranges do not overlap and the index is sorted, the first row where endIpNumber >= @ip is overwhelmingly the row that also satisfies startIpNumber <= @ip. The seek range collapses to a handful of rows and TOP 1 fires almost immediately.

The INCLUDE (locID) makes the index covering, so the lookup is satisfied entirely from the index leaf and never goes back to the base table.

The worst sample went from 14,718 logical reads to 3, with CPU dropping from a few hundred milliseconds to effectively zero. That is about a 5000x cut on the pathological IPs, measured after the index was live on prod, and the read count backs it up.

Checking the assumption the fix rests on

The whole argument depends on one claim: ranges do not overlap. If they do, the same one-sided pathology can resurface on the new index, and we would have shipped a fix that does nothing for some inputs. So before trusting the design, we ran one query against the live 4M rows to count overlapping pairs:

WITH ordered AS (
    SELECT startIpNumber, endIpNumber,
           LAG(endIpNumber) OVER (ORDER BY startIpNumber) AS prev_end
    FROM   dbo.GeoIPRanges
)
SELECT COUNT(*) AS overlapping_pairs
FROM   ordered
WHERE  prev_end IS NOT NULL
  AND  prev_end >= startIpNumber;

Zero overlapping pairs across all four million rows. The assumption held, so the design was sound. Had that count come back nonzero, the second index alone would not have saved us.

The online build we were sure about

We had originally written the DDL with ONLINE = ON and waved it through as fine. A reviewer flagged it as an unsafe assumption, because ONLINE is edition-gated. We went to check instead of arguing, and it turned out we had half-talked ourselves into believing an earlier 2016 service-pack feature unlock covered online index builds. The box is running Standard Edition. Those earlier SPs unlocked things like DATA_COMPRESSION and partitioning on Standard, but online index operations stay Enterprise-only on every version. The server settled it when the first apply attempt came back with “Online index operations can only be performed in Enterprise edition of SQL Server.”

So the build was offline, with no way around it. On a calm box it took about 12 to 13 seconds against the static 4M-row table, holding a schema-modify lock for that window, which is why it went out off-hours.

DATA_COMPRESSION = PAGE brought the index in at about 57MB (7153 pages), against an uncompressed estimate of roughly 80MB. We had guessed it would compress harder, closer to 40MB, and it didn’t. Sorted bigint IP ranges still have enough locality that page compression earns its keep, and the smaller footprint means fewer reads per seek on top of everything else.

Verifying with more than one IP

A single mid-range IP can look great while a deep one still scans. We tested low, mid, and high ranges plus deliberate misses, and read the execution plan together with STATISTICS IO for each. The plan tells you which index the optimizer picked. The read count tells you whether the seek was actually cheap.

One correction on scope. An earlier version of this account took the 332ms figure from an incident capture during a slow-site episode and read it as proof that the request logging procedure ran this scan on every request. That overreached. What the monitoring evidence supports is that the lookup was a material CPU consumer. The result also holds only for this verified dataset of non-overlapping ranges, and the fix was tested in a controlled environment before any production change.

What to take from it

When a seek costs like a scan, look at the seek range and the logical reads. Then ask whether a second covering index oriented around the other side of the range would give the optimizer a small seek, and check the data assumption that fix depends on before you build it.