Very large Indexes (~3TB) and need to rebuild fast

  • Are you rebuilding your indexes on the large table as a preventive measure or because your queries are having performance problems?  Are your query plans showing scans against the tables?

  • The OP has left the building! 😉

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

  • Are you rebuilding online?

  • I think you should consider using partition view with tables being in multiple databases. It will also reduce your backup time and rebuild indexes, if data is not changing for certain partitions. For the partitions where data is not changing you do not have to rebuild indexes and have to take backups. Create database monthly if data is based on the dates. Also, if you are not using some old data then you can put offline or detach from the partition view so it does not query any more which will increase performances(Banks does that).

    Using table partition is good in sql server 2014 or greater, but whole data is still in same table plus index rebuild and backups still becomes problem. In SQL Server 2014 or above it is ok to switch partition to remove some data into another table but partition view will give you automatically remove unnecessary database containing old data if created dynamically.

    Thanks,

Viewing 4 posts - 16 through 18 (of 18 total)

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