10

Will you please help me understand pros and cons of using Ola’s solution over maintenance plan? I have prepared a presentation based on SQL Pass (http://www.pass.org/DownloadFile.aspx?File=ebae1b31) which I will present.

I am also preparing few scenarios which Ola’s solution addresses and maintenance plan solution doesn’t. Can you all please help me explain this more technically?

By the way, we are managing almost 150+ servers (mix of 2008/2012/2014/2016) with Ola’s solution on at least 75% of them. I liked this article by Brent Ozar. But in one the comments, Brent has recommended to use script based solution for the number of servers we have. https://www.brentozar.com/archive/2012/04/maintenance-plans-roombas-suck-good-way/

Pat
  • 337
  • 1
  • 5
  • 12
  • Are we talking backups, index/stat maintenance, corruption checking or all of the above? – nkdbajoe May 17 '18 at 01:39
  • All of them. Entire maintenance solution script by Ola. I have resolved issues of unnecessary Index rebuilds and statistics updates in maintenance plan using Ola’s scripts. I just wanted some more technical ammo from other DBAs out there. Thank you. – Pat May 17 '18 at 04:59
  • 2
    I personally believe the best solution is a solution that meets your business requirements and your capability to solve it once there is error.

    Ola's solution is not bad in general but I still prefer my own customized solution for my environment. For example, for index maintenance, I want to have parallel executions to save time. Ola's solution does not work here. I want to have incremental stats update, Ola's solution does not work here either.

     – jyao May 17 '18 at 05:30
  • Do you have any data to show that they are better or are you just going with what you feel is better? The best way to solve the issue is get some performance data to be able so show why a method is better – Joe W May 17 '18 at 16:06
  • For index maintenance, many times the limiting factor is your SAN and storage subsytem. Depending on your SAN configuration, you may not see any improvement in parallel rebuilds. – Alen May 17 '18 at 17:56
  • If you have no data to prove one method is better then another it can be hard to explain that to someone who thinks otherwise. Also there is nothing from preventing a deadlock error from occurring when running it from the command line even when you think it is properly timed. – Joe W May 17 '18 at 20:05
  • @Joe W. At my previous employer, I have cut down statistics update job that used to run for 10-12 hours on SQL Server 2008R2 to less than 90 minutes. There was no way to update only modified column statistics through Maintenance Plan. At my current employer, once our Index Rebuild job setup from Maintenance Plan failed. I didn't see how to make sure whether all other statements succeeded or not other than failed command seen in error log. With Ola’s solution, in case of deadlock or any other error, you can run failed command from CommandLog table when time permits. – Pat Jun 20 '18 at 21:26

5 Answers5

14

I have written here

Maintenance plans are not bad, but when your environment grows, the limited flexibility and functionality that maintenance plans provide wont be sufficient.

To add more,

  • Ola's maintenance solution is widely accepted in the community and large organizations.
  • Its open sourced and issues can be raised at github/issues with a likelyhood of getting it fixed very fast.
  • Its flexible and scalable (even if you want to deploy it to 100s of servers just use Install-DbaMaintenanceSolution from dbatools.)
  • Microsoft took almost a decade to fix Maintenance plan GUI which was buggy itself :-)
  • has extensive documentation and FAQs and is constantly updated to accomodate newer sql server versions.
  • in preview version, you can even run backups in parallel.
  • for index maintenance solution, you can even time the process e.g. if it runs more than X amount of time, abort it.
Kin Shah
  • 62,225
  • 6
  • 120
  • 236
6

One of the major advantages of a script based solution is ease of deployment - something that is clearly relevant in your case, as you have 150+ servers. Trying to rollout several maintenance plans (i.e. at minimum 2, one for system databases and one for user databases) across 150 servers would be a nightmare. Maintaining them once in place is just as much of a hassle.

A script based solution is much easier to deploy and maintain over time. Ola's scripts are fairly comprehensive and cover most needs. They represent a great starting point for any organisation to then tweak to match their own unique requirements.

In our case, we have about 40 SQL instances in our DEV environment, and we use modified Ola scripts with the multiple-connections feature of SSMS to be able to rollout changes to the maintenance regime on all 40 instances at one click. Any special cases are handled by our modifications.

Mike
  • 648
  • 4
  • 13
4

I'm not 100% sure of his scripts but custom scripts are way better than maintenance plans. The built in plans will rebuild every index on a table no matter how little fragmentation. If you have Always On it will create a storm of traffic.

Custom scripts you can rebuild anything over 20% or whatever your threshold is. Less indexes rebuilt at a time. Less Always On data to send to secondaries. Faster rebuilding because you are rebuilding less indexes. Shorter maintenance window required.

Last time I used maintenance plans, I had a 300 million row table and Always On would be hours behind whenever index rebuilds kicked off and issues with the transaction log overflowing. Went back to scripts and it all went away.

Michael Green
  • 24,839
  • 13
  • 51
  • 96
Alen
  • 539
  • 3
  • 9
  • 1
    I originally wrote my own for SQL 2005 back around 2007. I used what books on-line had as a base and changed it a bit. Back then it seemed like most people were using the plans and now it seems to have reversed. And before I started doing DBA stuff, the previous DBA before me had custom scripts for SQL 2000. The only thing I differently was I rebuilt everything. It was faster to rebuild anything over 20% or 30% than reorganize as well. For backups we've always used third party products – Alen May 17 '18 at 12:55
2

As well as the above my favourite reason is to be able to switch the type of backup automatically so a new database will have a full backup the first time the log backup job runs instead of waiting until the next full backup job runs.

SQLDBAWithABeard
  • 645
  • 1
  • 6
  • 13
0

Maintenance plans will do just fine most of the time. Ola Hallengren's scripts will do just fine most of the time.

In very rare cases, you might have to grow your own.

As Jyao said, it comes down to which you are most comfortable working with. If your co-worker is most comfortable with maintenance plans, why get your knickers in a twist?

If he's been databasing for 20+ years, he's already written his own maintenance scripts. Right about the time you were learning to drive, there was probably some young punk in cargo shorts and fliplops that came along and was all like "hey, you old codger, maintenance plans are better - and pull your pants down, you look ridiculous!".

Then there was probably a 4 year battle where the uppity youngster slipped in a maintenance plan whenever he could. Now this other young punk with skinny jeans and a freakin bow tie is telling him to go back to scripts. It's enough to turn your hair gray.

Three things to consider:

Are these examples of Hallengrenite superiority actually applicable to your environment?

Is it going to cause you an actual problem if he uses maintenance plans?

If you convince him to use Hallengren's scripts and there's an issue, will he be able to resolve it himself or will he have to call you?

James
  • 2,612
  • 5
  • 26
  • 49
  • Answer to first two questions Yes. We have seen issues with Maintenance Plans already and I resolved those with Ola’s solution. There was shrink log task (??) on production server as well which I disabled immediately. Third question, I don’t know. I am not sure whether he will be able to resolve issues created by Maintenance Plan itself. When I asked him in case we see issues with it, what should we do? His answer was to call Microsoft support. (??) – Pat May 17 '18 at 13:57
  • @Pat, ahh, sounds like he's hit his limit under the Peter Principle. Be careful trying to make him better - he isn't likely to appreciate the help. – James May 17 '18 at 14:04