The Billion-Row Scan: Why Your SQL Query for MAX Is Slow and How to Fix It

SUMMARY:

Database professionals facing surprisingly slow SELECT MAX() queries on massive SQL Server tables can eliminate costly billion-row Clustered Index Scans by intentionally introducing a non-partitioned Non-Clustered Index to provide the query optimizer a globally sorted shortcut.

• Table partitioning is a double-edged sword that, despite aiding large table management, can break the contiguous sorting structure of a clustered index, forcing the SQL Server optimizer to scan every partition to find the true global maximum.
• Creating a non-partitioned Non-Clustered Index on a column already covered by the clustered index is necessary to create a unified, globally sorted B-tree, enabling the optimizer to find the maximum value instantly.
• Do not trust the graphical execution plan icon alone; detailed analysis of the plan’s properties—specifically checking for ScanDirection = BACKWARD and ActualRowsRead = 1—confirms the high-efficiency seek operation.
• Redundant indexing and declaring the index as UNIQUE (when the column, like an IDENTITY column, guarantees uniqueness) are sometimes necessary strategic trade-offs that exchange minimal storage cost for massive performance gains.

Understanding deeper architectural features, such as how table partitioning affects a clustered index’s global sort order, is crucial for correctly diagnosing and resolving these deceptively simple performance bottlenecks.

A Simple Query with a Devious Problem

It’s a scenario every database professional has encountered: a seemingly simple query brings a powerful server to its knees. In this case, 64 Core / 1TB RAM / all SSD.

… AND the query was as basic as it gets:

SELECT @LastMaxID = MAX(AttributeID) FROM dbo.HugeTable;

The AttributeID column is a BIGINT and the table’s IDENTITY column. It’s also the leading column of the clustered index. By all accounts, this query should be instantaneous. The SQL Server optimizer should simply navigate to the end of the clustered index, pick off the last value, and be done.

Instead, the execution plan revealed a costly Clustered Index Scan. The server was reading the entire table—all 1.78 billion rows of it—to find a single value. The query took ages, consumed massive I/O, and left me scratching my head. How could the optimizer make such a poor choice?

The answer wasn’t in the query or the index itself, but in a deeper architectural feature: table partitioning.

When Partitioning Works Against You

Our HugeTable was partitioned by a YYYYMM date column. Partitioning is a fantastic feature for managing enormous datasets; it allows for efficient data loading, archiving, and partition-level maintenance. The clustered index key included this partition column to ensure it was “partition-aligned,” which is a standard best practice.

CREATE UNIQUE CLUSTERED INDEX ... ON HugeTable(AttributeID, PartitionYYYYMM)

This alignment is typically great for performance, but it was the source of our MAX() query problem. A partitioned table isn’t one single, physically sorted structure. It’s a collection of many smaller, individually sorted B-trees.

For the optimizer, this means there is no single “end of the table.” The highest AttributeID could be at the end of any of those partitions. To guarantee a correct result, the optimizer concluded it had no choice but to scan every row in every partition to find the true global maximum.

The Counterintuitive Fix: Adding a “Duplicate” Key

The solution initially felt wrong: I needed to add another index on the exact same column.

CREATE UNIQUE NONCLUSTERED INDEX IX_HugeTable_AttributeID ON dbo.HugeTable(AttributeID);

Wait, create a non-clustered index on a column that’s already the lead key of the clustered index? Isn’t that just duplicating data and wasting space?

Yes, it is. And in this case, it was absolutely necessary.

This is a classic “space vs. speed” trade-off. The key difference is that this new non-clustered index was not partitioned. By default, it was created on the primary filegroup as a single, globally sorted B-tree. It contained a sorted list of every AttributeID from the entire table, from all partitions, in one unified structure.

This gave the query optimizer the “shortcut” it was missing. With this index in place, finding the MAX(AttributeID) became trivial again:

1. Identify the new, non-partitioned index.
2. Navigate directly to the last page of that single index structure.
3. Read the value.

The cost of storing this “duplicate” key—a BIGINT value for each of the billions of rows—was a small price to pay for turning a multi-hour query into a millisecond operation.

Don’t Trust the Icon: Read the Real Execution Plan

After creating the index, I reran the query. The new execution plan was dramatically cheaper, but one thing was visually confusing: the primary operator was still an Index Scan.

If you only look at the GUI, you might think the problem isn’t solved. This is where the true value of reading the plan’s XML or properties comes in. When I examined the details of the Index Scan Operator, I saw the real story:

• ScanDirection = BACKWARD: It wasn’t starting from the beginning; it started from the end of the index.
• A Top Operator: The scan was feeding rows into a Top (1) operator, meaning it was instructed to stop after finding the very first row.
• ActualRowsRead = 1: The runtime statistics confirmed it! To find the maximum value, the engine only had to read a single row from the index.
• ActualLogicalReads = 4: The entire operation was completed by reading only four data pages from memory.

What the icon called a “scan” was, in reality, a hyper-efficient seek to the last entry in the B-tree. The billion-row read operation was gone, replaced by an operation so fast it was essentially free.

Key Takeaways for Data Professionals

This journey from a crippling scan to a near-instant query offers several important lessons:

1. Partitioning is a Double-Edged Sword: While essential for managing very large tables, partitioning can introduce performance complexities. Understand how it changes the physical data structure and affects optimizer choices.
2. Redundant Indexes Aren’t Always Redundant: An index that seems to duplicate another can serve a completely different purpose. Our non-partitioned index provided a global view that the partitioned clustered index could not.
3. Dig Deeper Than the GUI: The graphical execution plan is a great starting point, but the real insights are in the properties and the XML. ActualRowsRead and other runtime stats tell you what really happened.
4. Declare Your Intentions with UNIQUE: Because our AttributeID was an IDENTITY column, I defined the new index as UNIQUE. This not only enforces data integrity but also provides the optimizer with more accurate information to create even better plans for other queries.

Next time you face a deceptively simple query with a shockingly bad execution plan, remember this story. The solution might be counterintuitive, but by understanding the underlying architecture, you can give the optimizer the tools it needs to get the job done right.

Check out our SQL Admin and Support Services.

Please contact us for more information.