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 332ms of CPU per call under load. Both of those were true at the same time, and the gap between them is the entire post.

This surfaced while investigating a slow range lookup. The monitoring evidence identified it as a material CPU consumer. When I pulled up the plan, the operator was a clean green Index Seek with a low estimated cost. Nothing in the plan icon told me the work was acceptable. The logical reads and CPU evidence told a different story.

The table and the query

There is a geoIP table that every page hits to geolocate a visitor:

CREATE TABLE dbo.geoip_ranges (
    startIpNumber bigint NOT NULL,
    endIpNumber   bigint NOT NULL,
    locID         int    NOT NULL,
    CONSTRAINT PK_geoip_ranges PRIMARY KEY CLUSTERED
        (startIpNumber, endIpNumber, locID)
);

3,955,984 rows. One clustered primary key on (startIpNumber, endIpNumber, locID). The ranges are non-overlapping, sorted blocks of the IP address space, each mapped to a location.

The hot lookup is a textbook range-overlap query. The visitor IP has to fall inside one row’s [startIpNumber, endIpNumber] interval:

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

This type of lookup can sit on a request path and accumulate cost quickly under normal load. That is why representative measurements matter more than a reassuring operator label.

Why the seek lies

The clustered index leads with startIpNumber. The optimizer looks at startIpNumber <= @ip, sees a sargable predicate on the leading key column, and does what looks correct: it seeks the B-tree to the upper bound on startIpNumber and walks the range that satisfies the predicate. Every row in that walk gets its endIpNumber tested as a residual.

That is a real seek. The plan is not lying about the operator. It is lying by omission about the seek range.

There is a difference between an index seek, which is B-tree navigation to a specific entry point, and the seek range, which is the set of rows that pass the leading-column predicate and then get walked. When the leading predicate is one-sided, <= @ip with no lower bound, the seek range is everything below the boundary. For a visitor whose IP sits in the middle of the address space, the seek lands roughly halfway through the table and the engine walks about 2 million rows, testing endIpNumber on each one, until TOP 1 finally fires on the first row that satisfies the second predicate.

A seek that walks half the table is a half-table scan wearing a seek’s icon. That is where the ~8,000 logical reads and ~332ms of CPU come from. The optimizer’s estimated cost is low because the seek itself is cheap. The walk after it is not in the icon.

The fix is a second seek path, not a query rewrite

You cannot make startIpNumber <= @ip two-sided. The data is what it is and the predicate is what the lookup needs. The instinct is to rewrite the query, and there are levers there (ORDER BY startIpNumber DESC with a TOP 1 and a post-filter can work). But the cleaner fix is to hand the optimizer a different B-tree to enter from, oriented around the other end of the range:

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

Now the optimizer has two angles of attack:

  1. Seek by startIpNumber <= @ip, walk down through endIpNumber. The old half-table-scan path.
  2. Seek by endIpNumber >= @ip, walk up through startIpNumber. The new path.

The new path is the cheap one, and it is cheap for a reason worth stating out loud. In a well-formed geoIP dataset, almost no rows have an endIpNumber greater than @ip and a startIpNumber also greater than @ip. Those would be ranges sitting entirely above the visitor’s IP. Because the ranges do not overlap and they are sorted, the first row where endIpNumber >= @ip is overwhelmingly the row that also satisfies startIpNumber <= @ip. The seek range on the new index is tiny. TOP 1 fires almost immediately.

The INCLUDE (locID) makes the index covering, so the lookup never bounces back to the clustered index for the one column it returns.

In this verified dataset, worst-case logical reads fell from roughly 14,000 to 3. That result is not a portable promise: it depends on the schema, data distribution, query shape, statistics, engine version, and non-overlap assumption.

Validate the assumption and plan the change safely

Before adding an index or changing a query on a consequential system, obtain appropriate authorization and change approval. Test with representative, privacy-safe data in a controlled environment; review the actual plan and I/O across realistic values; assess write overhead, storage, locking, availability, and downstream effects; prepare monitoring, rollback, backup and recovery steps; and verify the result after deployment. Do not treat an example query as a production runbook.

The empirical check the whole thing depends on

Every word of the argument above rests on one assumption: the ranges do not overlap. If they do, the same one-sided pathology can come right back on the new index, just from the other direction. So before trusting the design, I ran one query against the live table to prove the assumption instead of believing it:

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

Zero overlapping pairs across all 4 million rows. The assumption held, so the design was sound. If that count had come back non-zero, the covering index would have been a smaller win at best and a false sense of safety at worst.

One more thing worth stating here: verifying a query plan fix with a single sample IP proves nothing, because the pathology is distribution-dependent. The whole point is that a mid-range IP is slow while an edge IP looks fine. The real verification tests IPs across the low, middle, and high ranges plus a miss, and reads STATISTICS IO and the actual plan on each, not one lucky sample.

Two things that bit on the way to deployment

The first draft assumed that an online index build would be available. That was an unverified assumption. Index-build capabilities, locking behavior, compression support, storage effects, and availability impact vary by database edition, version, configuration, workload, and table characteristics. Verify those facts in the target environment through approved change procedures before selecting a deployment method. If a change cannot run online, plan the maintenance window, user impact, monitoring, rollback, and recovery deliberately rather than treating the build as a harmless DDL statement.