The goal of implementing a database maintenance plan is to help prevent the kinds of problems just described. If implemented correctly, a database maintenance plan can help ensure that a SQL Server’s databases perform adequately and, if there should be a problem, provide the necessary backups to minimize the loss of any data. Another benefit of implementing a database maintenance plan is that it helps to prevent, or to catch early, many different kinds of database-related problems. By being proactive with a good maintenance plan, time spent troubleshooting problems after the fact is often reduced.

SQL Server provides two tools, built into SSMS, which make the process of creating database maintenance plans much easier and faster: the Maintenance Plan Wizard and the Maintenance Plan Designer. These tools can help DBAs to ensure that at least the most critical maintenance tasks are included in the maintenance plans, and performed on the database servers that they manage. In this article, I’ll examine what these critical tasks are, and discuss the pros and cons of using the Wizard and Designer to implement them, in contrast to using of T-SQL or PowerShell scripting.

If you feel, after reading this article, that the SSMS Maintenance Plan Wizard or Designer may be suited to your database maintenance needs, then you can find a full exploration of both of these tools in my free eBook, Brad’s Sure Guide to SQL Server Maintenance Plans.

The Scope of Database Maintenance Plans

If you were to ask ten different DBAs to define “database maintenance”, you would probably get ten different answers. The problem is that the term “database maintenance” is not clearly defined within the DBA community. Taken literally, the term refers to the maintenance of SQL Server databases. However, most DBAs confer on the term a more general meaning, encompassing maintenance of not only the databases, but also the SQL Server instances on which they reside, the OS, and the physical box on which SQL Server runs.

Every part of the larger SQL Server environment needs to be carefully managed and maintained in order to assure a high level of performance and availability. However, in the context of the SQL Server Maintenance Plan Wizard and Designer, I use a very specific meaning for the term database maintenance plan:

Definition: Database Maintenance Plan
A database maintenance plan is a set of specific, proactive tasks that need to be performed regularly on databases to ensure their adequate performance and availability.

In other words, the goal of the Maintenance Plan Wizard and Designer is to cover those critical database maintenance tasks that, as a bare minimum, should be applied to all databases, to ensure adequate performance and availability. These tools do not cover every database maintenance task that might need to be performed (see later) and they do not cover other issues surrounding the health of the broader SQL Server ecosystem. As such, while most of the tasks that the tools can help you configure are important, they are only a subset of all the things that a DBA needs to do to maintain healthy SQL Servers.

Is “adequate”, as opposed to “optimal”, performance good enough? This, ultimately, is a business decision, based on the nature of the business function that a given database supports, and on the amount of time, resources and money that the organization is prepared to invest. If an organization doesn’t have the resources (or isn’t willing to expend them) then, up to a point, it has to accept slower performance and lower availability from its SQL Servers.

This is a perfectly rational choice. Many SQL Server instances, especially those with small databases or a small number of users, often don’t need to be “optimized to perfection” for performance, or even be highly available. If a query takes a little while to return a result, or if a database goes down for a couple of hours, or even a day, the organization will continue to function. In such cases, the Maintenance Plan Wizard or Designer will suffice to ensure that the databases operate smoothly, and with acceptable performance. In general, these tools are well suited accidental DBAs, or full-time DBAs who are just starting out, and who manage smaller non- mission critical SQL Server installations.

Having said this, although they are sometimes reluctant to admit it, I know many experienced DBAs who still use the Maintenance Plan Wizard and/or the Maintenance Plan Designer from time to time. Alongside their “missions critical” systems, even experienced DBAs still maintain the databases of smaller, less active SQL Server instances and, for this purpose, these tools are the quickest and easiest way to create and schedule the schedule the set of maintenance tasks that will help ensure the continued smooth-running of these systems.

As for the databases that support mission-critical business functions, most DBAs will find that the limitations of the Wizard, and to a lesser extent the Designer, preclude their use in creating maintenance plans for such systems. In these cases, you will also need to invest time in creating more flexible and powerful maintenance plans, probably using T-SQL or PowerShell scripting, rather than using the Database Maintenance Wizard and Designer. Of course, organizations that choose to have highly performing and highly available SQL Servers have to make a large resource investment to attain this goal. There is no right or wrong maintenance plan; just different choices based on different needs.

