Introduction to Indexes

Question

Post reply

Introduction to Indexes

Viewing 15 posts - 16 through 30 (of 124 total)

You must be logged in to reply to this topic. Login to reply

Carleton SSCommitted Points: 1706 More actions · Answer 1

Carleton

SSCommitted

Points: 1706

October 26, 2009 at 10:08 am

#1070801

I agree, excellent article Gail. I'll also be looking for the follow-up articles. -Carleton

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 2

alen teplitsky

SSC-Dedicated

Points: 30014

October 26, 2009 at 11:49 am

#1070848

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?

jbuttery Ten Centuries Points: 1206 More actions · Answer 3

jbuttery

Ten Centuries

Points: 1206

October 26, 2009 at 11:57 am

#1070850

Thanks, Gail. Excellent nuts and bolts foundation. Perhaps you could cover statistics one day and how they relate to indexes.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

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 on googles mail service

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

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, 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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

jbuttery (10/26/2009)

Perhaps you could cover statistics one day and how they relate to indexes.

Next week Thursday, 4pm Seattle time.

http://summit2009.sqlpass.org/Agenda/SpotlightSessions/Liesdamnedliesandstatistics.aspx

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

lharmes Valued Member Points: 61 More actions · Answer 7

Thanks for this concise summary.

I note that you suggest using complex keys for the non-clustered indexes. Would I be correct in assuming that, in order for SQL to use those indexes efficiently, my query would have to look for the combination of columns equalling some value? In other words, if my key is lastname + firstname, my query would need to be SELECT * FROM Names WHERE LastName + FirstName LIKE 'Smith%'. Is that right?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

lharmes (10/26/2009)

In other words, if my key is lastname + firstname, my query would need to be SELECT * FROM Names WHERE LastName + FirstName LIKE 'Smith%'. Is that right?

Nope.

If you have an index defined on Names (LastName, FirstName) then queries of either of these forms would be able to seek

WHERE LastName = 'Smith' AND FirstName = 'John'

WHERE LastName = 'Smith'

Queries of this form would not be able to seel

WHERE FirstName = 'John'

because FirstName is not the leading column of an index.

As for concatenating columns together, doing that prevents index seeks at all, regardless of what indexes exist.

WHERE LastName + FirstName LIKE 'Smith%' is not a SARGable predicate.

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

lharmes Valued Member Points: 61 More actions · Answer 9

lharmes

Valued Member

Points: 61

October 26, 2009 at 3:04 pm

#1070948

Thanks for the clarification. That's pretty handy.

dyfhid SSC Enthusiast Points: 160 More actions · Answer 10

Wow, Gail, thank you so much for that, can't wait for parts 2 and 3!

This came right on the heels of G. Fritchey's presentation regarding Execution Plans at Quest Connect last week, and your article helped click switches in my brain to make the connection between indexes and Execution plans, which will, in turn, help me learn about performance issues. I'm becoming less of a noob each day, thanks to good folks like you willing to share your knowledge.

Nicole Bowman SSCommitted Points: 1555 More actions · Answer 11

Thank you, thank you, thank you Gail! You write so clearly. I have read articles/chapters in books on this topic before and was always a little confused. Not any more. I am really looking forward to parts 2 and 3. I have begun on my journey towards being able to query tune a little more scientifically!

Cheers!

Nicole Bowman

Nothing is forever.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

Great stuff, Gail! And I'm sure that you'll knock'em dead in Seattle next week, good luck... 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]