TOP(1) vs MIN problem - Why does SQL Server use a clustered scan rather than seeking the covering index?

Question

Short Version

I know which one is better - but why?

--Clustered index scan of 4.5M rows / 2.1 GB
SELECT MIN(RowNumber)   FROM Transactions WHERE TransactionDate >= '20191002 04:00:00.000' OPTION(RECOMPILE)

--Covering index seek; 3 logical reads
SELECT TOP(1) RowNumber FROM Transactions WHERE TransactionDate >= '20191002 04:00:00.000' ORDER BY TransactionDate OPTION(RECOMPILE)

Long Version

I have a table as:

Transactions

RowNumber          TransactionDate
(int, clustered)   (datetime)
-----------------  -----------------------
4592515            2019-10-07 11:12:13.690
4592516            2019-10-07 11:12:13.690
4592517            2019-10-07 11:12:18.660
4592518            2019-10-07 11:12:22.960
4592519            2019-10-07 11:13:16.587
4592520            2019-10-07 11:13:22.310
4592521            2019-10-07 11:14:50.060
4592522            2019-10-07 11:15:15.073
4592523            2019-10-07 11:15:32.860
4592524            2019-10-07 11:16:12.360

I want to get the first RowNumber or on after a specific time, e.g.:

SELECT MIN(RowNumber) FROM Transactions WHERE TransactionDate >= '20191007 11:13:00' OPTION(RECOMPILE);

As expected, this performs a clustered index scan:

When your clustered index scan is 4.5M rows, and 2.1 GB, you want to avoid doing that every few seconds. In practice this query takes so long that it, for all intents and purposes, never returns.

Covering index on TransactionDate

I'm interested in TransactionDate and RowNumber, so i create an index on it

CREATE INDEX IX_Transactions_TransactionDate ON Transactions 
(
   TransactionDate
)

(since RowNumber is the unique cluster key, it will implicitly be part of the index).

Re-run a query

And now i'll run a logically identical query:

SELECT TOP(1) RowNumber FROM Transactions ORDER BY TransactionDate OPTION (RECOMPILE)

And this, as expected, seeks through the new covering index, returning instantly after 3 logical reads:

The the original equivalent query doesn't use the covering index:

SELECT MIN(RowNumber) FROM Transactions WHERE TransactionDate >= '20191002 04:00:00.000' OPTION(RECOMPILE)

It never actually returns, so i can only get the estimated execution plan:

Why does TOP(1) behave so much different, and better than MIN?

It must be statistics

The only excuse the server has to run two logically equivalent queries so horribly differently, would be statistics.

Clustered index statistics

Name                      Updated            Rows     Rows Sampled  Steps  Density  Average key
------------------------- -----------------  -------  ------------  ------ -------  -----------
IX_Transactions_RowNumber Oct 7 2019 2:32AM  4561899  4561899       5      1        4          


All density   Average Length Columns
------------- -------------- ---------
2.19207E-07   4              RowNumber

RANGE_HI_KEY RANGE_ROWS    EQ_ROWS       DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
------------ ------------- ------------- -------------------- --------------
9171         0             1             0                    1
1700836      1687750       1             1687750              1
2755345      1048575       1             1048575              1
4592121      1825569       1             1825569              1
4592122      0             1             0                    1

Covering index statistics

Name                                    Updated            Rows     Rows Sampled  Steps  Density    Average key length
--------------------------------------  -----------------  -------  ------------  -----  ---------  ------------------
IX_Transactions_TransactionDate         Oct 7 2019 2:33AM  4561899  4561899       120    0.8462854     12             


All density   Average Length Columns
------------- -------------- --------------------------
2.590376E-07  8              TransactionDate
2.19207E-07   12             TransactionDate, RowNumber

RANGE_HI_KEY            RANGE_ROWS    EQ_ROWS       DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
----------------------- ------------- ------------- -------------------- --------------
...snip...
2019-09-22 03:06:09.883 32767         1             27923                1.173477
2019-10-02 19:10:18.007 32767         1             27714                1.182327
2019-10-07 02:30:21.680 14599         2             12430                1.174497
2019-10-07 02:31:56.807 0             1             0                    1

So i have to ask:

if you were the optimizer
and you had these two logically equivalent queries
and these statistics
why would you choose to scan 4.5M rows of a table
rather than seek to the exact answer?

tl;dr:

--Clustered index scan of 4.5M rows / 2.1 GB
SELECT MIN(RowNumber)   FROM Transactions WHERE TransactionDate >= '20191002 04:00:00.000' OPTION(RECOMPILE)

--Covering index seek; 3 logical reads
SELECT TOP(1) RowNumber FROM Transactions WHERE TransactionDate >= '20191002 04:00:00.000' ORDER BY TransactionDate OPTION(RECOMPILE)

why?

Bonus Chatter

I'm trying to censor some table names. Governments get touchy about that sort of thing. I included OPTION(RECOMPILE) in case anyone tried to hand-wave an answer about cached execution plans. Of course you don't have that in production. duh-doy

Sample scripts

These sample scripts include unrelated columns and indexes:

CREATE TABLE Transactions (
    Column1 varchar(50) NOT NULL,
    RowNumber int NOT NULL,
    Column3 varchar(50) NULL,
    Column4 varchar(50) NULL,
    Column5 varchar(50) NULL,
    Column6 varchar(50) NULL,
    Column7 varchar(50) NULL,
    Column8 varchar(50) NULL,
    Column9 varchar(50) NULL,
    Column10 varchar(50) NULL,
    Column11 varchar(50) NULL,
    Column12 varchar(50) NULL,
    Column13 varchar(50) NULL,
    Column14 varchar(50) NULL,
    TransactionDate datetime NOT NULL,
    Column16 varchar(50) NULL,
    Column17 varchar(50) NULL,
    Column18 varchar(50) NULL,
    Column19 varchar(50) NULL,
    Column20 varchar(50) NULL,
    Column21 varchar(50) NULL,
    Column22 varchar(50) NULL,
    Column23 varchar(50) NULL,
    Column24 varchar(50) NULL,
    Column25 varchar(50) NULL,
    Column26 varchar(50) NULL,
    Column27 varchar(50) NULL
)

CREATE NONCLUSTERED INDEX [IX_Tranasctions_Index1] ON [dbo].[Transactions] (Column7 ASC) INCLUDE (Column12)
CREATE NONCLUSTERED INDEX [IX_Transactions_Index2] ON [dbo].[Transactions] (Column13 ASC)
CREATE NONCLUSTERED INDEX [IX_Transactions_Index3] ON [dbo].[Transactions] (Column5 ASC, TransactionDate ASC) INCLUDE (Column1, Column7, Column11, Column16, Column17, Column18) WHERE (Column5='1')
CREATE NONCLUSTERED INDEX [IX_Transactions_Index4] ON [dbo].[Transactions] (Column11 ASC) INCLUDE (Column7)
CREATE UNIQUE CLUSTERED INDEX [IX_Transactions_RowNumber] ON [dbo].[Transactions] ([RowNumber] ASC)
CREATE NONCLUSTERED INDEX [IX_Transactions_TransactionDate] ON [dbo].[Transactions] (TransactionDate)
CREATE NONCLUSTERED INDEX [IX_Transactions_Index7] ON [dbo].[Transactions] (Column9)
CREATE NONCLUSTERED INDEX [IX_Transactions_Index8] ON [dbo].[Transactions] (Column7, Column8) WHERE (Column7 IS NOT NULL)
CREATE NONCLUSTERED INDEX [IX_Transactions_Index9] ON [dbo].[Transactions] (Column13) INCLUDE (Column7)
ALTER TABLE [dbo].[Transactions] ADD  CONSTRAINT [PK_Transactions] PRIMARY KEY NONCLUSTERED (Column1)

Indexes contain the cluster key

There seems to be some confusion from people who don't really understand how an index works.

This table is unique-clustered by RowNumber. That means that RowNumber uniquely identifies the row.

Lets create a hypothetical Customers table, clustered by CustomerID:

| CustomerID | FirstName | LastName    |
|------------|-----------|-------------|
|          1 | Ian       | Boyd        |
|          2 | Tim       | Biegeleisen |
|          3 | Gordon    | Linoff      |

When it comes time to create a non-clustered covering index, you specify the data you want to index. E.g. for a hypothetical covering index on firstname+lastname:

| FirstName | LastName |
|-----------|----------|

That means that literally the database will store:

|  FirstName | LastName    |
|------------|-------------|
|  Gordon    | Linoff      |
|  Ian       | Boyd        |
|  Tim       | Biegeleisen |

But that's not all it will store. It also has to store the Cluster key value.

Each entry in an index has to point back to the original data-row that the index entry points to.

So internally the index contains another column - the cluster value:

| FirstName | LastName || Cluster key |
|-----------|----------||-------------|

Which in our case in the CustomerID:

|            |             || Cluster key       |
|  FirstName | LastName    || (i.e. CustomerID) |
|------------|-------------||-------------------|
|  Gordon    | Linoff      || 3                 |
|  Ian       | Boyd        || 1                 |
|  Tim       | Biegeleisen || 2                 |

Here's the cool thing: if you had a query that made use of the index, that query can return the cluster key value without having to return to the original full table - because the CustomerID already exists in the index!

SELECT CustomerID FROM Customers WHERE FirstName = 'Ian'

The database can use your covering index to return CustomerID- even though you didn't specify CustomerID in your index. Pretty neat huh?

You can even test it for yourself.

And you can even see it in my original screenshots above (since SQL Server did it when asked). You can also verify it by looking at the showplan:

|--Top(TOP EXPRESSION:((1)))
   |--Index Seek(OBJECT:(Transactions.IX_Transactions_TransactionDate]), 
                 SEEK:(Transactions.[TransactionDate] >= '2019-10-02 04:00:00.000') ORDERED FORWARD)

You can also see it in the statistics i included above:

All density   Average Length Columns
------------- -------------- --------------------------
2.590376E-07  8              TransactionDate
2.19207E-07   12             TransactionDate, RowNumber

You can also see it in the execution plan: return a column from a index when "the index doesn't even contain that column - how can you even call that a covering idnex!?".

Because it covers what we need:

Works fine on 4.2M row table

I went back to an old copy of the database (4.2M rows vs 4.5M today) and:

it correctly uses the covering index CREATE INDEX ... (TransactionDate)
to return the RowNumber
in both cases

So it must be something related to the statistics, and the optimizer.

But in both cases the statistics are up-to-date with FULLSCAN.

This is where i need someone more knowledgeable about the optimizer than I. But since this is just one unanswered query among millions, and people have already down-voted it out-of-hand, i'll never get an explanation:

why for two queries that are logically equivalent
and have everything it needs in an already-sorted index
one chooses to use the index
and the other does a full table scan

The dump of the statistics contains everything the optimizer knows about the data - the answer must be there.

You never gave us the full table structure, including what the clustered and non clustered indices are. — Tim Biegeleisen, Oct 07 '19 at 15:50
@IanBoyd . . . I am quite confused by your use of the term covering index for an index that has one column when the query references two. It is also not clear that the two original query are deterministically known to return the same value. — Gordon Linoff, Oct 07 '19 at 15:51
The 2 queries are very different. if I asked you to get the "lowest" value phone number from the phone book, which is ordered by customer name, and the phone number for the first customer in the phone book (ordered by customer), what would you do faster? — Thom A, Oct 07 '19 at 15:52
Perhaps you're assuming that because `TransactionDate` and `RowNum` correlate and that ordering your data in the table by either will give you the **exact** same order, you think that SQL Server "knows" that too? Without an Index to tell it, SQL Server has no idea what the order of the data in a table is. Just because *you* know the order of your data does not mean SQL Server does. It can't infer that information. — Thom A, Oct 07 '19 at 15:56
@Larnu It depends. In the one case i would jump to the name i was interested in. In the other case i would jump to the name i was interested in. — Ian Boyd, Oct 07 '19 at 16:46
@TimBiegeleisen If you like, i can create a `CREATE TABLE` that adds 25 unrelated columns, and 9 other indexes on unrelated columns. But since none of those columns are referenced, it doesn't add, to detract, from the question. — Ian Boyd, Oct 07 '19 at 16:48
Yes, *you* could, @IanBoyd, but SQL Server can't as the `INDEX` you create in your question isn't covering. You've told it the order of the customers, but you haven't told it what the phone numbers are for those customers, so the order of them is effectively useless to it. — Thom A, Oct 07 '19 at 16:59
@Larnu The index i've created *is* covering. Look at the `TOP(1)` case. Using only the index `CREATE INDEX IX ON Transactions (TransactionDate)` the cluster key is being returned by the covering index - even though it doesn't explicitly call out the cluster key. Try it! I'll add the screenshot of the execution plan showing it outputing the row that you don't believe it can output. — Ian Boyd, Oct 07 '19 at 17:12
@IanBoyd, no, it isn't. `CREATE INDEX IX_Transactions_TransactionDate ON Transactions (TransactionDate)` does not reference `TransactionNumber` or `RowNumber`; therefore is useless in a query that references those columns. — Thom A, Oct 07 '19 at 17:14
@Larnu Look again. The index `CREATE INDEX IX_Transactions_TransactionDate ON Transactions (TransactionDate)` doesn't *reference* the RowNumber, but it *does* contain it. Look at the statistics above. Look at the execution plan. SQL Server **is** using that index alone to return RowNumber. — Ian Boyd, Oct 07 '19 at 17:31
Note, that my comments were all based on a lack of knowledge of what the `CLUSTERED INDEX` was. On it's own, that index does not contain `RowNumber`. — Thom A, Oct 07 '19 at 18:20

David Browne - Microsoft · Accepted Answer · 2019-10-07T18:30:47.560

4

Even though RowNumber, the clustered index key, is a key value on IX_Transactions_TransactionDate, the index keys are ordered first by TransactionDate, then by RowNumber. The MIN(RowNumber) may not be on the first row with TransactionDate >= '20191002 04:00:00.000'.

Consider if the IX_Transactions_TransactionDate contained the key values:

(20191002 04:00:00.000,10),
(20191002 05:00:00.000,11),
(20191002 06:00:00.000,1)

The result of

SELECT MIN(RowNumber) FROM FintracTransactions WHERE TransactionDate >= '20191002 04:00:00.000' OPTION(RECOMPILE)

is 1. While the result of:

SELECT TOP(1) RowNumber FROM FintracTransactions WHERE TransactionDate >= '20191002 04:00:00.000' ORDER BY TransactionDate OPTION(RECOMPILE)

is 10.

So the optimizer's real choice is to scan every value from IX_Transactions_TransactionDate after the target date, or to scan the clustered index from the beginning until it finds the first row with a qualifying TransactionDate.

You should see that the execution plan for:

SELECT MIN(RowNumber) FROM [Transactions] with (index=[IX_Transactions_TransactionDate]) WHERE TransactionDate >= '20191002 04:00:00.000' OPTION(RECOMPILE)

has a higher estimated cost than the execution plan for:

SELECT MIN(RowNumber) FROM [Transactions]  WHERE TransactionDate >= '20191002 04:00:00.000' OPTION(RECOMPILE)

edited Oct 07 '19 at 18:30

answered Oct 07 '19 at 18:02

David Browne - Microsoft

80,331
6
39
67

Note that even though your business logic may "guarantee" that the RowNumber data tracks along with the transaction date - the query optimizer doesn't 'know' that and treats them as an independent value. – Rawheiser Oct 07 '19 at 18:09
The question still is: why is the optimizer not using the covering index in **both** cases? And why on a 3 month old copy does it **use** the covering index for both? – Ian Boyd Oct 07 '19 at 18:14
Probably because SQL Server _estimates_ that it would be cheaper, based on the limited information and compute time available during query compilation. See updated answer. – David Browne - Microsoft Oct 07 '19 at 18:19
The original query estimates the clustered index scan will produce 1 row (subtree: 0.01, I/O: 116, CPU: 5.02). The one forced to use the index is estimated to produce 16k rows (subreee: 0.04, I/O: 0.03, CPU: 0.02). The one forced to use the index instantly returns, producing 16k rows to the aggregate operator. The original un-hinted query takes 157006 logical reads. Is this a case where statistics mean absolutely nothing? The server is going to start scanning and **hope** it **happens** to hit a matching row sooner rather than later? – Ian Boyd Oct 07 '19 at 18:31
1

The statistics don't really say much about the correlation between the values, so, yes, it's going to start scanning and hope to hit a matching row. You can cut the cost of this scan by providing a nonclustered index on (RowNumber,TransactionDate). – David Browne - Microsoft Oct 07 '19 at 18:37