Database Maintenance Plan Properties (Optimizations Page)
When connecting to Microsoft SQL Server 2000 servers, use the Database Maintenance Plan Properties Optimizations page to maintain efficient data organization. New database maintenance plans cannot be created with SQL Server Management Studio. Use Enterprise Manager instead. Database maintenance plans cannot be created on Microsoft SQL Server 2005. Use maintenance plans instead.
Use this tab to reorganize your data and index pages, allowing the query optimizer better access to execution plans.
- 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 percentage of free space per page 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 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 shrinking
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 percent, 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.