Any database management system requires constant or at least periodical maintenance of hosted databases. This is the case with SQL Server as well, and in order to make database administration tasks easier for DBAs, SQL Server provides the built-in Maintenance Plans feature

There is a range of functionalities that SQL Server Maintenance Plans offers, and these are designed in order to fulfill administration tasks either by using individual or appropriate combination of multiple functionalities:

• Rearrange the information in the data and index pages by rebuilding indexes with a specified fill factor. The fill factor is the option that provides fine-tuning of SQL Server index storage and performance
• A fill factor value (the ReindexPercentage task property) determines the amount (in percentage) of index page that will be reserved for future index growth. If the new data is regularly added to the end of the table, there is no need to change the default value of the fill factor – 0. Otherwise, in order to improve the overall index performance, it’s advisable to specify the form fill factor that fits regular inserts and updates. But a word of caution – in case the fill factor value is too big (e.g. 50), the database performance can be 50% reduced
• Compress data files by removing empty database pages and returning them to the file system
• Update index statistics is automatically performed. With this option, you can force immediate index statistics update in order to improve search performance for rapidly updated database
• Perform database internal consistency checks in order to verify whether a software or system issue hasn’t corrupted the data
• Back up the database and transaction log files in order to maintain the history of backups if a restore to a point in time is required
• Automatically run any defined Maintenance Plan task as a SQL Server Agent job using the Schedule option

There are two methods to create a SQL Server Maintenance Plan task – manual and via the Maintenance Plan Wizard option. The first one provides a wider range of options, properties, and flexibility during the creation, while the wizard offers a quick creation but with many of options and parameters (task properties) set to their defaults

To manually create a Maintenance Plan task in SQL Server:

1. In the Object Explorer tree, select the New Maintenance Plan option from the context menu of the Maintenance Plans folder

2. Using the Maintenance Plan Tasks toolbox, add the tasks you want to be performed at the scheduled time in desired order. To adjust the order of the task execution, use the arrows between the tasks as shown below

   

Each SQL Server maintenance plan consists of one or more subplans. The purpose of the subplans is to group tasks and provide different schedule for each subplan

Once saved, the maintenance plan can be modified or executed ad-hoc, without waiting for its scheduled time. Each maintenance plan automatically creates the corresponding SQL Server Agent job that can be used for additional fine tuning of the plan execution – scheduling, SQL Server alerting, and notifying (whether the job failed or succeeded) via email, net send, or the Windows Application event log entry

To use the wizard, in the Object Explorer tree, select the Maintenance Plan Wizard option from the context menu of the Maintenance Plans folder

Simply follow the click-and-point wizard dialog until the new SQL Server maintenance plan is created. This plan is editable the same way as manually created plans – after the wizard is closed, the created plan can be modified using the previously described designer window. The purpose of the wizard is to create appropriate tasks faster, with just a few clicks

However, either created manually or via the wizard, maintenance plans should be tested, by selecting the Execute option. Do not wait for the first scheduled execution to fail in order to fix plan settings omissions (e.g. the SQL Server and SQL Server Agent service accounts do not have full control of the backup directory)

Suggested practices on SQL Server Maintenance Plan tasks and scheduling

There are several suggested practices which take maintenance task consequences into consideration. Maintenance task benefits depend on SQL Server usage and are not applicable for all scenarios

The Shrink Database task is not recommended to be frequently scheduled, as it may cause the database performance issues and both index and disk fragmentation

As for the index related tasks (rebuild, reorganize, and update statistics), they can also be the source of performance issues with significant resource (e.g. CPU and hard disk) overheads

It is recommended to create full database backups daily, but in this case, you must check for the available disk space and remove old backups on time

The database integrity check is a very resource intensive operation and it should be scheduled during off hours

The practices and recommendations on specific maintenance tasks and scheduling are various and they depend on a SQL Server environment, usage, and company policy. The ultimate suggested practice is the one determined by a DBA, scheduled per needs and resource capabilities for a particular SQL Server.