Database Maintenance Plan, Optimizations Tab

SQL Server Enterprise Manager Help

SQL Server Enterprise Manager Help

Database Maintenance Plan (Optimizations Tab)

Use this tab to reorganize your data and index pages, allowing the query optimizer better access to execution plans.

Options

Reorganize data and index pages

Cause table indexes in the database to be dropped and re-created with a new fill factor. The FILLFACTOR determines how much empty space to leave on each page in the index and reserves a percentage of free space on each data page of the index to accommodate future expansion. As data is added to the table, the free space fills up because the FILLFACTOR is not maintained. Reorganizing data and index pages can reestablish the free space.

Reorganize pages with the original amount of free space

Drop and re-create table indexes in the database with the original FILLFACTOR that was specified when the indexes were first created.

Change free space per page percentage to

Drop and re-create the indexes with a new, automatically recalculated FILLFACTOR, thereby reserving the specified amount of free space on the index pages. The higher the percentage, the more free space is reserved on the index pages and the larger the index grows. Valid values are 0 to 100.

Update the statistics used by the query optimizer

Resample the distribution statistics of each index created on user tables in the database. The distribution statistics are used by Microsoft® SQL Server™ to optimize navigation through tables during the processing of Transact-SQL statements. To build the distribution statistics automatically, SQL Server periodically samples a percentage of the data in the corresponding table for each index. This percentage is based on the number of rows in the table and the frequency of data modification. Use this option to perform an additional sampling using the specified percentage of data in the tables.

Percentage of database to sample

Specify the percentage of data in the tables to sample in order to generate distribution statistics. As the percentage increases, the accuracy of the statistics increases. However, the sampling takes an increasingly long time. If the specified value does not generate a sufficient sample, SQL Server determines an adequate sample size automatically. Valid values are 1 to 100.

Remove unused space from database files

Remove any unused space from the database, thereby reducing the size of the data files.

Shrink database when it grows beyond
Remove unused space from the database only if the database exceeds the specified size, in megabytes (MB).
Amount of free space to remain after shrink
Determine the amount of unused space that will remain in the database after the database has shrunk. The greater the percentage, the smaller the amount by which the database can shrink. The value is based on the percentage of the actual data in the database. For example, if you were to shrink a 100 MB database containing 60 MB of data and 40 MB of free space, with a free space percentage of 50%,you would have 60 MB of data and 30 MB of free space left. Only excess space in the database is eliminated. Valid values are 0 to 100.

Schedule

Set the frequency at which the data optimization tasks (scheduled using SQL Server Agent) are executed. The default is every Sunday at 12:00 midnight.

Change

Display the Edit Recurring Job Schedule dialog box, where you can change the default schedule.

See Also

Database Maintenance Plan Wizard

Optimizing Database Performance Overview