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

Help On Execution Plan Status Expand / Collapse
Author
Message
Posted Friday, November 30, 2012 7:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:28 AM
Points: 268, Visits: 680
Hi All,

I am a newbie in SQL Server and like exploring it. Currently i was working with some complex queries and happen to see there Execution Plan. I saw that there were some Clustered index which were 'ClusteredIndexSeek' and Some Clustered Index as 'ClusteredIndexScan'.

So my basic question is, how does the query analyzer decide when to used ClusteredIndexScan and when to used ClusteredIndexSeek.
Post #1391318
Posted Friday, November 30, 2012 8:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 175, Visits: 547
Paul White's blog series will pretty much any question you could ever think of on this.
http://www.sqlservercentral.com/articles/SQL+Server+2008/71019/




For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1391333
Posted Friday, November 30, 2012 8:12 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 @ 1:13 PM
Points: 40,428, Visits: 36,879
Seek when there's a SARGable predicate (<column> <comparison operator> <expression>) on the index key columns. Scan if there isn't. Very, very simplified, but that's the basics.


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 #1391351
Posted Friday, November 30, 2012 9:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 14,029, Visits: 28,404
And the indexes have to be selective enough to be useful for a seek or you'll get scans. Same things apply to non-clustered indexes too.

For lots more detail on query tuning and execution plans, take a look at my books (listed below).


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1391418
Posted Friday, November 30, 2012 12:28 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:31 PM
Points: 2,268, Visits: 3,427
Grant Fritchey (11/30/2012)
And the indexes have to be selective enough to be useful for a seek or you'll get scans. Same things apply to non-clustered indexes too.

For lots more detail on query tuning and execution plans, take a look at my books (listed below).



Not for clustered indexes. SQL can do a seek on a clustered index even if you're SELECTing 99% of the rows.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1391499
Posted Friday, November 30, 2012 12:49 PM


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 @ 1:13 PM
Points: 40,428, Visits: 36,879
ScottPletcher (11/30/2012)
Grant Fritchey (11/30/2012)
And the indexes have to be selective enough to be useful for a seek or you'll get scans. Same things apply to non-clustered indexes too.

For lots more detail on query tuning and execution plans, take a look at my books (listed below).



Not for clustered indexes. SQL can do a seek on a clustered index even if you're SELECTing 99% of the rows.


More correctly, not for any index that covers the query. A covering nonclustered index can and will be used for a seek up to 100% of the rows.



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 #1391515
Posted Saturday, December 1, 2012 10:38 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 9,928, Visits: 11,196
Shadab Shah (11/30/2012)
So my basic question is, how does the query analyzer decide when to used ClusteredIndexScan and when to used ClusteredIndexSeek.

It will often consider both alternatives. It estimates the cost of each and chooses the one that appears cheapest (according to the model it uses). There are a number of detailed factors that affect the cost calculation, and overall plan selection, but that's a basic answer to your basic question.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1391700
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse