some questions related to Ola hallengren maintenance scripts

Question

Sam 1,476

Hi All,

I have couple questions related to Ola Hallengren's maintenance script. If anyone has the answers, please do reply.

In which order the rebuild and update stats run. Is it based on tables sorted by name or something else?
Is there a way to tell how much % of work is left over or how much % of work is done?
Can we pause/suspend index rebuilds or updating stats operation and resume as per need?

Thanks,

Sam

Accepted answer

0 additional answers

Answer 1

Christoph Muthmann 181

Hi Sam!

It is based on objects sorted by database, schema and object_name.
No, because the process does not know how much work has to be done or how complex the work could be.
You can not pause the execution, but I believe you can kill the job/process at every moment. It is similar to killing an index rebuild which is started manually. Every time the job starts, it checks the index and column statistics to detect what has to be done. So every maintenance you have been done in the meantime shortens the job run.

Kind regards,

Christoph

Dirk Hondong 871 Reputation points

2024-03-19T16:10:18.3933333+00:00

As a little add on: you an "split" the logic of the Hallengren Jobs a bit. During the week, just perform statistic maintenance. During the weekend, do index maintenance wherever necessary. Your mileage may vary. But with this approach, you get a bit more control.

I always try to align the index mainenance tasks with the tlog backups to avoid a full tlog. Example: if I have tlog backups every 15 minutes (again YMMV) , then set the "@timelimit" value to 600 seconds. And then trigger the next run in your maintenance windows a couple of minutes later.

Regarding killing the job: be aware that this may roll back a rebuild of the latest index touched.

Reorg and stats update are less of a problem though.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Christoph Muthmann 181 Reputation points

2024-03-21T07:36:02.6966667+00:00

Somehow my last answer was detected as inadequate. So let's try it again.
Dirk is right and also provided some important information.

Please take a look at resumable index operations. You can do this with Ola Hallengren's solution as well.