Post reply

Creation of CI is taking more time

  • December 7, 2019 at 10:06 am

    Go to Answer

    #3705641

    Hi All,

    We are trying to creating a clustered idx on a HEAP. It is almost 45 mins, its still running. Heap is around ~400 GB. Is it expected ? anyway to speed up the creation of clustered idx?

    SQL Server 2014 EE.

    Tname

    Thanks,

    Sam

     

  • December 7, 2019 at 10:42 am

    #3705649

    what was the exact create index statement?

    and if this was on the same machine that you have with a maxdop of 1 that is likely normal and in this case using the maxdop option on the create statement will most likely make it faster

    and are you using compression or not (most likely you should on your server)

  • December 7, 2019 at 12:17 pm

    Answer

    #3705657

    45 minutes or more doesn't sound unreasonable for a table that big, but it also depends on the hardware, particularly the disk.

  • December 7, 2019 at 6:53 pm

    #3705687

    vsamantha35 wrote:

    Hi All,

    We are trying to creating a clustered idx on a HEAP. It is almost 45 mins, its still running. Heap is around ~400 GB. Is it expected ? anyway to speed up the creation of clustered idx?

    SQL Server 2014 EE.

    Tname

    Thanks,

    Sam

    It's kind of reasonable and kind of not.  "It Depends".

    In order to determine what might be possible, we'd need to things like what recovery model you're in and what's allowed, what the table is used for, the full CREATE TABLE statement including all constraints, keys, and triggers, and the code you're using to add the Clustered Index.

    A part of the reason for why it's taking so long may be because you've not considered what the creation may be doing to either the log file or the mdf file.  Done the way most people try to do these things, you're in the process of making both grow by 400GB.

    I also believe you may be missing the great opportunity to do some really cool optimizations of this table that will greatly reduce index maintenance and backup times, etc.

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

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