Every now and then, I hear an experienced DBA say, “SQL Server maintenance plans for backups, indexes, and statistics? What, are you kidding me? Nobody uses maintenance plans because they suck.”
They say maintenance plans are so inflexible because they insist on defragging or rebuilding every index, every time, regardless of how little the table has changed. Or maybe they complain about problems with scheduling, or how they saw somebody use a DBCC SHRINKDB maintenance plan once.
Well, I use maintenance plans all the time.
I even suggest ’em to my clients.
In a perfect world, every DBA would have an unlimited amount of time to get the absolute best solution to every problem. We’d spend endless time tuning T-SQL, perfecting plans, and daddying databases. We’d drive home from work in our Porsche 911s to our mansion on the hill.
Back here in the real world, we have a limited amount of time to work each day. We need to implement solutions that anybody can troubleshoot and maintain with a bare minimum of knowledge. Sure, you might be the only DBA in the shop today, but wouldn’t it be nice to get a junior person hired, and then immediately be able to pass work off to ’em? I’ve seen shops where the lead DBA is still personally managing their homegrown maintenance scripts – the DBA doesn’t trust anyone else to touch this convoluted spaghetti code.
In the real world, we usually have two, or sometimes three, problems:
- The servers need to be maintained
- We need to save DBA time
- We need to save server time
Everybody has problem #1, but often problem #3 is overrated. With careful use of maintenance plans, we can solve problems #1 and #2 easily. Could the maintenance be done faster with custom scripts? Yep – but when those scripts fail – and they will fail – we’ll make problem #2 worse, not better.
Maintenance plans aren’t perfect, but they require less troubleshooting time than your homemade scripts. I know, you think your scripts are perfectly bulletproof and would never possibly have a bug, but time after time, I do SQL Server health checks and find some amazing problems. Some highlights have included:
- A t-log backup script that failed every night at 11pm without sending a warning
- A 1mb database with 100GB of transaction logs because people thought it was in simple mode and their script skipped it by name
- Databases that had never been backed up because the script used the wrong variable name for the database
- DBCC jobs that completed successfully in under a second and nobody noticed. They were using sp_msforeachdb, which is notoriously unreliable.
Maintenance plans have a killer advantage over custom maintenance scripts because they don’t have your bugs. Let’s face it: we’re just not that good at writing code. If we were, we’d be developers, not DBAs. They do have bugs – but the bugs tend to be in bad implementation, like doing SHRINKDB, and those same problems pop up in custom maintenance scripts too. If you’re smart enough to write a maintenance script, then you’re smart enough to use maintenance plans correctly. (And conversely, if you’re not smart enough to use maintenance plans correctly, maybe you should ease up on those scripts, eh?)
Maintenance plans are like my iRobot. Their Scooba is a floor-cleaning robot that sprays cleaning solution on my wood floors, scrubs it (a little), and vacuums the liquid and dirt up. It doesn’t do a great job – in fact, it does a pretty crappy job, leaving a damp trail of cleaning solution behind. At first this bothered me, and I searched the web looking for ways to get it to do a perfect job and leave a dry floor behind. No dice – everybody complains about the dampness.
Thing is, J-Lo (that’s my name for her, because she’s On the Floor) does a better job of floor maintenance than I can do in my spare time. Once a week, I fill up J-Lo’s tank with cleaning solution, put in her battery, and turn her on. I watch her do her thing while I write blog posts (and she’s doing it right now – I know you want to watch, but this is a private show.) The cleaning solution evaporates, and I’m left with a cleaner apartment than I’d have had otherwise.
If you insist on using crafty scripts to do your database maintenance, use Ola Hallengren’s free scripts. They’re powerful and he even updates ’em frequently – just this week, he added support for the new preferred-replica-backup DMVs for SQL Server 2012’s AlwaysOn Availability Groups.
That’s the curse and blessing of scripts – even good scripts like Ola’s require maintenance because new versions come out from time to time. If you don’t have the time to maintain and debug scripts, use maintenance plans. Take all that time you save, and keep reading blogs to fix problems that actually bother your end users.