Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Update Data Optimization Information

SQL Server 2000

Use the Update Data Optimization Information screen to view or specify the following options.

Options

Reorganize data and index pages

Cause the indexes on the tables in the database to be dropped and re-created with a new FILLFACTOR. The FILLFACTOR determines how much empty space to leave on each page in the index, thereby reserving 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 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

Cause the indexes on the tables in the database to be dropped and re-created with the original FILLFACTOR that was specified when the indexes were created.

Change free space per page percentage to

Cause the indexes on the tables in the database to be dropped and re-created with a new automatically calculated 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 from 0 through 100.

Update statistics used by query optimizer.

Cause the distribution statistics of each index created on user tables in the database to be resampled. 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.

Sample % of the database

Generate distribution statistics by sampling the percentage of data in the tables. The higher the percentage, the more accurate the statistics, but the longer the sampling takes. If the specified value does not generate a sufficient sample, SQL Server determines an adequate sample size automatically. Valid values range from 1 through 100.

Remove unused space from database files

Remove any unused space from the database, thereby allowing the size of the data files to be reduced.

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 to remain in the database after the database is shrunk (the larger the percentage, the less the database can shrink). The value is based on the percentage of the actual data in the database. For example, a 100 MB database containing 60 MB of data and 40 MB of free space, with a free space percentage of 50 percent, would result in 60 MB of data and 30 MB of free space (because 50 percent of 60 MB is 30 MB). Only excess space in the database is eliminated. Valid values are from 0 through 100.

Schedule

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

Change

Change the default schedule.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.