SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
SQLJay
SQLJay
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 Visits: 123
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.
MicahNikkel
MicahNikkel
SSC-Addicted
SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)

Group: General Forum Members
Points: 419 Visits: 422
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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search