asked
What is the best practice for Re-index and Update Statistic
Dear All, As a new comer in DBA, I am looking for a best practice of how to set either one or more jobs to do re-index and update statistic in databases. For current condition, I have 60 databases online in production server and there is a job created to do re-index and update statistic for every databases available in the server. Few months ago, the job run as expected, 30 to 60 mins. As the size of the databases have been growing quickly, the job also takes longer time to run. Last week, the job took about 6 - 7 hours. Yesterday, I had to stop the job due to running over 7 hours, because it would distract the daily transaction and consume more available space of memory. I have a thought to split the job into several based on the size of the databases. If there is a database with huge file size, It will be in one job only. If the size is medium, the database will be in a group of 4 databases which will be run in one job. If small one, the database will be in a group of 8 databases which will be run in one job. What do you think about my thought? Is that a good way to do re-index and update statistic? or Do you have other ways to overcome this case? Any C&Cs would be very appreciated. Regards, Hery He,
If you are planning on running the different jobs in parallell, I doubt you will get them done quicker. But if you are planning on running the jobs for one set of databases on monday, another set of databases on tuesday etc, then I think it's a good idea. But you might also want to look into your maintenance scripts to see what you can do to reduce the time for updating statistics and rebuilding indexes. Are you rebuilding all indexes every time, are you updating all statistics every time etc? There are a number of scripts available which can be configured to rebuild an index only when it is fragmented to a certain percent, reorganize when it's fragmented to another certain percent etc. I use Ola Hallengren's scripts myself:
http://ola.hallengren.com
8 People are following this question.
