January 5, 2011 at 9:16 pm
All,
I have run into the following issue while implementing SQL Server High Availability solution for the project we are woking on and need advise here
I'm rebuilding or reorg indexes based on the fragmentation in the indexes on a daily basis.
After rebuild indexes the transaction log backup is going up more than 5 GB.
We got 3 MBPS (low bandwidth) between Production Site and DR Site. They are more than 100 miles apart.
Initially implemented DB Mirroring between the sites but mirror database took more than 4 hours to catch with principle server after rebuilding indexes. The performance of the production database went down at this time so we ended up removing mirroring altogether.
I'm thinking of implementing log shipping but i'm afraid i will run into the same issue again.
What is your take on this? Do i need to rebuild indexes on a daily basis at all? I highly appreciate your help in this regard.
Thanks In Adavce,
Satish
January 5, 2011 at 9:42 pm
It is quite natural for the Transaction Log to grow during Rebuild index operation. Even if you implement log shipping, the transaction log backup will be *big* in size after rebuilding the indexes.
Don't rebuild all the indexes. Check the fragmentation level of the indexes and decide which one needs to be rebuilt. You can make use of Ola's script[/url] to selectively rebuild the indexes. Rebuilding fewwer indexes will considerably bring down the size of the transaction log backup size when compared to rebuilding all.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
January 5, 2011 at 9:56 pm
Thanks for you input. Currently, I'm rebuilding index only if the fragmentation is more than 40%.
I'm performing reorg on the index if the fragmentation is between 10 and 40.
Do you see a reason to perform the Index maintenance on a daily basis or weekly may be good enough?
Thanks,
Satish
January 5, 2011 at 10:28 pm
After rebuilding the index if the fragmentation level increases drastically the very next day, then it might be a good idea to rebuild it on a daily basis. Otherwise rebuilding it weekly should suffice. Again it depends on how sensitive the application is to fragmentation. I have seen some application whose performance drops even with a little fragmentation.
It would be a good idea to check this by rebuilding the indexes on a test box and based on the outcome you can take a call on the Production instance.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
January 5, 2011 at 10:42 pm
I skip running this job some days due to planned downtime or for other reasons. I have not heard that application has performance issues next day. So, i assume i can schedule this job once a week and see how it goes.
However when this job runs every day the log backup size is huge. Dont understand why the fragmentation level seems to be same every day through rebuilding indexes every day.
Do you think using "Slective Indexe script" help me in this regard if i decided to continue rebuilding indexes on a daily basis?
Thanks,
Satish
January 6, 2011 at 12:51 am
Rebuilding the selective indexes would surely bring down the size of the Transaction Log backups.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
January 6, 2011 at 7:24 am
I will look into that. On a side note, do you recommend us to go with Database Mirroring (Async) Vs. Log Shipping. We have no requirement for automatic failover. My worry with log shipping is, it might break if copy of the log backup takes longer than expected. At least with database mirroring, though it takes long time for the mirror database to catch up with principle it wont fail. I was wondering if mirror database takes while to catch with priniciple database would it cause any performance issues?
Thanks,
Satish
January 6, 2011 at 7:38 am
Satish-219904 (1/6/2011)
I will look into that. On a side note, do you recommend us to go with Database Mirroring (Async) Vs. Log Shipping. We have no requirement for automatic failover. My worry with log shipping is, it might break if copy of the log backup takes longer than expected. At least with database mirroring, though it takes long time for the mirror database to catch up with principle it wont fail. I was wondering if mirror database takes while to catch with priniciple database would it cause any performance issues?Thanks,
Satish
I would go with Mirroring. I have not come across any performance issues when the principal and mirror are out of sync.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
January 6, 2011 at 8:16 am
Perfect. I will implement Mirroring then. I'm still thinking of doing Index Maintenanc once a week as opposed to it daily. However, i will update statistics daily though.
What do you say?
Thanks,
Satish
January 6, 2011 at 8:26 am
Satish,
I ran into a very similar issue recently. When my indexes would rebuild on the weekend, my log would inflate 300%. It will stay that way until the next log backup is run. In my post, I outline the scenario and a few solutions, one being to use the rebuild option SORT_IN_TEMPDB = ON. This will force all sorting and index rebuild operations to be logged in tempdb, not the user database. Just make sure your tempdb log disk has enough space to handle the additional log size.
Do the research on how index rebuilds effect transaction logs. You'll find there are also performance gains involved when SORT_IN_TEMPDB = ON is used. This might help make your decision on whether to go with mirroring or log shipping a little easier.
Here's the link to my thread:
http://qa.sqlservercentral.com/Forums/Topic1042706-391-1.aspx
January 6, 2011 at 2:39 pm
SQL2008 can compress the trans log then ship to remote when mirroring, right?
January 6, 2011 at 3:45 pm
If you need to rebuild your indexes that often then I would suggest that you look at rebuilding with a lower fill-factor and pad-index.
For the log shipping, think about it this way.
The log contains a purely internal pointer (call it a surrogate key!) to the database block and any changes to the contents of that block mean that either the whole 8k block goes into the log, or a pointer to the block and and the internal pointer withing the block and the changes.
If your transaction log is an issue then you can definitely choose to rebuild fewer index and do it less often. That may mean that you can get to a situation where this becomes a weekly task and you can manage a large log file once per week...
December 26, 2014 at 3:55 am
Hi,
Another way to go about it is that you can change the recovery model of the database from Full to Bulk logged and hence Alter Index statement would be minimally logged.
Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)
December 26, 2014 at 6:06 am
Shafat Husain (12/26/2014)
Another way to go about it is that you can change the recovery model of the database from Full to Bulk logged and hence Alter Index statement would be minimally logged.
Not if the database is mirrored. Mirroring requires full recovery. It also won't help with log shipping. Bulk logged can reduce the amount written into the transaction log, but the log backups will be the same size, if not larger, and it's the size (and copy time) for the log backup files which are the problem in this case.
p.s. 4 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply