Post reply

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

  • February 8, 2017 at 7:09 pm

    #1927426

    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?

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • March 5, 2017 at 4:54 pm

    #1931721

    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)

  • March 6, 2017 at 8:36 am

    #1931834

    Are you rebuilding online?

  • March 7, 2017 at 1:57 am

    #1931987

    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