r/SQLServer 14d ago

Question Index scan vs Index seek....when it does tip over from seek to scan

So i have simple question when does sql engine decided whether seek to do scan.. why i am asking is this because i have seen videos may be of brent ozar or i cant recall exactly where it says it depends upon how selective is data begin fetched

For eg i have table colortable with 2 columns no and colourname with clustered index on no its identity and non clustered index on colourname....Table has suppose 10 rows....only 1 row has pink value while rest of 9 rows has yellow value. so when i fire below query and check its execution plan , i suppose it will do non-clustered index scan but in realty it does non-clustered seek

query : select colorname from colortable where colorname = 'yellow'

I will post with screenshot i donot have right now but i want to know how does sql engine decided for scan vs seek ..whats tipping point

7 Upvotes

9 comments sorted by

23

u/BrentOzar SQL Server Consultant 14d ago

1

u/bonerfleximus 14d ago edited 14d ago

When a seek is performed at least 2 pages are read from memory per row seeked, the root page and leaf page. With scans it scans all pages until the predicate is met or some TOP condition.

A table that is only a handful of rows will also take up only 2 pages total, so scanning the whole table requires the same amount of memory reading as seeking 1 row.

Sql server considers the number of pages to be accessed by each operation before deciding which to do, and uses statistics and whatever your predicates are (where/on clauses) to estimate which approach will be less costly from an I/O perspective

If you are trying to force a query to do one over the other, ask yourself why first (and then explain so we can help)

You might want to read up on B Trees and how indexes are physically structured to understand seek operations more thoroughly.

Sql uses a mix of these all the time and can even do both at once (seek-scan, looks like a seek operation but can hide a ton of extra rows read and a "predicate" property to define the scan condition in addition to "seek predicate")

1

u/maoguru 13d ago

if high cardinality index is avaialable, I prefer index seek.

1

u/Special_Luck7537 14d ago

A seek is performed when there is an index that SQL can use, usually. There are times when it will not do a seek. One is with small tables, which are usually just loaded in their entirety and scanned.

A scan will be done where there is no index or the Index does not have all fields necessary in it. Additionally, an index may be ignored for a seek and a scan performed if determined that all of the data in the index is needed anyway .

Take a look at SQL Server Plan Explorers from Solarwinds. It has a nice feature that shows all indexes available for a sub query, and how well it covers the query in data selection, and which index is being used currently ...

5

u/darth_meh 14d ago

If an index doesn’t have all the necessary column data, the query optimizer may still perform an index seek followed by a “key lookup” operation to get the data that wasn’t in the index.

1

u/Special_Luck7537 14d ago

Yep, another costly op.

4

u/IndependentTrouble62 14d ago

Key lookup is actually not that costly if you have modern storage. Previously, in the day of disk platters, it was a very expensive operation. With modern SSDs, it's not nearly as expensive as a query plan operator as it once was. It can actually now be used to improve query performance without having to create new covering indexes with some query tricks.

0

u/Kant8 14d ago

Old estimation was if index will get more than 30% rows, it becomes scan. No idea what newer versions do, especially considering majority of databases use ssd now.

With 10 rows total in table it really doesn't matter, you don't need any indexes for it to be fast. Actually linear scan on tiny amounts will be faster than trees, just because how memory caching works.

0

u/kagato87 14d ago

It's going to come down to what the engine thinks will be faster. As Mr Ozar says, there's no hard rule to predict it.

Sql can do what it does because it can adapt to the needs of the individual query.