Post reply

index scan where seek expected

  • October 22, 2018 at 3:56 pm

    #2010702

    ScottPletcher - Monday, October 22, 2018 11:25 AM

    jontris - Monday, October 22, 2018 10:21 AM

    Thanks Grant but I'm going to be bold and say that there is no rationale, and the optimiser is just getting it wrong this time. This should not astonish us too much. What is perhaps more astonishing is how much it gets it right.

    The optimizer is doing the best it can.  I really think to genuinely address this issue, you must cluster the table on the best column(s).  Otherwise you will stay in a cycle of issues with this table, queries working well for a while, then suddenly having issues again.

    I hope you're not saying that ColA and ColB would make the best CI in this case.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 22, 2018 at 4:05 pm

    #2010704

    Jeff Moden - Monday, October 22, 2018 3:56 PM

    ScottPletcher - Monday, October 22, 2018 11:25 AM

    jontris - Monday, October 22, 2018 10:21 AM

    Thanks Grant but I'm going to be bold and say that there is no rationale, and the optimiser is just getting it wrong this time. This should not astonish us too much. What is perhaps more astonishing is how much it gets it right.

    The optimizer is doing the best it can.  I really think to genuinely address this issue, you must cluster the table on the best column(s).  Otherwise you will stay in a cycle of issues with this table, queries working well for a while, then suddenly having issues again.

    I hope you're not saying that ColA and ColB would make the best CI in this case.

    If he (almost) always searches by ColA and/or ( ColA, ColB ), then it most likely would.  Would need more details to be sure that's OK, but it typically is.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • October 22, 2018 at 8:06 pm

    #2010713

    ScottPletcher - Monday, October 22, 2018 4:05 PM

    Jeff Moden - Monday, October 22, 2018 3:56 PM

    ScottPletcher - Monday, October 22, 2018 11:25 AM

    jontris - Monday, October 22, 2018 10:21 AM

    Thanks Grant but I'm going to be bold and say that there is no rationale, and the optimiser is just getting it wrong this time. This should not astonish us too much. What is perhaps more astonishing is how much it gets it right.

    The optimizer is doing the best it can.  I really think to genuinely address this issue, you must cluster the table on the best column(s).  Otherwise you will stay in a cycle of issues with this table, queries working well for a while, then suddenly having issues again.

    I hope you're not saying that ColA and ColB would make the best CI in this case.

    If he (almost) always searches by ColA and/or ( ColA, ColB ), then it most likely would.  Would need more details to be sure that's OK, but it typically is.

    The trouble is that few people consider the number of page splits, physical fragmentation, logical fragmentation, and the massive effect that the crazy number of page splits will have on the log file that such keys for a CI will cause.  Here's an example of such a keyed CI.  It's a train wreck that needs to be rebuilt quite often.  Of course, that causes even more log file action.  Good luck if you doing log shipping or any other type of replication that uses the log file.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 23, 2018 at 2:49 am

    #2010726

    Thanks again for the responses. I can't re-cluster the table as it's part of a third-party package and already has a surrogate (clustered) primary key. Also the index in question is very far from the most used. I'm wondering why it's so shocking to suggest that the optimiser might be getting it wrong on this occasion. It is after all only a program, and programs have bugs.

  • October 23, 2018 at 10:13 am

    #2010809

    jontris - Tuesday, October 23, 2018 2:49 AM

    Thanks again for the responses. I can't re-cluster the table as it's part of a third-party package and already has a surrogate (clustered) primary key. Also the index in question is very far from the most used. I'm wondering why it's so shocking to suggest that the optimiser might be getting it wrong on this occasion. It is after all only a program, and programs have bugs.

    Typically you can still re-cluster it.  Retain the original PK, but just make it non-clus.  If the new clus works, then you can drop any non-clus indexes that have the same leading key(s).  Of course you'd have to check with vendor on this: some will work with you, some won't.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • October 23, 2018 at 10:25 am

    #2010811

    ScottPletcher - Tuesday, October 23, 2018 10:13 AM

    jontris - Tuesday, October 23, 2018 2:49 AM

    Thanks again for the responses. I can't re-cluster the table as it's part of a third-party package and already has a surrogate (clustered) primary key. Also the index in question is very far from the most used. I'm wondering why it's so shocking to suggest that the optimiser might be getting it wrong on this occasion. It is after all only a program, and programs have bugs.

    Typically you can still re-cluster it.  Retain the original PK, but just make it non-clus.  If the new clus works, then you can drop any non-clus indexes that have the same leading key(s).  Of course you'd have to check with vendor on this: some will work with you, some won't.

    Heh.... but first, see my response with the graph for why you shouldn't even consider clustering on the two columns we're talking about. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 23, 2018 at 10:52 am

    #2010822

    Jeff Moden - Tuesday, October 23, 2018 10:25 AM

    ScottPletcher - Tuesday, October 23, 2018 10:13 AM

    jontris - Tuesday, October 23, 2018 2:49 AM

    Thanks again for the responses. I can't re-cluster the table as it's part of a third-party package and already has a surrogate (clustered) primary key. Also the index in question is very far from the most used. I'm wondering why it's so shocking to suggest that the optimiser might be getting it wrong on this occasion. It is after all only a program, and programs have bugs.

    Typically you can still re-cluster it.  Retain the original PK, but just make it non-clus.  If the new clus works, then you can drop any non-clus indexes that have the same leading key(s).  Of course you'd have to check with vendor on this: some will work with you, some won't.

    Heh.... but first, see my response with the graph for why you shouldn't even consider clustering on the two columns we're talking about. 😉

    That graph has nothing specific to do with this table.  I'd have to see the specific stats on this table to decide.  But if (almost) all queries had WHERE conditions on ColA or ( ColA, ColB ), I would certainly cluster on those columns if at all reasonable.  Things can be done to mitigate alleged "atrocities" from INSERTs.  And there's only one INSERT of a row, and generally 100Ks or Ms or SELECTs.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • October 23, 2018 at 3:05 pm

    #2010859

    ScottPletcher - Tuesday, October 23, 2018 10:52 AM

    Jeff Moden - Tuesday, October 23, 2018 10:25 AM

    ScottPletcher - Tuesday, October 23, 2018 10:13 AM

    jontris - Tuesday, October 23, 2018 2:49 AM

    Thanks again for the responses. I can't re-cluster the table as it's part of a third-party package and already has a surrogate (clustered) primary key. Also the index in question is very far from the most used. I'm wondering why it's so shocking to suggest that the optimiser might be getting it wrong on this occasion. It is after all only a program, and programs have bugs.

    Typically you can still re-cluster it.  Retain the original PK, but just make it non-clus.  If the new clus works, then you can drop any non-clus indexes that have the same leading key(s).  Of course you'd have to check with vendor on this: some will work with you, some won't.

    Heh.... but first, see my response with the graph for why you shouldn't even consider clustering on the two columns we're talking about. 😉

    That graph has nothing specific to do with this table.  I'd have to see the specific stats on this table to decide.  But if (almost) all queries had WHERE conditions on ColA or ( ColA, ColB ), I would certainly cluster on those columns if at all reasonable.  Things can be done to mitigate alleged "atrocities" from INSERTs.  And there's only one INSERT of a row, and generally 100Ks or Ms or SELECTs.

    It has everything to do with this type of table and index because CI's that are built in such a fashion will, in fact, follow the same silo pattern.

    However, one good test is worth a thousand expert opinions.  Design a table and tell me how you'd like me to populate it over a simulated year and we'll see what happens.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 31 through 37 (of 37 total)

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