A Time and a Place for the SQL Server Maintenance Plan Wizard

Reprinted from my editorial in Database Weekly.

In the course of my job, I get to give a lot of presentations, at various conference and user group events, many of them offering advice to DBAs on how to maintain their SQL Server databases. Aware of its limitations and failings I, like many other experienced DBAs, initially had a rather dismissive attitude towards use of the SQL Server Maintenance Plan Wizard for database maintenance. I advised my audience to avoid it and instead create their own custom maintenance scripts using T-SQL or PowerShell.

Of course, the great thing about giving presentations is that you don’t just get to talk at people; you get to talk with them. Over the course of many post-seminar conversations, I began to realize that a significant proportion of the attendees were part-time, accidental, or novice DBAs, most of whom were using the Maintenance Plan Wizard. It dawned on me that, in effect, I was telling them that they were not doing their job correctly, and I began to question my stance on the tool.

It’s certainly true that the Wizard has many significant limitations and, if used incorrectly, it can hurt SQL Server’s performance. However, it is equally true that a) if you know how to use it properly it will do a lot more good than harm and b) there are countless part-time and accidental DBAs out there who don’t currently have the knowledge and experience to create their own custom scripts, and for whom finding the time and the training to acquire these skills is a slow process. For these DBAs, as they learn their trade, a simple tool that will help make sure essential database maintenance is carried out is an extremely attractive and viable option.

I thought I had been offering good advice but, by being so dismissive of the Maintenance Plan Wizard, I realized that I was effectively implying that “If you aren’t smart enough to create your own maintenance plans using T-SQL, then you shouldn’t be a DBA”. It’s a sentiment I hear echoed by many experienced DBAs; but that doesn’t mean it’s a very helpful one for the novice DBA. All DBAs start out as novices. Eventually, they become more experienced DBAs, but during the transition period, tools such as the Maintenance Plan Wizard, and its companion, the Maintenance Plan Designer, can still be used to good effect, even though they are less-than-perfect.

I decided to “put my money where my mouth is”. I changed my recommendation to avoid the Wizard, created a new series of presentations on how to use it, and have just completed a book,  Brad’s Sure Guide to Database Maintenance, advising DBAs how to get the most out of the Maintenance Plan Wizard and Designer, and get past some of its shortcomings. My goal is to help DBAs to maintain their databases as well as they possibly can, given their current level of knowledge, the time they have, and the needs of their database systems. Over time, then they can decide whether to continue using the Wizard and Designer, or start creating custom T-SQL or PowerShell scripts. Ultimately, it really doesn’t matter how a DBA implements optimal database maintenance, as long as the goal is achieved.