---
title: "The Index Seek That Was Actually a Half-Table Scan (5000x, on a 4M-Row geoIP Table)"
canonical: https://dxdev.com/blog/the-index-seek-that-was-a-half-table-scan/
datePublished: 2026-05-18
---
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 of those were true at the same time, and the gap between them is the entire point of this post: a green seek icon in the plan does not mean the seek was cheap.

I found this profiling the geoIP lookup inside the sports SaaS, a 25-year-old classic-ASP app 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, single clustered primary key on `(startIpNumber, endIpNumber, locID)`. 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:

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

This is not a point lookup and it is not an equality check. It 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 looks at `startIpNumber <= @ip`, sees a sargable predicate on the leading column, and does exactly 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. None of that is a lie, exactly. The engine really did navigate the B-tree to an entry point. The problem is 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, and 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.

This is the distinction between an index *seek* (navigating to a point) and the index *seek range* (the set of rows that actually 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 the size of the range you scan afterward.

The read count is where the truth lives. `SET STATISTICS IO ON` does not care what the operator is called. About 8,000 logical reads per call on average under load, worst case 14,718 for an IP that lands deepest in the table, with that worst sample burning around 360ms of CPU on its own. The reads and the 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, not a query rewrite

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:

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

Now the optimizer has two angles of attack 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 promotes 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 it is the read count that proves it, not a faster-looking plan.

## The assumption I had to actually check

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

```sql
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 non-overlap assumption the whole approach depends on held, so the design was sound. If that count had come back nonzero, the second index alone would not have saved me.

## A few details that mattered in production

`DATA_COMPRESSION = PAGE` brought the index in at about 57MB (7153 pages), against an uncompressed estimate of roughly 80MB. I had guessed it would compress harder, closer to 40MB, and it didn't, but 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.

The build ran offline, and that was not a choice, it was the edition. I 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, so I went to check instead of arguing. The box is running Standard Edition. I had half-talked myself into believing an earlier 2016 service-pack feature unlock covered online index builds. It does not. 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 the argument 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, 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.

When you verify a fix like this, do not verify with one sample IP. A single mid-range IP can look great while a deep one still scans. Test 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.

## The takeaway

Index Seek in a plan does not mean fast. It means the engine used a B-tree to find an entry point, and the operator name is silent about how much it scanned after that point. A one-sided range predicate against the leading column of a composite key seeks to a boundary and then walks everything past it, which is a range scan with extra steps.

When you see a seek that costs like a scan, do not start by rewriting the query. Look at the seek range, not the operator, and ask whether a second covering index oriented around the *other* side of the range would give the optimizer a genuinely small seek. And trust the read count over the icon. The logical-read number is the truth; the operator name is just a label.

## Related

- [The Index Seek That Was Actually a Half-Table Scan, 5000x on a 4M-Row geoIP Table](index-seek-was-half-table-scan-geoip): earlier framing of this same incident
- [It Looked Exactly Like a Bot Swarm. It Was SQL Server Parameter Sniffing.](sql-server-parameter-sniffing-looked-like-a-bot-swarm): another SQL Server problem that looked like something else entirely
- [From 3.5 million round-trips to hundreds: batching a remap with temp tables](batch-remap-temp-tables-3.5-million-round-trips): a different SQL Server performance fix: the cost of per-row queries at scale
- [Codex flagged my ONLINE=ON, my own probe said it was fine, and the server proved us both wrong](codex-flagged-online-index-standard-edition): the Standard Edition online-index trap that surfaced during the same fix
- [The Datetime That SQL Server Can't Read Back: Keep the Copy In the Database](datetime-sql-server-cant-read-back-keep-it-in-sql): another SQL Server data-representation gotcha with a similar root cause shape