some questions related to Ola hallengren maintenance scripts

Question

some questions related to Ola hallengren maintenance scripts

vsamantha35

SSChampion

Points: 11551
More actions
March 19, 2024 at 6:14 am
Go to Answer
#4397964

Hi All,
I have couple questions related to Ola Hallengren's maintenance script. If anyone has the answers, please do reply.
1. In which order the rebuild and update stats run. Is it based on tables sorted by name or something else?
2. Is there a way to tell how much % of work is left over or how much % of work is done?
3. Can we pause/suspend index rebuilds or updating stats operation and resume as per need?
Thanks,
Sam
Ant-Green

SSC Guru

Points: 113507
More actions
March 19, 2024 at 8:44 am
Answer
#4398032

1, if you set @Execute = 'N', it will print out the actions it will do in the order it will do it.
2, there is no progress indicator, you could write your own wrapper on top, to do Execute=N, LogToTable=Y, then your code comes in picks up what it was going to do, and can update commandlog when it has done it, then you can see what you have done and what is left.
3, yes use @resumable, assuming you are using a version and edition of SQL which supports the RESUME / PAUSE syntax.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MMartin1 One Orange Chip Points: 27799 More actions · Answer 1

It sounds like there is a particular index that may be taking a lot longer and blocking the processing of others , especially if you set a time limit on the operation. Refer to the commandLog to pinpoint any problematic maintenance on an index. You can remove this index and put it in its own SQL Agent job on its own day and time.

----------------------------------------------------

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 2

vsamantha35 wrote:

Hi All,
I have couple questions related to Ola Hallengren's maintenance script. If anyone has the answers, please do reply.
1. In which order the rebuild and update stats run. Is it based on tables sorted by name or something else? 2. Is there a way to tell how much % of work is left over or how much % of work is done? 3. Can we pause/suspend index rebuilds or updating stats operation and resume as per need?
Thanks, Sam

Sam, this is going to sound like an incredibly stupid question but... Why are you doing index maintenance? What do you think you're going to achieve for rowstore indexex with index maintenance? Or, as Brent Ozar would ask, "What problem are you trying to solve"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27799 More actions · Answer 3

Jeff Moden wrote:

vsamantha35 wrote:
Hi All,
I have couple questions related to Ola Hallengren's maintenance script. If anyone has the answers, please do reply.
1. In which order the rebuild and update stats run. Is it based on tables sorted by name or something else? 2. Is there a way to tell how much % of work is left over or how much % of work is done? 3. Can we pause/suspend index rebuilds or updating stats operation and resume as per need?
Thanks, Sam
Sam, this is going to sound like an incredibly stupid question but... Why are you doing index maintenance? What do you think you're going to achieve for rowstore indexex with index maintenance? Or, as Brent Ozar would ask, "What problem are you trying to solve"?

Aside from the obvious fix or prevent query performance issues ? I have a feeling I missed something here.

----------------------------------------------------

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 4

MMartin1 wrote:

Jeff Moden wrote:
vsamantha35 wrote:
Hi All,
I have couple questions related to Ola Hallengren's maintenance script. If anyone has the answers, please do reply.
1. In which order the rebuild and update stats run. Is it based on tables sorted by name or something else? 2. Is there a way to tell how much % of work is left over or how much % of work is done? 3. Can we pause/suspend index rebuilds or updating stats operation and resume as per need?
Thanks, Sam
Sam, this is going to sound like an incredibly stupid question but... Why are you doing index maintenance? What do you think you're going to achieve for rowstore indexex with index maintenance? Or, as Brent Ozar would ask, "What problem are you trying to solve"?
Aside from the obvious fix or prevent query performance issues ? I have a feeling I missed something here.

MMartin1 - You're a keen observer though! Well sighted and well cited!

You said "Aside from the obvious fix or prevent query performance issues". What PROOF do you have that index maintenance is actually making a worthwhile difference other than what any statistics rebuilds (either directly or though an index rebuild) are doing?

I suspect you answer will be "None". If you measure performance on the proverbial "Morning After" your largest index maintenance day, your answer may change to "Oh my... I had no idea things were that bad".

Go setup the "tests" and see. Measure the performance the first workday after the index maintenance. Then, measure the first workday before the index maintenance (if they have about the same work loads). If you do index maintenance once a week or once a month, try skipping a week or a month and see what performance does. I think you're in for a big surprise.

Spoiler alert: I went 4 years without doing any index maintenance. All I did was stats maintenance. The alarming number of page splits and the resulting major blocking on the morning after stopped, CPU usage dropped from a "normal" 22% to only 8-10% in the first 3 months, and then stayed there for the next 3 years and 8 months without me lifting a finger.

I eventually had to do some index maintenance for space recovery but I also did those VERY carefully and with a new understanding that 4 years of deep study brought for me.

With that, let me ask... have you seen any of my 'tubes or other presentations on the subject?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

Index maintenance mainly about white space recovery, gives a placebo effect as index rebuilds will update stats with a full scan, in reality the stats were the issue all along

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

MMartin1 One Orange Chip Points: 27799 More actions · Answer 6

Hmm. I am not so sure. I had an issue where the query plan kept regressing due to an issue with memory grant on a table with a large varchar combined with the optimiser thinkging that lookups against the huge table for most all the rows was better than a straight scan. The large memory grant was filling the temp table and thus other user transactions came to a crawl. I repeatedly updated statistics, the issue of high cpu and blocking would go away for a few days and then return. I would rinse and then have to repeat. Eventually I went with the advise of the tuning engine with a missing (covering) index recommendation. The problem went away and has not come back.

----------------------------------------------------