Post reply

Introduction to Indexes

  • October 26, 2009 at 10:08 am

    #1070801

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

  • 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?

  • 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.

  • October 26, 2009 at 12:01 pm

    #1070853

    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

  • October 26, 2009 at 2:13 pm

    #1070917

    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

  • October 26, 2009 at 2:19 pm

    #1070921

    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

  • October 26, 2009 at 2:40 pm

    #1070935

    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?

  • October 26, 2009 at 2:53 pm

    #1070942

    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

  • October 26, 2009 at 3:04 pm

    #1070948

    Thanks for the clarification. That's pretty handy.

  • October 26, 2009 at 3:18 pm

    #1070951

    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.

  • October 26, 2009 at 3:25 pm

    #1070954

    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.

  • October 26, 2009 at 5:00 pm

    #1070980

    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]

  • October 26, 2009 at 5:42 pm

    #1070986

    Me Too Gail. Thanks very much.

    Steve B.

  • October 27, 2009 at 2:09 am

    #1071059

    Gail

    I found your article very interesting and useful. It is good timing for me too as I need to research deeper into the area of indexes.

    Looking forward to the next 2 articles.

    Regards

    Graham Goodwin

  • October 27, 2009 at 3:07 am

    #1071070

    Great article!

    Waiting for part2.

    -Vikas Bindra

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

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