Click here to monitor SSC
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
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47181 Visits: 44356
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
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 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 (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47181 Visits: 44356
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
SSC-Addicted
SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)

Group: General Forum Members
Points: 477 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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47181 Visits: 44356
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