Core Maintenance Plan Tasks

As discussed earlier, the basic intent of the Maintenance Plan Wizard and Maintenance Plan Designer is to allow you to configure the “core” database maintenance tasks that must be performed on more or less every SQL Server database. These tasks are reviewed in the following sections.

Backup Databases

As obvious as this advice sounds, it is surprising how many SQL Servers I have run across that don’t have proper backups. If your database becomes corrupt, and you don’t have a restorable backup, then you will probably end up losing your data.

It is critical that any maintenance plan makes provision for the following two types of backup:

• Full database backups – backs up the data in the data (mdf) file(s) for that database. Full backups are the core of any disaster recovery plan.
• Transaction log backups – backs up the data in the log (ldf) file(s) for that database.

While most people understand why full database backups are important, some don’t fully understand the rationale behind transaction log backups. The purpose of transaction log backups is twofold. Firstly, they serve to make a backup copy of all the transactions that have been recorded in the transaction log file since the last log backup. In the event of a disaster, these log backups can be applied to a restored copy of a full database backup, and any transactions that occurred after the full backup will be “rolled forward” to restore the data to a given point in time, and so minimize any data loss. For example, if you backup your transaction logs once an hour (and you have a valid full backup), then, theoretically, the most you could lose would be an hour’s worth of transactions.

Secondly, for databases that use the full or bulk-logged recovery models, this action truncates the transaction log, so that it doesn’t grow too large. Many part-time/accidental DBAs perform full backups on their databases, but they don’t perform transaction log backups. As a result, the transaction log is not truncated, and it grows and grows until the drive it is on runs out of disk space, causing SQL Server to stop working.

It is the responsibility of every DBA to ensure that all appropriate databases are properly backed up and protected.

Verify the Integrity of Database

It is possible for data in a SQL Server database to become corrupted, perhaps due to a failure in the disk subsystem, or some other event. While it is not common for a database to become physically damaged in this way, the possibility must be considered. Data corruption may occur only in one specific areas of the database and it’s possible that the damage may not be discovered for some time, usually until an attempt is made to query the corrupted data. Between the time at which the damage occurred, and the time it was discovered, many days may have passed, and each of the backups made during this time will include the damaged data.

The longer the damage remains undiscovered, the more out-of-date will be the most recent undamaged backup. If you delete older backups on a regular schedule, you may not even have an undamaged copy! In either case, you may end up losing a lot of data, so it is important for DBAs to regularly check the physical integrity of their databases, using the DBCC CHECKDB command.

Maintain a Database’s Indexes

Over time, as indexes are subjected to data modifications (INSERTs, UPDATEs, and DELETEs), index fragmentation can occur in the form of gaps in data pages that create wasted empty space, and in a logical ordering of the data that no longer matches the physical ordering of the data.

Both forms of fragmentation are normal byproducts of data modifications but, unfortunately, both can hurt SQL Server’s performance. Wasted space reduces the number of rows that can be stored in SQL Server’s data cache, which can lead to increased disk I/O. The index page ordering problem also causes extra disk activity, as it often takes more work to find the data on disk and move it to the data cache, than if the pages were in physical order.

SQL Server doesn’t automatically correct index fragmentation problems. The only way to remove wasted space and restore the correct page ordering is to rebuild or reorganize the indexes on a regular basis. This requires the DBA to create such a maintenance job.

Maintain Index and Column Statistics

The Query Optimizer uses index and column statistics as part of its evaluation process, as it tries to determine an optimal query execution plan. If the statistics are old, or incomplete, then the Query Optimizer might create an inefficient execution plan, which substantially slows down a query’s performance. In theory, index and column statistics are self-maintaining, but in practice this self-maintaining process is not perfect.

In order to ensure that the optimizer has the most complete and current statistics at its disposal, the DBA needs to create a maintenance task to ensure that they are regularly updated, either by rebuilding the indexes, or by updating the statistics using the UPDATE STATISTICS or sp_updatestats commands.

Remove Older Data from msdb

The SQL Server msdb database stores historical data about various activities, such as details about backups, SQL Server Agent jobs, and Maintenance Plan execution. If left unattended, over time, the msdb database can grow to a considerable size, wasting disk space, and slowing down operations that use the msdb database. In most cases, this data does not need to be kept for a long period, and should be removed using such commands as sp_delete_backuphistory, sp_purge_jobhistory, and sp_maintplan_delete_log.

