Post reply

Introduction to Indexes: Part 3 – The nonclustered index

  • July 23, 2012 at 6:14 pm

    #1516539

    Nice series Gail.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • June 8, 2013 at 4:00 pm

    #1622252

    This was so interesting 😮 went through it along with the simple-talk tracing articles & just finished (slow reader -_). I'm gonna read through your other articles tomorrow (or as far through as I can get) unless someone here has a good TV show to recommend 😛 a few questions, in the article you said:

    GilaMonster (7/6/2012)

    If SQL considers the index (or the subset of the index keys that the query would be seeking on) insufficiently selective then it is very likely that the index will be ignored and the query executed as a clustered index (table) scan.

    It is important to note that this does not just apply to the leading column. There are scenarios where a very unselective column can be used as the leading column, with the other columns in the index making it selective enough to be used.

    In these "scenarios" the result would then be a non-clustered full scan right?

    GilaMonster (7/6/2012)

    Sure, the clustering key will be there (though whether or not it can be used for seeks depends on a property of the index, specifically unique)

    So it will only allow you to seek on a clustering key if the key is unique?

    GilaMonster (7/6/2012)

    Plus creating and dropping indexes will cause plan recompilation, extensive locks, etc.

    I guess the locks are only relevent for the creating rather than the dropping right? (since I guess SQL wouldn't attempt to make new queries use an index while its in the process of being dropped)

    You also talked about the fillfactor & page splits in the thread and how 80% would cause more I/O reads as a result of there being more pages. How do you personally approach index fill factors? I guess the cost of excessive page splits & the desire to avoid them would outweigh the desire to minimise the number of pages (by settings 100%)?

    Seems 2.5 months is inadequate study time for MSSQL...still learning about the fundamentals of indexes T_T

    Dird

  • June 9, 2013 at 3:58 am

    #1622269

    Dird (6/8/2013)

    This was so interesting 😮 went through it along with the simple-talk tracing articles & just finished (slow reader -_). I'm gonna read through your other articles tomorrow (or as far through as I can get) unless someone here has a good TV show to recommend 😛 a few questions, in the article you said:

    GilaMonster (7/6/2012)

    If SQL considers the index (or the subset of the index keys that the query would be seeking on) insufficiently selective then it is very likely that the index will be ignored and the query executed as a clustered index (table) scan.

    It is important to note that this does not just apply to the leading column. There are scenarios where a very unselective column can be used as the leading column, with the other columns in the index making it selective enough to be used.

    In these "scenarios" the result would then be a non-clustered full scan right?

    Scenarios when the leading column plus other columns make the index selective enough to use? No, that'll be an index seek.

    GilaMonster (7/6/2012)

    Sure, the clustering key will be there (though whether or not it can be used for seeks depends on a property of the index, specifically unique)

    So it will only allow you to seek on a clustering key if the key is unique?

    No. Seeks on the clustered index itself don't require the cluster to be unique. You can get a seek on a clustered index even if there's only one value in the entire table.

    The part you're quoting pertains to a very specific situation with multiple columns in index.

    GilaMonster (7/6/2012)

    Plus creating and dropping indexes will cause plan recompilation, extensive locks, etc.

    I guess the locks are only relevent for the creating rather than the dropping right? (since I guess SQL wouldn't attempt to make new queries use an index while its in the process of being dropped)

    Dropping an index takes a schema modification lock, like any changes to database structures.

    How do you personally approach index fill factors?

    Evaluate on a per-index basis.

    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

Viewing 3 posts - 91 through 92 (of 92 total)

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