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
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.
April 26, 2024 at 9:47 pm
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.
----------------------------------------------------
April 27, 2024 at 1:40 am
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
Change is inevitable... Change for the better is not.
May 6, 2024 at 2:14 am
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.
----------------------------------------------------
May 8, 2024 at 3:33 am
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
Change is inevitable... Change for the better is not.
May 8, 2024 at 8:38 am
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" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply