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 ««12345»»»

Introduction to Indexes Expand / Collapse
Author
Message
Posted Monday, October 26, 2009 8:50 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
Thanks for the article, Gail.

"When an index scan is done on the clustered index, it’s a table scan in all but name."

Then why do we see index scan and table scan separately in execution plan rather just table scan on a single query with simple join?

Would wait for part 2 and 3.


SQL DBA.
Post #808748
Posted Monday, October 26, 2009 8:55 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 4,469, Visits: 6,398
SanjayAttray (10/26/2009)
Thanks for the article, Gail.

"When an index scan is done on the clustered index, it’s a table scan in all but name."

Then why do we see index scan and table scan separately in execution plan rather just table scan on a single query with simple join?

Would wait for part 2 and 3.


I don't follow that one Sanjay. Can you post a plan with what you are meaning and what the issue with that plan is?


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #808755
Posted Monday, October 26, 2009 9:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 6, 2014 7:59 AM
Points: 12, Visits: 207
Thanks Gail! This has helped my understanding on this topic. I have posted the article on an internal wikipedia as my Manager was very interested in the topic as well. This was very well written and I look forward to the next parts.
Thanks again!
Rob
Post #808766
Posted Monday, October 26, 2009 9:13 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 @ 9:13 AM
Points: 40,609, Visits: 37,070
SanjayAttray (10/26/2009)
"When an index scan is done on the clustered index, it’s a table scan in all but name."

Then why do we see index scan and table scan separately in execution plan rather just table scan on a single query with simple join?


Possibly because they're against different indexes or different tables?

What I'm saying there is that a clustered index scan is virtually the same as a table scan. You won't see the table scan operation against a table that has a clustered index. It appears in the execution plan as a clustered index scan. That doesn't make it any more efficient than a table scan.



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 #808771
Posted Monday, October 26, 2009 9:19 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 820, Visits: 2,025
Excellent article. This coupled with Brad's article is very helpful in design considerations. I was especially looking for the size of the row pointer. It's quite a consideration for the choice of a clustered key. For example If I don't have a clustered index I get 8 byte pointers. If I used an INTEGER row ID (auto number) I get 4 byte pointers. If I use a US phone number as my clustered key I get 10 byte pointers. (I use data compression techniques).

You said:
Sure, hard drives are cheap and storage is abundant but increasing the size of a database has other effects,
But that is not so true for those of us working on the Mobile. Every byte is precious.

I'm looking forward to the other installments.


ATB

Charles Kincaid

Post #808779
Posted Monday, October 26, 2009 10:08 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, June 8, 2012 12:30 AM
Points: 150, Visits: 3,892
I agree, excellent article Gail. I'll also be looking for the follow-up articles. -Carleton
Post #808820
Posted Monday, October 26, 2009 11:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:02 PM
Points: 1,414, Visits: 4,545
is there any hard documentation on what the tipping point is for an index seek to become an index scan? is it 5% of rows affected, 10% or some other percentage?

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #808885
Posted Monday, October 26, 2009 11:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 25, 2010 6:40 AM
Points: 46, Visits: 133
Thanks, Gail. Excellent nuts and bolts foundation. Perhaps you could cover statistics one day and how they relate to indexes.
Post #808887
Posted Monday, October 26, 2009 12:01 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 4,469, Visits: 6,398
Carleton (10/26/2009)
I agree, excellent article Gail. I'll also be looking for the follow-up articles. -Carleton


I believe this is somewhat dependent on the index and pages therein, but it has been shown here on sqlservercentral.com that the percentage is VERY low, typically on the order of 1% or so of the total rows.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #808891
Posted Monday, October 26, 2009 2:13 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 @ 9:13 AM
Points: 40,609, Visits: 37,070
SQL Noob (10/26/2009)
is there any hard documentation on what the tipping point is for an index seek to become an index scan? is it 5% of rows affected, 10% or some other percentage?


It usually tips somewhere between 0.5%-1%. It's not a hard-coded value, it depends on a whole bunch of factors, but it is a very, very low percentage.
This is covered in either part 2 or 3, there's also a post on my blog - http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/



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 #808968
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse