• As Grant said, it looks like the system might be waiting on file growth. Make an educated guess on how big the Leaf Level of the index is going to be and grow your database at least that much.

    Also, if you're in the FULL recovery model, your log file is going to grow a lot on this one. Make a backup just before you're ready to try another rebuild and the set the server to the BULK LOGGED recovery model. Your point-in-time backups will take the hit of not being able to recover to a point in time because, in the BULK LOGGED mode, CREATE INDEX is minimally logged.

    Also, what kind of table is this? Is it an "audit" or "order detail" type of table by any chance? Do you have a "Date_Created" column on this table and, regardless of type, is it "temporal" and the old rows are mostly static?

    As for the new index, everyone on your team realizes that NCIs are a duplication of data that includes all keys, includes, and the PK columns, right? You sure you REALLY want to make an index that turns out to be 9 columns wide (PK includes in the INCLUDEs)? Are you sure that key lookups are going to hurt that bad? Are you sure that your backups can handle the extra load and that you actually have the time to do the backups each night. Are you really, really sure that you want another index and set of stats to maintain on this relatively large 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)