Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Index Scan vs Index Seek Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 3:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:11 AM
Points: 216, Visits: 273
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?

Post #1373091
Posted Tuesday, October 16, 2012 4:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 41,530, Visits: 34,447
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 2008, MVP
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

Post #1373110
Posted Tuesday, October 16, 2012 5:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:11 AM
Points: 216, Visits: 273
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

Post #1373177
Posted Tuesday, June 11, 2013 2:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 17, 2013 3:57 AM
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.
Post #1461920
Posted Tuesday, June 11, 2013 2:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 41,530, Visits: 34,447
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 2008, MVP
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

Post #1461923
Posted Wednesday, June 12, 2013 8:53 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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.

 


  Post Attachments 
QueryWithoutWhereClause.sqlplan (1 view, 5.95 KB)
QueryWithWhereClause.sqlplan (1 view, 6.82 KB)
Post #1462679
Posted Wednesday, June 12, 2013 8:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 41,530, Visits: 34,447
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 2008, MVP
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

Post #1462682
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse