Home Forums SQL Server 2012 SQL 2012 - General Create Clustered Index on a Very Large Table (500 GB) RE: Create Clustered Index on a Very Large Table (500 GB)

  • May 13, 2014 at 12:23 am

    #1712906

    -A (5/12/2014)

    Jeff Moden (5/12/2014)

    -A (5/7/2014)

    Thanks for the replays.

    --Here is the answers to the follow up questions:

    1) I have to create this Index online since it is huge and I cannot afford such a long down time.

    2) MAXDOP was set to 10 at first and showed no significant improvement in speed. I believe at the end the create index still happens on 1 processor anyways.

    3) Also SORT_IN_TEMPDB = OFF or ON still didn't matter as SQL always seemed to use up all of the tempdb.

    4) This table is already partitioned by date and currently has 570 partitions.

    Wait justa minute... you have a partitioned table that has no Clustered Index??? :blink:

    What seems to be the problem ?

    Nothing if you never update variable width columns to contain more than they did at INSERT time and you never do deletes, both of which can waste huge amounts of disk space, memory space, etc. Sure, you can rebuild a heap as of 2008 but that also comes with some hefty hidden costs for the NCIs.

    I suppose that for tables like audit tables, which are only inserted into, a partitioned heap might actually do faster inserts than on a partitioned clustered table so that might be an advantage. I would think that you'd need some covering indexes to get any real performance out of such a thing and those might negate the insert advantage that a heap might have.

    I've never tried such a thing before and it's an interesting prospect. What's your experience on such a thing?

    And thanks for raising the question.

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