SQL 2005 includes a graphical tool called the Maintenance Plan Wizard that can be used to create database optimization and integrity checking packages. Some of the tasks the Maintenance Plan Wizard can orchestrate include backing up the database, reorganizing data and index files, compressing the data file, and updating the index statistics. While all of these tasks can be created using TSQL commands, the Wizard provides an easy way of selecting these tasks and their options. Packages created by the Wizard can be executed on command or scheduled as reoccurring jobs. In addition, the wizard will roll all of your selected tasks into a reusable and customizable package that can be edited in the Maintenance Plan Designer, which well explore in this article.
Listed below are tasks available to the Maintenance Plan Wizard.
- Check Database Integrity
- Shrink Database
- Reorganize Index
- Rebuild Index
- Update Statistics
- Clean Up History
- Execute SQL Server Agent Job
- Backup Database (Full, Differential, Transactional)
In Part 1 of this series, the first four tasks (Database Integrity, Shrink DB, Reorganize and Rebuild Index) were discussed. Well continue with the task Update Statistics. All of the examples in this series will act on the Microsoft SQL Server Adventure Works database.
Before proceeding with the examples, ensure Agent XP is enabled on the server. Agent XP allows SQL Server Agent Extended Stored Procedures to run. The following TSQL enables Agent XP:
USE master; GO sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Agent XPs', 1; GO RECONFIGURE GO
Statistics boost SQL Server query performance by indicating to the engine the most optimal execution plan. However, as rows are added, deleted, or modified in a table, these statistics can become out of date. The Update Statistics command will force the SQL Engine to reexamine statistics on columns and indexes.
The following steps are used to create an Update Statistics Job using the Maintenance Plan Wizard.
From Object Explorer, expand Management; right click Maintenance Plans, then select Maintenance Plan Wizard.
The Maintenance Plan Wizard splash screen should appear, click Next.
On the Select a Target Server screen, give the job being created a name and select the target server and authentication method, then click Next.
Select Update Statistics then click Next.
The Select Maintenance Task Order screen should appear next. Because only one task has been selected, only Update Statistics should appear. Click Next.
On this next screen, Define Update Statistics Task pictured below, a database can be selected as well as selected to examine Tables, Views, or both. Here were also given the choice of updating just Column Statistics, Index Statistics, or both. This example selected both.
Click Next to save the options.
The Plan Properties screen should appear next. Here scheduling choices can be selected. The options presented appear below.
These same scheduling choices appear for all the tasks options available in the Wizard. For this example, none were selected, making this an On Demand task. It can be executed at any time by clicking on it in the Management Studio.
The next screen, Report Options, gives choices for logging. Clicking Next brings us to the final screen. Click Finish to create the job. As the job is created, a green check mark should appear next to each step as shown below.
Click Close to exit the Wizard. Return to the SQL Server Management Studio and refresh the Maintenance Plans folder. The newly created job should appear in the list. To start the job, right click its name and select Execute.
Below is a sample of the TSQL code generated by the Maintenance Plan Wizard.
use [AdventureWorks] GO UPDATE STATISTICS [HumanResources].[Department] GO
To view the actual TSQL executed, as well as any messages reported as the job runs, check for a log file in the MSSQL\Log folder. The log file should have the name of the job followed by the date time it was executed.
In addition to viewing the log file by hand, Maintenance Plan logs can be viewed in the graphical Log File Viewer. To invoke the Viewer, right click the Maintenance Plans folder located inside the SQL Server Management Studio and select View History.
The Log File Viewer, pictured below, can present execution details as well as export log notes.
Maintenance Plan Designer
In addition to the Maintenance Plan Wizard, plans can be created and edited in the Maintenance Plan Designer. To open the Designer, right click the newly created job and select Modify, or to create a new Job, right click the Maintenance Plan folder in the Object explorer and select New Maintenance Plan. The Designer should appear as pictured below.
If the Maintenance Plan Tasks Toolbox doesnt automatically appear, select View, Toolbox from the top Management Studio menu.
All the task options available from the Wizard are available in the Designer. There are also some Designer only features.
The Designer Connection and Logging buttons offer similar features found in the Wizard. To edit the options for the job, double click the Update Statistics box in the right pane of the designer. To add a new task to the job, select the Task type icon from the toolbox and drag it onto the right pane of the Designer. Tasks can be linked and ordered by connecting a green flow arrow to each as shown below.
In addition, task links can be conditionally based on Success, Failure, or Completion. Right click the flow arrow to change its status. A green arrow indicates a task path that should be followed on success, a red arrow means failure, and a blue arrow indicates a Completion path.
Also, general notes can be added to package by right clicking in the designer pane and selecting Add Annotation.
Between the Maintenance Plan Wizard and the Maintenance Plan Designer, intricate SQL Server 2005 plans can be easily created. In the next article, additional tasks such as Backups will be discussed.