Remove Old Backups

While making database backups is important, you don’t need to keep them forever. If fact, if you don’t clean up older backup files, your SQL Server’s hard drives will quickly fill up, causing all sorts of problems. It is the job of the DBA to ensure that unneeded backups are removed from a SQL Server on a regular basis.

Database Maintenance Plans in SSMS

There are many different ways that DBAs can choose to implement the core maintenance tasks just described. In this section, we’ll take a brief overview of the two tools that are built into SSMS, the Maintenance Plan Wizard and the Maintenance Plan Designer, and assess their pros and cons.

Maintenance Plan Wizard

The goal of the Maintenance Plan Wizard is to guide you, step-by-step, through the creation of a Maintenance Plan, without the need to do any coding, thus making the whole process easy and quick. While the Wizard doesn’t include every possible database maintenance feature or option, it does include the core database maintenance tasks that all DBAs should be performing on their SQL Servers (discussed above). As such, it is often an appropriate tool for the part-time/accidental DBA, or even full-time DBAs. For example, if the databases are small, the number of users is low, high server availability is not required, and there are available maintenance windows, then this tool is more than adequate in most cases.

A note on terminology
SQL Server uses the term “Maintenance Plan” (note the capitalization) to refer to a database maintenance plan created using either the Maintenance Plan Wizard or the Maintenance Plan Designer.

Under the covers, each Maintenance Plan takes the form of an SSIS package, which is then scheduled to run under one or more SQL Server Agent jobs, and will perform the various tasks that make up a database maintenance plan.

In many ways, the Maintenance Plan Wizard does attain its goal of easing the creation of database maintenance plans. However, in some areas, it falls short, and can cause problems for the incautious. The Wizard assumes that you fully understand every option that it offers to you, and how it affects your databases. If you don’t understand the options, and you guess at their meaning, it is very easy to create a Maintenance Plan that performs terribly. Unfortunately, the Wizard is not smart enough to prevent you making these poor choices.

As useful as the tool can be, DBAs must be fully aware of what it can and can’t do. Having created a few Maintenance Plans with the Wizard, some novice DBAs confidently assume that that their databases are fully maintained. As we have already discussed, the Maintenance Plan Wizard only performs core maintenance tasks, and not every possible database maintenance task that should be considered for a given database or server. For example, just because you create backups with the Wizard, this does not ensure that the backups are good (restorable), or that they have been moved off the server to protect them should the SQL Server instance experience a disk failure. Such tasks (other examples are covered a little later) have to be done outside of the Maintenance Plan Wizard.

The Wizard also has the following, specific shortcomings:

• Limited number of database maintenance options. If you need database maintenance options that are not provided, you’ll have to resort to T-SQL or PowerShell scripts, or to use scripts for some tasks and the Wizard for others.
• Lack of granularity. For example, the Maintenance Plan Wizard can’t determine which indexes need to be rebuilt, and which ones don’t need to be rebuilt, and therefore has to rebuild them all. As such, it often takes more time to execute a Maintenance Plan created with the Wizard than a custom plan created using T-SQL or PowerShell scripts.
• Inability to run multiple tasks. Each type of maintenance task within a single Maintenance Plan can only be configured to run once within that Plan. This can make some tasks more difficult than they need to be. For example, the maintenance task that is designed to delete older backup files can only delete one file type at a time, such as BAK or TRN, and not both at the same time. Because of this, you may have to create multiple Maintenance Plans just to perform simple tasks such as this.
• No scripting to other instances. Maintenance Plans created with the Wizard cannot be scripted and moved to other SQL Server instances, although multi-server Maintenance Plans can be created.
• Bugs in some earlier versions of the Wizard. If you use SQL Server 2005 Service Pack 2 or higher, or SQL Server 2008, then you should have no problems.

Some experienced DBAs will tell you that “real DBAs” don’t use the Maintenance Plan Wizard and instead always write their database maintenance plans from scratch, using T-SQL or PowerShell scripts. In reality, this is not true. Many “real DBAs” use the Maintenance Plan Wizard, when it is appropriate.

