SQL Server maintenance plans get a bad rap, but for DBAs who need a simple way to maintain databases, Microsoft's built-in tools are a good place to start.
SQL Server maintenance plans have been around since SQL Server 2000, but they've always been met with mixed reviews. This is in part because of their reputation for taking a sledgehammer approach to maintenance, particularly with indexes.
Maintenance plans have also gotten a bad rap because they don't include the intelligence necessary to prevent redundant operations -- such as unnecessary statistics updates -- or destructive operations -- such as shrinking databases without accounting for the consequences.
Over time, many DBAs have turned to third-party methods to address their maintenance needs, using tools like Minion Reindex or Ola Hallengren's SQL Server Maintenance Solution, which is a comprehensive set of free scripts.
Even so, Microsoft has introduced several improvements to maintenance plans over the years. For some DBAs, they represent a viable approach to maintenance -- especially if administrators are new to SQL Server or if their hands are tied by company policies that prevent the use of third-party methods.
Introducing SQL Server maintenance plans
A maintenance plan is a defined workflow that runs a set of preconfigured maintenance tasks, such as backing up databases, rebuilding indexes or running SQL Server Agent jobs. Tasks can be configured to run in a specific order or grouped into sub-plans and scheduled to run at different times.
SQL Server also supports maintenance plans that span multiple SQL Server instances. A multiserver plan requires one instance that is designated as the master server and other instances that are target servers. The maintenance plan must be created and maintained on the master server, but it can be viewed on the target servers.
Administrators can run maintenance plans manually or schedule them to run at specified intervals. When a DBA creates a master plan, SQL Server generates a SQL Server Integration Services (SSIS) package made up of individual maintenance tasks. SQL Server also sets up a SQL Server Agent job, which runs the SSIS package.
A maintenance plan that has been carefully designed and scheduled can ensure that administrative tasks are performed in a timely manner, while also reducing the need to perform them manually, giving DBAs more time for other tasks. More importantly, SQL Server maintenance plans can ensure that databases are being regularly optimized so performance doesn't degrade over time. A database that is not carefully maintained can impact application performance and end-user productivity, as well as put data at risk.
Implementing maintenance plan tasks
A maintenance plan in SQL Server is made up of one or more predefined tasks configured to meet an organization's specific needs. For example, several tasks are available to back up database files and transaction logs. Administrators can schedule full or differential database backups, target specific destination files or tapes, and specify the amount of time for which to retain backups.
SQL Server also provides predefined tasks to reorganize and rebuild indexes. A reorganization operation compacts and defragments clustered and nonclustered indexes on tables and views in the target database, improving scan performance. A rebuilding operation reorganizes the data on the data and index pages, which can improve seek and scan performance. Rebuilding the indexes also optimizes how data and free space are distributed on index pages.
Additionally, SQL Server provides a task to shrink databases, a process that recovers disk space by removing empty data and log files. To accomplish this, the database engine must move data near the end of the file to empty space near the front of the file. Although this results in more disk space, it also causes significant fragmentation, which is why many seasoned DBAs warn against using this option. If shrinking a database is unavoidable, the indexes should be rebuilt afterward.
SQL Server also provides tasks to update statistics and check database integrity. When statistics are updated, the database engine provides the query optimizer with up-to-date information about the data in tables and indexes, which can lead to better-performing queries. When a database integrity check is carried out, the database engine provides administrators with a report on database integrity issues that they can use to resolve the issues at a later time.
SQL Server also provides tasks to run T-SQL statements and SQL Server Agent jobs, as well as to clean up files. For example, a maintenance plan can include a task that deletes historical data that results from backup, restore, maintenance and SQL Server Agent operations.
There is also a task that targets files left over from executing a maintenance plan. In addition, SQL Server provides a task to automatically send email to DBAs or other recipients with details about the plan.
Creating maintenance plans in SQL Server
There are three supported methods to create SQL Server maintenance plans: running the Maintenance Plan Wizard in SQL Server Management Studio (SSMS), building a maintenance plan package on the Maintenance Plan Design Surface -- which is also in SSMS -- or writing a T-SQL script that manually defines the maintenance plan.
The simplest approach is to use the Maintenance Plan Wizard, which walks DBAs through each step of creating the plan. The DBA picks the tasks the plan should include, arranges them in the order in which they should run and configures each selected task for the organization's specific needs. The wizard then builds the plan based on the input.
The approach that DBAs take to create maintenance plans will depend on their level of expertise and each plan's requirements.
Although the wizard is handy for basic plans, DBAs have more control over the workflow and more tasks available to them when using the Maintenance Plan Design Surface, which works similarly to SSIS. The DBA arranges tasks on the design surface and then connects them to precedence constraints that determine the order in which the tasks should run. However, the individual task configurations are much like those in the Maintenance Wizard.
The third approach, using a T-SQL script to define a maintenance plan, starts with creating a SQL Server Agent job, adding one or more steps to the job, defining the schedules that determine when the job is run, and attaching the schedules to the job. An administrator can use the following built-in stored procedures to define a plan:
dbo.sp_add_job: Adds a job to the SQL Server Agent service for the maintenance plan.
dbo.sp_add_jobstep: Adds a step to the maintenance plan job that includes the T-SQL code necessary to carry out a specific maintenance operation, such as rebuilding indexes or backing up databases. A job can include multiple steps.
dbo.sp_add_schedule: Adds a schedule to SQL Server Agent that can be applied to one or more steps in the maintenance plan job. A schedule defines such specifics as start time and frequency. A maintenance plan can include multiple schedules.
dbo.sp_attach_schedule: Attaches a schedule to a maintenance plan job.
The approach that DBAs take to create maintenance plans will depend on their level of expertise and each plan's requirements. In some cases, the wizard might be all they need to set up a maintenance plan. Other times, they might want the control they can get with the Design Surface or T-SQL. They can also use the wizard to create a plan and then use the design surface to edit it.
Getting comfortable with SQL Server maintenance plans
The debate around maintenance plans is not going away anytime soon. With high-quality tools already available, such as those in Ola Hallengren's SQL Server Maintenance Solution, Microsoft will have to make a lot more improvements before some DBAs will be ready to use the built-in approach.
That said, for those new to SQL Server or those looking for a quick and simple way to maintain their databases, the Maintenance Plan Wizard and Maintenance Plan Design Surface are a good place to start. As with any SQL Server feature, database teams must decide for themselves what tools best fit their specific circumstances.
