SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Scan vs Index Seek


Index Scan vs Index Seek

Author
Message
Andrew Diniz
Andrew Diniz
Mr or Mrs. 500
Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)

Group: General Forum Members
Points: 552 Visits: 293
While troubleshooting a poor performing query this morning I came across something which surprised me - actually, two things:

1) one of the tables being queried defined no primary key or clustered index
2) the optimiser chose an index seek over an index scan for the query

For argument's sake, let's put aside the first shocking point for now. In fact, look past all the poor design decisions. It's the optimiser's behaviour in the given scenario which interests me.

The heap being queried comprises ~9.5M records in a single partition occupying ~36GB of space or ~5M pages (it's a wide denormalised table). A non-clustered index exists on a char(1) column called 'TransType' with density of 0.25:


RANGE_HI_KEY EQ_ROWS
9,581,682
A 264
H 263
X 98


It also defines 4 INCLUDED fields: PolNo, PolSeq, PolYear, PolMonth and occupies 3 levels over 41,482 pages.

Finally, here's the portion of the query which surprised me:


SELECT
PolNo,
PolSeq,
PolYear,
PolMonth
FROM
dbo.PolHistory
WHERE
TransType = ' '; --41,575 logical reads




The resulting plan consists of a single Index Seek operator. The index stats utilised a FULLSCAN and are up-to-date.
Sure, the index is covering, but given the value being seeked has such poor selectivity, why isn't a scan of the leaf nodes chosen over 9.5M traversals the index structure?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223197 Visits: 46294
The index is covering, so one seek operation to the start of the range and a partial scan is all that's necessary.

It's not traversing the index 9.5 million times. If it was, the logical reads would be ~27 million. It's traversing the index tree once, reaching the start of the range of rows that match then reading along the index leaf level until it's got all the rows. It's more efficient than a scan as it doesn't have to read the (few) rows that don't match.

p.s. Please post new questions in a new thread in future. Thanks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Andrew Diniz
Andrew Diniz
Mr or Mrs. 500
Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)

Group: General Forum Members
Points: 552 Visits: 293
GilaMonster (10/16/2012)
The index is covering, so one seek operation to the start of the range and a partial scan is all that's necessary.

It's not traversing the index 9.5 million times. If it was, the logical reads would be ~27 million. It's traversing the index tree once, reaching the start of the range of rows that match then reading along the index leaf level until it's got all the rows. It's more efficient than a scan as it doesn't have to read the (few) rows that don't match.

p.s. Please post new questions in a new thread in future. Thanks


I followed this up with a private message to Gail as follows:


Thanks Gail

That makes a lot of sense.

Are there instances when cardinality does play a part in the optimiser's choice of 'scan vs seek' in the presence of a covering index and all SARGable predicates?

Thanks again

Andy


To which she answered:


No.

The only reason cardinality can flip a seek to a scan of the cluster is because of the cost of the key lookups. A covering index doesn't need key lookups and so there's no reason at all to switch to a more expensive scan.

p.s. can you post your PM and my reply in the forum thread, for anyone who encounters it via google


domalti
domalti
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 11
This blog http://www.crazyfrog.me/2013/06/index-seek-vs-scan-microsoft-sql-2008.html has detailed explanation of Index seek and scan.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223197 Visits: 46294
domalti (6/11/2013)
This blog http://www.crazyfrog.me/2013/06/index-seek-vs-scan-microsoft-sql-2008.html has detailed explanation of Index seek and scan.


Not sure I'd call a bunch of pictures with no descriptions or explanations 'detailed', but that's just me.

p.s. 6 year old thread

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Steven Willis
Steven Willis
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2019 Visits: 1721
Gail

I think this is on-topic and you seem to know as much (or more) than anyone on the subject of index behavior that I'd like to ask a question that I've posted before but no one has ever had a definitive answer.

I notice that on many queries, depending on the makeup of the WHERE clause, that SQL will be doing Index Scans that no amount of fiddling around seems to help. I see this most often when I'm returning all rows with no WHERE clause at all (which sort of makes sense).

But I can add an artificial filter like 'WHERE primarykey > 0" and that seems to force the compiler into doing an Index Seek on that key. I've wondered about this for years now. Is this really changing the execution of the query in any significant way? Or is it just a 'phantom" effect of some kind?

I've attached two Execution Plan files that were run against the same table to give an example of this behavior.

 
Attachments
QueryWithoutWhereClause.sqlplan (6 views, 5.00 KB)
QueryWithWhereClause.sqlplan (5 views, 6.00 KB)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223197 Visits: 46294
Steven Willis (6/12/2013)
But I can add an artificial filter like 'WHERE primarykey > 0" and that seems to force the compiler into doing an Index Seek on that key. I've wondered about this for years now. Is this really changing the execution of the query in any significant way? Or is it just a 'phantom" effect of some kind? 


http://sqlinthewild.co.za/index.php/2009/03/05/when-is-a-seek-actually-a-scan/
Also discussed in a chapter in "Pro SQL Server 2012 Best Practices"

Please in future post new questions in a new thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search