Let’s just get one thing out of the way first.
Yes, I understand that you, dear reader, probably hate maintenance plans. You associate them with everything dumb that you did early on in your career, like setting up a single plan that reorganized indexes, then rebuilt them, then updated their statistics. You are older and wiser now, and you swear by tools like Ola Hallengren’s maintenance scripts or Minion Reindex.
This blog post, however, is not about you.
It’s about all of the SQL Servers out there that have not yet had the wonderful opportunity to feel the tender loving hand of a qualified database administrator such as yourself. It’s about the tools that accidental DBAs will use over the years to come.
So let’s start with the index rebuild task:
Be still, my beating heart.
You can tell it’s kinda slapped together haphazardly – note the awkward spacing of the “Used in last” line at the bottom – but God bless ’em, Microsoft’s heart is in the right place. We have new options to only rebuild indexes if they’re a certain percent fragmented, or a certain size, or they’ve been used recently.
The goodness continues on the reorg screen:
Same nice options about only optimizing indexes that are in use, or are in bad shape.
The CHECKDB screen shows off its new MAXDOP capabilities, now that DBCC CHECKDB can take a hint:
Part of me is happy because undereducated database caretakers now have new, more powerful tools at their disposal.
The other part of me is a little bit sad because it’s still not easy to use. If maintenance plans are designed for the accidental and junior DBAs amongst us, I don’t think a lot of this stuff should even be an option. It should just default to the right thing, and take care of the database with Microsoft’s best practices set up as standard.
But that is a really, really small part of me. Maintenance plans are getting better, and that means something good.
12 Comments. Leave new
Nice enhancements MS, and thanks for showing us Brent. I haven’t had a chance to install it yet, but did they include “with Checksum” on backup? If not, maybe I’ll put it on my Christmas list…
I most definitely hate maintenance plans (and constantly see that exact horrible scenario you described of reorg+rebuild+update stats), but I agree this is a huge improvement. At least now there’s a chance that someone using maintenance plans could select some better choices.
Ahh maintenance plans. Those were so 3 months ago. Now that I’m in my 6th month of being a “DBA” and have moved on to Ola’s scripts, I’m with you – these improvements will be great for all the noobs out there. 🙂
But seriously, I have learned so incredibly much and our environment is in such better shape because of the fantastic resources you and the team have provide Brent.
thankyousir!
Judging from my experience with most accidental DBA’s I meet it looks like this will be a great tool in 2026 when their company lets them upgrade to SQL 2016
Hi Brent,
As add on, there are also new options in the “Backup Database Task”
– Perform checksum
– Continue on error
– Block size
– Max transfer size
But you’re right, except for Perform checksum, these settings will not be used by an accidental DBA.
Regards
Pieter
Yeah, those make more sense in Ola Hallengren’s scripts.
i still have no idea what im doing as a DBA, i’ll be leaving it all default. . .if they keep paying me i’ll keep doing it 🙂
I’m configuring my first 2016 SQL Server – all prior versions allowed me to create a maint plan for full backups writing to a local drive on the server. I have a domain acct that runs all sql server services and this account has full privs on the backup drive. The maint plan for b/u’s was created using a local login with sysadmin privs, however, when I execute the plan to run the backups, this local logins ends up getting locked out and the backups fail. What am I doing wrong?
Bev – for Q&A, head on over to https://dba.stackexchange.com
I’m trying to understand your hate for Maintenance Plans. Is it just lack of options/control with?
*within them
Mark – yep, and doing things like rebuilding indexes that don’t need to be rebuilt, causing excessive IO and transaction logging.