SQL Server 2016 Maintenance Plan Improvements

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:

Index rebuilds in SQL Server 2016

Index rebuilds in SQL Server 2016

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:

Reorganizing indexes in SQL Server 2016

Reorganizing indexes in SQL Server 2016

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:

maintenance-plan-checkdb

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.

Previous Post
What to Do When DBCC CHECKDB Reports Corruption
Next Post
[Video] Office Hours 2016 2016/05/18 (With Transcriptions)

12 Comments. Leave new

  • Joe O'Connor
    May 20, 2016 9:35 am

    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…

    Reply
  • 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.

    Reply
  • 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!

    Reply
  • 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

    Reply
  • Pieter Vanhove
    June 29, 2016 3:23 pm

    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

    Reply
  • 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 🙂

    Reply
  • 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?

    Reply
  • Mark Earleywine
    May 2, 2018 12:40 pm

    I’m trying to understand your hate for Maintenance Plans. Is it just lack of options/control with?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}