question

heryhe avatar image
heryhe 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,
updateindexingstatistics
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.