SQL Server Maintenance Plan Feedback
-
Hi
I set this up today. I wanted to get some feedback. I have some questions:
1. How often should I be running each subplan?
2. What order should each subplan be run?
Here is the plan (proposed):
Thank you
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
To be honest, I'd delete the REORGANIZE job and disable the REBUILD job. Microsoft finally took down the 5/30 supposed "Best Practices" on 20 April 2021 and a lot of people that I know still haven't read Paul Randal's article from 2009.
There's a huge amount of more data. For example, do you know why Random GUIDs are so bad with fragmentation? They're actually not the reason. REORGANIZE is the reason. Random GUIDs behave in a manner that is the very epitome of how people think that indexes should operate (and a very high portion of normal non-clustered indexes do, as well)
People don't understand what REORGANIZE actually does nor how much worse it can be for the log file and more than a REBUILD can be. It actually sets up to perpetuate fragmentation and that causes massive page splits with is more guaranteed fragmentation and a whole lot of unnecessary blocking on the proverbial "morning after" you do index maintenance.
Here's a run little 'tube just for starters to see how wrong people have been about index maintenance.
https://www.youtube.com/watch?v=rvZwMNJxqVo
And, just for grins, do something you've probably never done before... do a set serious performance measurements the Last business day before you do you index maintenance and then do the exact same performance measurement on the First business day after you index maintenance. I'm thinking that you'll find little difference to, depending on other factors, that performance and blocking got quite a bit worse and will gradually get better for the next week.
Except for some occasional space recovery, I haven't done any scheduled index maintenance since Sunday, 17 January 2016 because of the "Blocking Storm" that the index maintenance crippled my production server with on Monday, 18 January 2016. Most of the "index maintenance" suggestions like the supposed "Best Practice 5/30 method" have been wrong since the git.
Even Microsoft finally got the message and took that "Best Practice" out of their documentation on 20 April 2021. What they replaced it with is a whole lot more complicated but at least they took down that incorrect and actually "worst practice" method that was so bad that it made a whole class of indexes and a datatype look bad.
--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)
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply