March 22, 2012 at 1:50 pm
We have a busy production system with an OLTP database of 600-GB size: full recovery and log backups are taken every 10 min.
The database is mirrored to a DR environment.
Every once in a while we run into a situation where a large reindex/reorg operation runs through the night for several hours in production generating a 50-100 GB log.
This causes mirroring to be suspended in the DR server and resynchronizing prod with mirror takes several hrs the next day.
We are using the DR environment for reporting purposes, so many users log in daily to the mirroring snapshots for their reporting.
When the mirror gets disrupted due to the size of the log that needs to be transferred from prod, a lot of people get affected.
Is there a way to reduce the size of the log resulting from reindex/reorg?
We reorganize indexes with avg. fragmentation between 10 and 30% and re-index any with frag above 30%.
I'm looking to reduce the fill factors of several large indexes that tend to get re-indexed frequently (and undergo more updates than reads).
I'm hoping this will bring some relief.
I'm just wondering whether there is anything else we should be doing so we can keep doing our maintenance, but at a lesser cost to server resources, especially at the mirror site.
Would "sort_in_tempdb=ON" help to reduce the log? Any other option?
Just trying to leave no stone unturned. 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 22, 2012 at 3:04 pm
Since you are using DB Mirroring, you can't change from full recovery model to bull_logged model.
Only thing that comes to my mind is finding a way to spread out the index reorgs and rebuilds so that they aren't all running at the same time. Also, make sure they aren't occurring during the full backup. The transaction log won't get truncated while it is running even if t-log backups run at the same time.
March 22, 2012 at 4:06 pm
Reorganise instead of rebuilding and run small iterations.
Rebuild just what needs rebuilding and not everything and spread it out over several days
Set the mirroring to high performance for the rebuild and back to high safety after will reduce the effects on the principal but not the effects on the mirror
Upgrade the network between the servers.
REduce the fill factor of the indexes so that they fragment less.
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
March 23, 2012 at 3:48 am
Thanks both for your suggestions.
I'm also looking into some large indexes that over the last couple of months have hardly been used for seeks/scans/lookups (<10) but have undergone millions of updates. Those are being defragmented quite often.
I'm thinking of dropping those (after saving the scripts). (I know I have to be careful about dropping "unused" indexes, but at this point I feel I need to be a little more aggressive than normal).
Spreading out the load over several days is tricky. At the present time we have scheduled the maintenance to take place on Saturday nights, so on Sundays, even if mirror is unavailable for several hrs, fewer users will be affected. I think we'll stay with that arrangement for now, and, if need be, will look into spreading out the load.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply