Post reply

Clustered Index Ordering & Insert Speeds

  • July 14, 2013 at 7:13 pm

    #296385

    Hi,

    We used to have a clustered index which consisted of 5 fields, this was ordered incorrectly which meant new inserts caused a lot of bad page splits.

    This CI was reordered to be correct and that composite (5 fields) key would now be always ascending therefore it will not insert anything in between the pages.

    I confirmed that there were minimal page splits via the transaction logs. However the inserts now are taking approximately 10 times longer than before.

    There was an additional non-clustered index put in to cover for scenarios that the new CI doesn't cover, this is causing some new page splits, however I doubt that the non-clustered index page splits cause that much of a difference.

    I use SSIS OLE DB Destination with Fast Load & Check Constraints Options.

    Any idea what would be the issue, or what I should look at?

  • July 14, 2013 at 11:22 pm

    #1632293

    shing.lau (7/14/2013)

    Hi,

    We used to have a clustered index which consisted of 5 fields, this was ordered incorrectly which meant new inserts caused a lot of bad page splits.

    This CI was reordered to be correct and that composite (5 fields) key would now be always ascending therefore it will not insert anything in between the pages.

    I confirmed that there were minimal page splits via the transaction logs. However the inserts now are taking approximately 10 times longer than before.

    There was an additional non-clustered index put in to cover for scenarios that the new CI doesn't cover, this is causing some new page splits, however I doubt that the non-clustered index page splits cause that much of a difference.

    I use SSIS OLE DB Destination with Fast Load & Check Constraints Options.

    Any idea what would be the issue, or what I should look at?

    It probably is the new non-clustered index. Depending on size, the NCIs will do extent splits instead of just page splits. You can actually cause applications to fail with timeouts if you have a low selectivity NCI on the table.

    --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 2 posts - 1 through 1 (of 1 total)

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