SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

How Many DBAs Use the Maintenance Plan Wizard/Designer?

image 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.


Posted by Glenn Berry on 31 March 2010

My tip is to not use the Maintenance Plan Wizard/Designer. I purposely don't even show it to my students at Denver University - University College.

Instead, I show them how easy it is to create a SQL Agent job that calls either a stored procedure or some T-SQL to do maintenance operations.

Still, I agree with you that many novice DBAs do use the tool.

Posted by Jerry Hung on 31 March 2010

I think it goes both ways

Often, we don't need to kill a mosquito with a cannon

I use Maint. Plan for simple default boxes that don't need any fancy tools

Also for newcomers, it's easier to understand/edit the Maint. Plan THEN a custom stored procedure

I use SQL Backup now to schedule backup jobs anyway

I just want MSFT to have an Advanced Maint. Plan, that'll be awesome.

Leave a Comment

Please register or log in to leave a comment.