Post reply

seeking info for avoiding impact on log shipping and replication with index rebuilds

  • February 9, 2018 at 2:43 pm

    #343593

    JrJrDBA requesting feedback..

    here's the system info:

    --SQL2008R2 enterprise edition | 500GB RAM
    --backend storage: SSD
    --configured: full recovery mode for SQL transnational replication :150 publications, and log shipping of database to remote site.
    --database size around 4.2TB
    --temp db: 400GB allocation on SSD, typically in use under 15GB on a day to day basis.
    --database usage: almost 24/7/364 in use

    The database is for an ERP software. We recently purged historical data (50million+ records) from many of the key tables.

    What I am seeking ?

    Assistance and best practice suggestions to start index rebuilds.Top 20 tables have 90%+ fragmentation. Rebuilds on these large tables was never done. Most of the top 20 tables have 500million+ rows, each table averaging 25 - 30 indexes, as delivered by the ERP. Given the number of indexes that need to be rebuild, I am trying to figure out how many parallel rebuilds can I launch and not  impact log shipping and/or replication, and of course the day to day ERP operation.I have already identified some unused index and will be deleting those as a starting point. There are at least 3 to 5 (unused indexes) for each of the 20 big tables.

    My findings so far:

    --allocate at least 120% space of original index when rebuilding
    --index rebuilds, when done online are slower
    --index rebuilds tax tlogs which will impact log shipping and tranactionla replication
    --enabling 'sort in tempdb' may take some load off tlog but tempdb needs to have adequte space to support the build
    --I am researching some sort of a WAIT parameter for index rebuild but i think it may not be available for SQL2008R2

    any feedback/pointers/direction is greatly appreciated that will help towards my task.

  • February 12, 2018 at 8:47 am

    #1979436

    Given the constraints you have, along with the amount of control you want over index rebuilds, I would highly recommend looking at Ola Hallengren's index maintenance solution.  Some of the benefits of using this for your situation:

    - There are options that let you go after the most fragmented indexes first.  This will help you make the best use of the time spent rebuilding indexes.
    - The MaxDOP option defaults to using the global maximum Degree of Parallelism.
    - Page count options allow you to skip over smaller indexes so the bigger ones are prioritized.
    - Statistics options let you control how statistics are calculated (sample size, etc) when rebuilding indexes.
    - A time limit can be set, after which no more actions are executed.
    - The Delay option will let you specify how long it should wait before kicking off the next index command.  This should address your desire for having a WAIT parameter.
    - Looking at the FAQ, it shows that it supports Log Shipping and it gives you a tip or two.

    Note: Running his scripts with the Execute option set to 'N' will allow you to just print the commands and look them over.  Even if you don't end up using this to do your maintenance, you'll at least have picked up a better understanding of some of the options available and how Ola utilizes them.  His scripts have been used by a ton of people and best practices have been built into them as much as possible.

Viewing 2 posts - 1 through 1 (of 1 total)

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