When I wrote my most recent free eBook, Brad’s Sure Guide to SQL Server Maintenance Plans, some of the DBAs I know asked me why I wrote it. After all, “real” DBAs don’t use the Maintenance Plan Wizard/Designer built into SQL Server 2005/2008 Management Studio (SSMS) to maintain their databases, they write Transact-SQL or PowerShell scripts instead. The reason I wrote the book is because there are many DBAs, many more than you think, who use this tool.

In a recent survey on my website (www.bradmcgehee.com) I conducted the following poll:

As you can see, 38% of respondents said they use this tool exclusively to create their database maintenance plans, and another 30% said they use it some of the time. This means that 68%, or about two-thirds of all SQL Server users, use the Maintenance Plan Wizard/Designer to build their server’s database maintenance plans. That’s a lot of DBAs. I imagine that most of these 68% are novice DBAs who don’t have a lot of experience using SQL Server and need a quick and easy way to perform database maintenance. On the other hand, I have had some experienced DBAs confide in me that they use the tool for less critical servers when time and convenience are the priority.

And this comes to the crux of why I wrote the book. While the Maintenance Plan Wizard/Designer may appear to be an easy tool to use, this ease of use is very deceptive. In reality, this tool is poorly designed and documented, and it allows novice DBAs to create horrendous maintenance plans that can give some DBAs a false sense of security, in addition to causing more performance problems than they resolve. So the goal of my book is to instruct novice DBAs on how to properly use the tool. Hopefully, once they have gained more experience, they will learn how to write their own maintenance scripts, or use some of the free ones provided on the Internet, such as the scripts provided by Ola Hallengren.

If you have used the Maintenance Plan Wizard/Designer, and have some tips you would like to share, please do so below.