Maintenance Plan Designer

If you search for the “Maintenance Plan Designer” in Books Online, you won’t find anything referred to by this exact name. This is because I had to provide a name for a feature of SQL Server that does not appear to have a consistently-used, official name. Sometimes it is referred to as “New Maintenance Plan”, or the “Maintenance Plan Design Tab”, and other times as the “Maintenance Plan Designer Surface”.

Essentially, the Maintenance Plan Designer is a drag-and-drop GUI interface found in SSMS, based on the SQL Server Integration Services (SSIS) Designer Surface, which allows DBAs to manually design and create Maintenance Plans from scratch, or to modify Maintenance Plans originally created using the Maintenance Plan Wizard.

The Maintenance Plan Designer offers more features than the Wizard and this, coupled with the element of manual control, means the DBA can create more comprehensive, flexible and customized Maintenance Plans than is possible with the Wizard.

One advantage of the Designer over the Wizard, in my opinion, is that it shows you the T-SQL code that will be executed when a maintenance task runs. This code can help provide you with a better understanding of exactly what the task is doing, and can also be used as an example of how to use T-SQL to create your own maintenance plans, should you decide to write your own T-SQL code to enhance your Maintenance Plans. In addition, the Designer tool has the following specific advantages:

• Control-of-flow ability. The Designer allows you to create branching execution paths based on conditional logic. For example, you can specify that if a particular maintenance task fails, that an e-mail is sent to the DBA team, notifying them of the problem.
• Running multiple tasks. Unlike the Wizard, you can run a task multiple times from within the same Maintenance Plan. This solves the problem described earlier with the Maintenance Plan Wizard. Now, within a single plan, you can delete both BAK and TRN files within a single Maintenance Plan.
• Two additional tasks, only in the Designer. An Execute T-SQL Statement task allows you to create a maintenance task that can do virtually anything, and have it run from within a Maintenance Plan. A Notify Operator task provides a powerful means to notify a DBA should a maintenance task fail to execute successfully.

Of course, the most obvious drawback of using the Designer is that it is a manual procedure and so is slower, and somewhat harder to learn than the Wizard.

Despite offering greater flexibility than the Wizard, the Designer still cannot match the power and flexibility of T-SQL and PowerShell scripts. In fact, aside from the ability to add conditional logic, the ability to run a task multiple times within a Plan, and the addition of two more tasks, the Designer suffers from most of the shortcomings listed for the Wizard.

Many DBAs might start off using the Maintenance Plan Wizard, but once they have mastered it, they often take the time to learn the additional features of the Maintenance Plan Designer, because the leap from learning the Wizard to the Designer is not a large one and, at the same time, they are gaining greater flexibility when creating Maintenance Plans.

Manual Scripting of Database Maintenance Plans

Many experienced DBAs manually script their database maintenance tasks. Writing the scripts is, of course, slower and more error-prone than using the Wizard or Designer, but it does offer a level of flexibility that is not available in either of these tools.

T-SQL Scripts

Today, most full-time, experienced DBAs use T-SQL scripts, in combination with SQL Server Agent jobs, to perform their database maintenance. This is because T-SQL scripts offer 100% flexibility when it comes to database maintenance; virtually anything you want or need to do, you can.

For example, if you specify the Rebuild Index task in the Maintenance Plan Wizard, it will automatically rebuild all the indexes in a database. While this accomplishes the job of rebuilding indexes it is a resource-intensive process. The ideal solution is to run a script that identifies only the heavily fragmented indexes, and rebuilds them, but leaves the others alone, thus conserving server resources. Unfortunately, you can’t do this with the Maintenance Plan Wizard; custom T-SQL or PowerShell scripts are required.

In addition, T-SQL scripts offer the following advantages:

• OS access. T-SQL offers the ability to access the Operating System (OS), although it is not always easy or as flexible as you might like. This is one option used by some DBAs to removed old BAK and TRN files.
• Portability. Appropriately written T-SQL scripts can easily be moved from server to server.
• Script sharing. Many DBAs share generic database maintenance T-SQL scripts on various community sites, so you don’t have to reinvent the wheel. Of course, you don’t want to run a script on your own server unless you fully understand what it does. You still need a good knowledge of T-SQL before using someone else’s T-SQL script. Check out these URLs for some examples of some freely availably T-SQL scripts used to perform database maintenance:
  • http://ola.hallengren.com/
  • http://www.grics.qc.ca/YourSqlDba/index_en.shtml
  • http://www.simple-talk.com/sql/database-administration/sql-server-tacklebox-free-ebook/

