December 29, 2020 at 9:37 am
Hi everyone,
I'm setting up the maintenance plan for the indexes and their rebuilding for the first time.
Since I don't want to rebuild all the indexes every night, I set "fragmentation greater than 30%" as a filter but I noticed that tonight it rebuilt all those with fragmentation greater than 0 and not 30.
Did I do something wrong? I only checked the flag in the optimization filters.
Thanks in advance
December 29, 2020 at 3:27 pm
My first question would be... how did you determine that all those indexes were rebuilt just because they had a frag level > 0?
Also, a screen shot of your "Rebuild Index" task might help a bit.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 29, 2020 at 3:33 pm
Hi Jeff,
Because I saw in the task execution log file that the rebuild queries that were performed only affected the indexes with fragmentation greater than 0%. Nothing was done for all other indexes with 0 fragmentation. Is this a coincidence?
I have attached the photo of the task rebuilt
December 29, 2020 at 5:03 pm
With apologies for my ignorance on the subject, I'm not sure what's going on because I don't actually use index maintenance plans, mostly because of the many reported horrors about it. I've not heard of this particular horror, either, and so I'm going to have to defer to someone much more knowledgeable on this particular subject.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 29, 2020 at 8:48 pm
With apologies for my ignorance on the subject, I'm not sure what's going on because I don't actually use index maintenance plans, mostly because of the many reported horrors about it. I've not heard of this particular horror, either, and so I'm going to have to defer to someone much more knowledgeable on this particular subject.
I have also heard all of the "horror" stories - and yet they all revolve around the 'wizard' and not the actual tasks. I do have issues with some of the tasks and how MS implemented them...but maintenance plans in and of themselves are not really a problem.
You see the same "horror" stories around Ola's utility...specifically related to how someone implemented *everything* in the utility, including both reorganize and reindex as well as update statistics (full scan after rebuilding indexes) and a host of 'other' issues.
It comes down to knowing what maintenance needs to be performed *for your system* and relying on a wizard...or some tool (Ola) to figure out maintenance for you just isn't going to work. And not all systems are the same...nor are all databases in a single instance.
Back to the OP - how did you determine that specific indexes were rebuilt? Did you run something that showed you the index fragmentation before and after you ran the task?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 29, 2020 at 11:04 pm
It comes down to knowing what maintenance needs to be performed *for your system* and relying on a wizard...or some tool (Ola) to figure out maintenance for you just isn't going to work. And not all systems are the same...nor are all databases in a single instance.
I've found that each index needs to be looked at a whole lot deeper than most people are willing to explore. That's a part of what my "Black Arts" Index Maintenance series of presentations are about.
I'm slowly (as in cautiously and doing some very deep testing) a method that will do a pretty good analysis for each index and then do rebuilds. A fair bit remains... I have created a tool that will help figure out which columns suffer from expansive updates along with metadata about that so that one can design expansive update out of the system.
It's getting there but still isn't ready for prime time.
And thanks for the notes you have in your post above... I been preaching that there is no panacea that actually works correctly when it comes to index maintenance and it's nice to see someone else say basically the same thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 30, 2020 at 11:22 am
Hi Jeffrey,
Yes sure, I ran the query to determine the index fragmentation before and after the maintenance plan and I saw that all those that were above 0 were changed and brought back to 0. And that's what I didn't want!
Furthermore, also from the SQL log written during the execution of the plan, I detected the individual Rebuild queries that had been executed.
December 30, 2020 at 7:56 pm
I am not sure what that maintenance task actually generates - or how it truly identifies the indexes to be rebuilt. My guess is there is something else involved that identifies the indexes in addition to the selection you made.
I believe there is a generate button available that shows the code that will be generated. And...just changing the value in the tasks doesn't update the tasks - the full plan needs to be saved for the changes to take effect.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply