Introduction
There are a number of database housekeeping tasks that should be done on a regular basis to insure SQL performance and data integrity are optimized. Backing up the database, reorganizing data and index files, compressing the data file, updating the index statistics, and performing consistence checks are a few examples. All of these tasks can be executed using TSQL commands. However, SQL 2005 includes a graphical wizard (Maintenance Plan Wizard) that makes selecting these tasks and their options easy. In addition, the wizard will roll all of your selected tasks into a reusable and customizable package.
Wizard
To start the Maintenance Plan Wizard, open the SQL Server Management Studio; from inside the Object Explorer expand the Management folder. (If the Object Explorer isn’t visible from inside the Management Studio, select from the top menu View, then Object Explorer.) Right click “Maintenance Plans”, and then select “Maintenance Plan Wizard” as shown below.
The initial splash screen for the wizard should appear. Click Next.
The “Select a Target Server” should appear. Give the plan a Name and select the SQL server it will target. Next select the authentication method the plan should use: either Windows or SQL Server. Which ever method is selected; ensure the account has enough permission to act on the database. Click Next.
This next screen, “Select Maintenance Tasks”, as shown below, presents a list of all the available tasks the wizard can configure.
For this article, all the options will be checked and each will be covered in the order the wizard presents them.
Clicking “Next” brings up the “Select Maintenance Task Order” screen. Typically, if I have backups mixed with other tasks, I’ll move the backup to the top so it executes first, prior to any maintenance, just in case data should corrupt during the job.
Database Check Integrity
The first task, “Data Check Integrity”, checks the structural integrity of the database. Examples would include checking disk space allocations inside SQL, checking consistency between system metadata tables, and checking the structures that make tables. For a complete description of what “Check Integrity” means, see the Microsoft online article “DBCC CheckDB” located at http://msdn2.microsoft.com/en-us/library/ms176064.aspx. This task is extensive and resource intensive. Schedule or execute this action at low production times. Also, insure TempDB has adequate hard disk space available.
As with most of the wizard screens, an option lets you choose which, or all databases to target. For this article, the database AdventureWorks will be used.
On the screen, there is an optional checkbox that will include or exclude indexes in the check. This example included checking them. The following TSQL is the code actually created by the wizard.
USE [AdventureWorks]
GO
DBCC CHECKDB WITH NO_INFOMSGS
The parameter “WITH NO_INFOMSGS” prevents the command from logging Informational messages, which can be very verbose. Towards the end of this article series, logging locations and options will be discussed.
Shrink Database Task
This task attempts to reduce the physical size of the database and logs. The value entered into “Shrink database when it grows beyond” will be evaluated when the job begins. If the database size is larger than the entered value, the task will execute.
The next value, “Amount of free space to retain”, directs SQL server to leave a certain percentage of free space in the database. If there were zero free space, then each database write would require the SQL engine to request additional disk space from the operating system which would degrade SQL performance.
Select “Return freed space to operating system” if you would like any space SQL reclaimed given back to the operating system. Otherwise, the “Retain” option keeps the space allocated to SQL.
Below is the TSQL generated by the wizard for this task.
USE [AdventureWorks]
GO
DBCC SHRINKDATABASE(N’AdventureWorks’, 10, TRUNCATEONLY)
For a detailed explanation of the ShrinkDatabase command, and how files are actually shrunk, see the Microsoft article “DBCC ShirkDatabase” located at http://msdn2.microsoft.com/en-us/library/ms190488.aspx. About half way through is a section called “How DBCC ShrinkDatabase WORKS”.
Reorganize Index Task
The simple explanation of this task is that the selected index will be defragged. Fragmentation, to quote Microsoft is defined as, ”Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file.” For a complete description of Index fragmentation, see the Microsoft article “Microsoft SQL Server 2000 Index Defragmentation Best Practices” located at http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx . About midway through is a section titled “Understanding Fragmentation”. Excessive Index fragmentation can adversely affect performance. The Reorganize Index task will correct this and may improve query response.
The Object choice lets you specify which type of indexes to reorganize, those that have been created off Tables, Views, or both. (In SQL 2005 a View can have an Index, see Books On Line “Indexed Views”).
Checking the next option “Compact Large Objects”, would only affect the data types Image, Text, XML, and MAX.
The following TSQL code was generated by the wizard. There will be a separate statement for each index.
USE [AdventureWorks]
GO
DBCC SHRINKDATABASE(N’AdventureWorks’, 10, TRUNCATEONLY)
Rebuild Index Task
This task recreates an index with new settings you define in the wizard. Although this task can Reorganize an index, like the previous “Reorganize Index Task”, this task is more suited to changing the Create options on an Index.
The “Change free space per page” option determines the percent of free space to leave in each Index Page file. (A Page is an 8,192 byte block of space inside the database file.)
“Sort results in tempdb” places the working file for a sort during the Rebuild task into the tempdb table rather than memory.
The “Ignore Duplicate Keys” check box can be used to tell the index to disregard duplicate keys being entered during a multiple row insert.
“Keep Index Online”, if checked, will allow users to access and query off the existing index while the new index is created.
Below is the TSQL code created by this wizard. There will be one statement per index.
USE [AdventureWorks]
GO
ALTER INDEX [PK_AWBuildVersion_SystemInformationID]
ON [dbo].[AWBuildVersion] REBUILD WITH
( FILLFACTOR = 90,
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
SORT_IN_TEMPDB = ON, ONLINE = ON )
Conclusion
The SQL Server 2005 Maintenance Plan Wizard offers many core tasks and options for database housekeeping. It’s very quick and intuitive to use. In addition, the packages it creates can be saved and modified. In the next article, additional maintenance tasks such as Updating Statistics and Backups will be covered.