Of course, all of this assumes a strong working knowledge of the T-SQL language, as well as a good understanding of SQL Server internals. For most people, this entails a long learning curve. Coding T-SQL scripts can be very time-consuming, and error prone. Sometimes debugging these scripts takes longer than writing them. In addition, if you are not careful about how you write your maintenance scripts, it is possible that when the next version of SQL Server is released, your scripts may need to be modified (sometimes substantially) to work with the new version.

Finally, aside from third-party tools, there is no easy way to automate the execution of your T-SQL maintenance scripts across multiple servers. For that, you will need to learn PowerShell.

While T-SQL scripts might be the choice of most DBAs today, don’t think this is the only option you have. If you want to keep database maintenance simple, then the Maintenance Plan Wizard and the Maintenance Plan Designer may work perfectly well. However, if you need an even more flexible option than T-SQL, consider using PowerShell scripts.

PowerShell Scripts

PowerShell is Microsoft’s latest command-line shell scripting language that allows DBAs full access to the object models of both the OS and SQL Server. It also supports much more complex logic than T-SQL and has better error-handling. This combination allows you to create extremely powerful and robust database maintenance scripts. PowerShell scripts, if written appropriately, can easily be used to perform database maintenance across multiple SQL Servers.

Microsoft has been avidly promoting PowerShell, although adoption has been slow, the main reason being that it involves learning a completely new object-oriented scripting language, which is very alien to many DBAs. On top of this, the DBA still needs to know T-SQL and SQL Server internals, as well as SQL Server Object Model (SMO), and the OS Object Model (assuming you decide to take advantage of PowerShell’s ability to access the OS).

This is a steep learning curve and means that PowerShell scripts, initially at least, can be even more time-consuming to write and debug than T-SQL. Also, whereas the appropriate T-SQL maintenance script can be run on most any SQL Server, many older servers many may not have PowerShell installed.

As time passes, I am guessing that you will see more and more DBAs start moving from T-SQL scripts to PowerShell scripts, especially those who manage large numbers of SQL Server instances. This will continue to be a slow move, until more DBAs not only become familiar with the power and flexibility of PowerShell, but master the large body of knowledge needed to take full advantage of it.

In the meantime, the body of community scripts and knowledge is starting to grow. For examples of how to use PowerShell to perform database maintenance, check out this CodePlex.com project.

http://sqlpsx.codeplex.com/

Alternatively, you can visit http://www.simple-talk.com and do a search for “powershell”, to find many articles on the subject.

What’s Outside the Scope of the Maintenance Plan Wizard and Designer?

While Maintenance Plans are a convenient way to perform much of your database maintenance work, neither the Wizard nor the Designer can do all your work for you. While the tasks included with Maintenance Plans are a good first start, the Wizard and designer aren’t really intended to enable you to perform every single maintenance task that could be included in your database maintenance strategy.

For example, the following, additional important database maintenance tasks are not covered by the Wizard or Designer:

• Identifying and remove physical file fragmentation.

• Identifying missing, duplicate, or unused indexes.
• Protecting backups so that they are available when needed.
• Verifying that backups are good and can be restored.
• Monitoring performance.
• Monitoring SQL Server and operating system error messages.
• Monitoring remaining disk space.
• And much, much more.

The moral of the story is that, while Maintenance Plans are a useful tool for many DBAs, they are not the perfect tool for all DBAs, and will only perform a subset of the required database maintenance tasks. If the Maintenance Plan Wizard or Designer meets your needs, then use them. On the other hand, if they don’t meet your needs well, then don’t use them. Custom-created T-SQL or PowerShell scripts instead offer much more power and flexibility. While there may be a steep learning curve to create custom scripts, this is knowledge that you will be able to use elsewhere as a DBA, and it won’t go to waste.

Keep your eye on the Simple Talk Newsletters in the coming weeks for a complete version of Brad’s new book, in eBook format. This will be available for free to members of Simple Talk.