March 28, 2012 at 1:26 pm
If I need to run 1272 indexes in batches, then I will have to go by doing this in a manual process....
Do you think this is what can be good?
1. Highly fragmented indexes (top 100 - 200) at a time.
2. Rest few in batches at a different time...?
But I hard code the above logic in a script, then my concern was everytime those indexes might not be very much fragmented because it will all depend upon the total no of insert/upd/del from the tables. So, I was thinking to alter the rec model to Bulk-Logged!
Thanks.
March 28, 2012 at 1:27 pm
One other item to note that may help you...for your highly fragmented indexes evaluate your fill factors. If you want to reduce fragmentation over time you may benefit from lowering your fill factors. It will cause more disk space to be used in your data files and subsequently RAM when those pages are read into memory, but it may extend the time between when your indexes should be defragged to keep that percent low.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 28, 2012 at 1:31 pm
Sourav-657741 (3/28/2012)
If I need to run 1272 indexes in batches, then I will have to go by doing this in a manual process....
No, you won't.
Grab the index rebuild script from Michell Ulford (SQLFool), it's got the ability to set a time limit on how long the rebuild runs.
If you're running a maint plan and blanket rebuilding everything no matter how fragmented it is, then log shipping is going to be a problem, you'll be generating probably 80GB of log backups over the time of the index rebuilds.
Again, bulk-logged recovery is not going to help in the slightest for keeping the size of the log backups low.
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 28, 2012 at 1:33 pm
Many thanks for this point too.
Unfortunately, at server level the fill factor is set as default (=0). And I can't change it.........since it has been working since long time!
Thanks.
March 28, 2012 at 1:33 pm
Sourav-657741 (3/28/2012)
So, I was thinking to alter the rec model to Bulk-Logged!
Let it go man! 😀
I use this system for index maintenance: Ola Hallengren > SQL Server Index and Statistics Maintenance
If you employ it, you can schedule a job to defrag only your highly fragmented indexes, or only your very large indexes first (look into FragmentationLevel1, FragmentationLevel2 & PageCountLevel parameters). You can also set the proc to quit after n-seconds so you can control the runtime (finishing current operation when limit is reached).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 28, 2012 at 1:35 pm
Sourav-657741 (3/28/2012)
Many thanks for this point too.Unfortunately, at server level the fill factor is set as default (=0). And I can't change it.........since it has been working since long time!
For all indexes? That's part of your problem right there. The second you defrag your indexes they immediately start off by fragmenting the index with the first insert or qualifying update.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 28, 2012 at 1:37 pm
Sourav-657741 (3/28/2012)
If I need to run 1272 indexes in batches, then I will have to go by doing this in a manual process....Do you think this is what can be good?
1. Highly fragmented indexes (top 100 - 200) at a time.
2. Rest few in batches at a different time...?
But I hard code the above logic in a script, then my concern was everytime those indexes might not be very much fragmented because it will all depend upon the total no of insert/upd/del from the tables. So, I was thinking to alter the rec model to Bulk-Logged!
Rebuilding 1272 indexes? Is that all the indexes in your database?
Two systems for maintaining your indexes have been suggested, please look into them. There really is no need to rebuild all indexes every time.
I haven't used either presented having written my own process for rebuilding indexes as needed. But it should definately help to implement something that only rebuilds or reorganizing indexes as needed.
March 28, 2012 at 1:37 pm
Sourav-657741 (3/28/2012)
Unfortunately, at server level the fill factor is set as default (=0). And I can't change it.........since it has been working since long time!
The default server-wide fill factor should not be changed. That should stay at 0. What should change is the fill factors on individual indexes, if they fragment often reduce it to 80 or even 70 on a per-index basis.
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 28, 2012 at 1:47 pm
Many thanks....................
Thanks.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply