August 5, 2009 at 11:44 am
Hi! I need some guidance on the best approach for index tuning to take for a large OLTP database which also has asynchronous mirroring at a remote location.
I am following the general rule of thumb for optimization i.e. any index which has over 30% fragmentation gets Rebuilt and between 10% and 30% gets Reorganized. There is a job which does that every night after full backups. Also, there are some indexes which cannot be rebuilt online or reorganized so, the job simply rebuilds them offline.
However, since I am using mirroring, the rebuild causes a lot of logs and it takes ages for the mirror to catch up. So, I was wondering if tinkering with the fillfactor on those frequently fragmented indexes, allow me to run the optimization every week rather than every night.
If yes, what is the best practice for setting up the fillfactor percentage and what does PAD INDEX do?
Thanks in advance
Anish
August 6, 2009 at 2:16 pm
Nobody has any suggestions?
Have I posted this in the right section?
August 7, 2009 at 2:10 am
FILLFACTOR specifies how much the leaf level of the indexes should be filled. PAD INDEX just specifies that this should be done on the intermediate levels of the indexs as well.
Reindexing or reorganising is going to generate a lot of changes to the index pages, and therefore a lot of entries in the transaction log. Tinkering with FILLFACTOR will reduce or increase the number of pages used by the index, so will have an impact on the size of the logs, but that's not necessarily the best way to go.
Reorganising a clustered index is going to generate a vast number of changes, as that affects the data itself, so it pays to chose your clustered index carefully to avoid lots of page splits, and therefore fragmentation that need reorganising e.g. a UNIQUEIDENTIFIER is random in nature, so inserts go somewhere in the middle of the data, not at the end.
Do your indexes get so fragmented that they need reindexing every night?
You could try increasing the thresholds, so they don't get reindexed or reorganised as frequently.
You could also be a bit more selective and spread the load, so that each table is reindexed once a week, but spread over the 7 days of the week.
Whatever you do, measure the performance of the day to day work before and after to make sure this doesn't have too big an impact.
July 6, 2010 at 11:44 am
Hi Anish,
I'm curious what solution you ultimately went with and how this is going. I'm in a similar position where I have a large database I'm mirroring, and it takes a long time for the mirror to catch up when rebuilding indexes is necessary. I'm weighing the same options; a combination of tweaking fragmentation percentage thresholds and staggering the index rebuilds throughout the week.
I've read through Microsoft's "Database Mirroring Best Practices and Performance Considerations" white paper which is quite informative http://technet.microsoft.com/en-us/library/cc917681.aspx I already use high performance asynchronous/safety off mode, and while the rebuilds happen faster I'm sure if timing how long it takes the mirror to catch up that overall duration would be pretty much the same. I was hoping to see some remarks in there about the impact of disabling indexes during rebuild. Likely this only has the benefit of conserving disk space on the principal. If I get some spare time I'll probably do some testing to see the effect of disabling indexes during rebuild.
Thanks,
Ken
-Ken
July 7, 2010 at 7:07 am
My take would be like this :
1. disable the mirroring ,
2. do the index rebuild/reorgnize
3. restore latest backup and log backup on OLAP node
4. shrinking log backup
5. then setup mirroring
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 7, 2010 at 8:18 am
here is what i do within maintenance jobs. basically, what it does is pauses the mirror before whatever you want to do. in your case, a reindex.
I would create a step 1 for the job, pause the mirror, step 2 is the actual job you want done, step 3 is to resume the mirror. works great!
--- PAUSE - add as STEP 1
--- PAUSE - add as STEP 1
declare @name varchar(100), @mirroring_role int
declare db_crsr cursor for
select T1.name, T2.mirroring_role
from sys.databases T1 join sys.database_mirroring T2 on T1.database_id = T2.database_id
where name not in ('master', 'tempdb', 'model', 'msdb')
open db_crsr
fetch next from db_crsr into @name, @mirroring_role
while @@fetch_status = 0
begin
if @mirroring_role = 1
BEGIN
declare @scripty nvarchar(250)
set @scripty = 'alter database '+@name+' set partner suspend'
exec (@scripty)
END
if @mirroring_role = 2
print 'Datbase ' +@name+ ' is the mirror and there is nothing to do.'
fetch next from db_crsr into @name, @mirroring_role
end
close db_crsr
deallocate db_crsr
-----------------------------------------------------------------------------
--- RESUME - add as LAST step
--- RESUME - add as LAST step
declare @name varchar(100), @mirroring_role int
declare db_crsr cursor for
select T1.name, T2.mirroring_role
from sys.databases T1 join sys.database_mirroring T2 on T1.database_id = T2.database_id
where name not in ('master', 'tempdb', 'model', 'msdb')
open db_crsr
fetch next from db_crsr into @name, @mirroring_role
while @@fetch_status = 0
begin
if @mirroring_role = 1
BEGIN
declare @scripty nvarchar(250)
set @scripty = 'alter database '+@name+' set partner resume'
exec (@scripty)
END
if @mirroring_role = 2
print 'Datbase ' +@name+ ' is the mirror and there is nothing to do.'
fetch next from db_crsr into @name, @mirroring_role
end
close db_crsr
deallocate db_crsr
July 13, 2010 at 12:56 pm
I ended up reorganizing or rebuilding based on fragmentation level;
< 5% or < 30 pages = do nothing
+ < 40% fragmentation = reorganize
+ > 40% fragmentation = rebuild
This reduced the amount of change and runtime greatly. My larger tables don't get this fragmented often, at least I'm not seeing them get reindexed every day so this is working well. As a result, I opted not to pause or break the mirror as part of my maintenance routine.
Thanks
-Ken
February 22, 2011 at 2:15 pm
To pause Mirroring is an option, but in this case is not a valid one cause is a large database and the problem gets bigger when you have to setup mirror in different physical locations (I have this problem). Example Principal Database in LA, Mirror Database: Las Vegas.
So We have to find out a better option.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply