Getting Index Scan instead of a possible Index Seek?

Question

Currently learning a few things on query optimization, and I've been trying out different queries and stumbled upon this "issue".

I am using the AdventureWorks2014 database, I ran this simple query:

table structure (taken from https://www.sqldatadictionary.com/AdventureWorks2014.pdf):

SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 100

returns 19,720 rows

total number of rows in Sales.Customer = 19,820

And after checking to make sure CustomerID is in fact not just the PK of the table but also has a clustered index on it (yet it uses a non clustered index), that indeed is the case:

EXEC SP_HELPINDEX 'Sales.Customer'

Here is the execution plan ↓

https://www.brentozar.com/pastetheplan/?id=B1g1SihGr

I have read that when faced with huge amounts of data and/or when it returns more than 50% of the data set, the query optimizer will favor an index scan. But that table as a whole barely has 20,000 rows (19,820 to be exact), it's not a big table by any means.

When I run this query:

SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 30000

returns 118 rows

https://www.brentozar.com/pastetheplan/?id=Byyux32MS

I get an index seek instead, so I thought it was due to that "more than 50% case" however, I also ran this query:

SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 20000

returns 10,118 rows

https://www.brentozar.com/pastetheplan/?id=HJ9oV33zr

And it also used an index seek, even though it was returning more than 50% of the data set.

So what's going on here?

EDIT:

With IO Statistics turned on, the >100 query returns:

Table 'Customer'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

While the >20,000 one returned:

Table 'Customer'. Scan count 1, logical reads 65, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So added WITH(FORCESCAN) option to the >20,000 one to see what would happen:

Table 'Customer'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So it ends up running better with an Index Scan (less logical reads), even though the query optimizer chose to run an Index Seek for this particular query.

Answer 1

You use a nonequality predicate so your "seek" operations are in fact scans which just start from some value (not from "first") and then go to the end of the clustered index leaf level.

In the other hand you return only one column which is the clustered index key so using any of indexes won't get any key lookup operations. The optimizer has to estimate what would be cheaper: scanning a nonclustered index (two int columns on the leaf level) or part scanning of your clustered index (all columns on the leaf level).

It estimates it depending on current statistics (how many rows) and metadata (what is one row size). We saw the optimizer made a mistake on >20,000 predicate.

When faced with huge amounts of data and/or when it returns more than 50% of the data set, the query optimizer will favor an index scan.

That is a fact when the optimizer has to choose performing clustered index or table scan versus nonclustered index seek + key lookups.

In your case if your index on CustomerID were nonclustered you would always see a seek operation on that index, but if you then added another column to your output you would see the index seek + RID lookups on short resultsets and table scan on the big ones.

Answer 2

In Cost base Optimization,Optimizer find best possible execution on given time which is cost effective.

When we check the index size of each index in this table,

SELECT
i.name AS IndexName,
SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
db_id(), object_id('Sales.Customer'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.object_id = i.object_id AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name;



IX_Customer_TerritoryID || 288
PK_Customer_CustomerID || 976

So clearly Index size of IX_Customer_TerritoryID is far less than PK_Customer_CustomerID.

Compare the cost of both query,

SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 100

SELECT C.CustomerID
FROM Sales.Customer AS C WITH(INDEX(PK_Customer_CustomerID))
WHERE C.CustomerID > 100

I/O cost of query with index IX_Customer_TerritoryID is less than that of PK_Customer_CustomerID.

Answer 3

The optimizer uses whatever it thinks is fastest; a lot of times what I'd expect it to do, it doesn't. It's not just based on row %; it's based on lots of factors like the statistics it has, indexes, the table columns and query itself. Uses that to create cost and threshold estimates, although number of rows does come into play.

I'd guess it scanned the first query because of the factors aforementioned, namely statistics. It did index seek on the second for the same reason. The third seek might have just been because plan was already compiled in memory. I'm curious if it would have scanned for that if you tried it with recompile like @scsimon suggested.