October 16, 2007 at 9:27 am
Are any of you currently using mirroring in a production environment with large (> 1Tb) databases? If so, how do you handle index rebuilds?
The issue here of course is that to mirror you must use full recovery model and there will be a lot of log activity during an index rebuild.
Thanks !
October 17, 2007 at 9:01 am
This is a big problem for many VLDBs that are mirrored. There are a couple of things you can do in 2005:
1) use a potentially less expensive (in terms of logging) solution for removing fragmentation - ALTER INDEX ... REORGANIZE (the replacement for my DBCC INDEXDEFRAG in 2000). This will do a comparable job to rebuilding an index most of the time but can do it with far less logging if the index isn't badly (> 30%) fragmented
2) be very selective on which indexes you rebuild. I don't know what your index maintenance strategy is - many people just rebuild all indexes every week without doing any analysis on their use and whether fragmentation removal leads to increased performance
3) partition the tables/indexes such that the amount of read/write data you need your index maintenance to operate on is minimized. Kimberly did a blog post a couple of weeks ago about this - see here.
In 2008 this problem will be alleviated a little by log stream compression for DBM. See my recent post here for more info on this.
Hope this helps.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 17, 2007 at 9:20 am
Thanks for your reply Paul. Much appreciated.
October 18, 2007 at 5:01 pm
MS has writepaper about performance considerations, including a lot of information.
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
Impact of long and log-intensive transactions
Long and log-intensive transactions can impact performance and failover time. Some common examples of long and log-intensive transactions are creating or rebuilding an index on a large table and bulk loading a large amount of data.
Need to be tested on your own environment as well.
October 18, 2007 at 5:32 pm
Yup - forgot about that. There's also a presentation available that I presented at TechEds in China and Hong Kong last year that's based on that whitepaper - see http://www.mshk.com/hk/technet/teched2006/ppt/Day_1/Session_1/DAT319_Paul_Randal.ppt
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
January 23, 2011 at 10:22 am
Paul, could you update the URL for the PPT? I am very interested to read the presentation. Thank you very much.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply