Lesson 3: Performing Maintenance Tasks

3 4

In addition to configuration and setup tasks, a database administrator must perform a variety of maintenance tasks. In this lesson, you will learn to update distribution statistics, rebuild indexes, and manage full-text indexes. These tasks along with performing backups and performing internal consistency checks are regular maintenance tasks of a database administrator. Backup tasks were covered in Chapter 9 and performing internal consistency checks is covered in Chapter 14.

---

After this lesson, you will be able to

• Update distribution statistics
• Rebuild indexes
• Maintain full-text indexes

Estimated lesson time: 15 minutes

---

Updating Distribution Statistics

SQL Server 2000 automatically creates and maintains distribution statistics for all indexes. Distribution statistics describe the selectivity and distribution of key values in each index. The SQL Server 2000 query optimizer uses these statistics to estimate the efficiency of an index in retrieving data in a query. The query optimizer is the component of SQL Server responsible for determining the most efficient method to retrieve data (such as whether to use an index and which index to use). Distribution statistics can also be created and maintained for unindexed columns. They can be created manually using the CREATE STATISTICS statement or can be created automatically by the query optimizer.

Although distribution statistics are periodically updated automatically by the SQL Server service, you should refresh them manually whenever significant numbers of changes to keys occur in an index or a significant amount of new data is added to a table. You refresh them manually using the UPDATE STATISTICS statement or by displaying the Execution Plan for a query in SQL Query Analyzer (you select Show Execution Plan from the Query menu). Out-of-date and missing statistics are displayed as warnings in the Execution Plan tab in SQL Query Analyzer, which you can respond to by creating or updating distribution statistics. You can also schedule the updating of distribution statistics using the Database Maintenance Plan Wizard, which is covered in Chapter 13.

To verify that the autocreate and autoupdate statistics options are enabled (the default) for a database, right-click the database in SQL Server Enterprise Manager, click Properties, and then click the Options tab. Verify that both of the check boxes are selected.

Maintaining Indexes

Clustered indexes control the order and placement of data stored in the data pages of a table. In a clustered index, the physical order of the rows in a table is the same as the indexed order of the index key values. As new data is entered, SQL Server 2000 might have to reorganize the storage of data to make room for new values in order to maintain the ordering. Nonclustered indexes also have to be reordered when new data requires page splits. When SQL Server 2000 needs to enter data on a page that is full, it allocates a new page and splits the existing data between the new page and the existing page. Page splitting can impair performance during the page split and cause data fragmentation that affects future performance.

To minimize the need for page splits, a fill factor for the index is frequently specified. It can be specified using a server-wide value or on an index-by-index basis. Creating an index using a fill factor leaves pages partially full at the time of index creation. However, a fill factor value has no effect when new data is being entered into tables. When data is added to tables, these partially filled pages become full and page splits begin occurring.

As a database administrator, when page splitting occurs you might need to re-create indexes to reorganize data and re-establish partially filled pages by re-specifying the fill factor. This should only be done when the database is not busy. Creating or modifying a clustered index is time-consuming because during these operations the table's rows are reorganized on disk.

You can rebuild indexes using the Transact-SQL DROP_EXISTING clause of the CREATE INDEX statement or the DBCC DBREINDEX statement. You can also schedule the rebuilding of indexes using the Database Maintenance Plan Wizard in SQL Server Enterprise Manager.

Maintaining Full-Text Indexes

Full-text indexes are indexes of all character data in one or more tables in a database. The indexes are stored in the file system, but administered through the database. Administration consists of several tasks. The full-text engine is implemented as the Microsoft Search service. At the server level, you can configure the amount of system resources that can be used by the Microsoft Search service to maintain current indexes. At the database level, you enable a database and one or more tables to use the Microsoft Search service. Afterward, you must create (populate) full-text indexes on each full-text enabled table. These full-text indexes are stored in catalogs on the local server.

You can use the Full-Text Indexing Wizard in SQL Server Enterprise Manager to enable full-text indexing for a database, one or more tables, and specified columns within the tables. You can also create a population schedule in the Full-Text Indexing Wizard. To initially populate a full-text index, expand the container for the database containing the full-text catalog and then click the Full-Text Catalogs container. You can right-click Full-Text Catalogs to populate all catalogs, or right-click an individual catalog in the details pane to populate a single catalog.

Note

---

The design and creation of full-text indexes is generally not the responsibility of the database administrator. However, you might be responsible for rebuilding these indexes and regulating the use of resources by the Microsoft Search service for maintaining these indexes.

After the full-text indexes are populated, you need to determine how to keep them current. To prevent the maintenance of full-text indexes from consuming resources when the database is busy, you can repopulate these indexes manually or on a schedule. You can also configure these indexes to be updated automatically as a background process that runs during periods of low database activity. the three repopulation methods for full-text indexes are shown in Table 12.7.

To perform an update of a full-text index manually, right-click the desired Full-Text Catalog name found in the Full-Text Catalogs container and select either Rebuild Catalog or Start Incremental Population. To enable change tracking, use the sp_fulltext_table system stored procedure. To control the amount of resources that may be used by the Microsoft Search service, use the sp_fulltext_service system stored procedure.

Lesson Summary

Additional maintenance tasks a database administrator might have to perform include updating distribution statistics, rebuilding indexes, and updating full-text indexes. You can update distribution statistics and rebuild indexes manually, or you can schedule them. You can update full-text indexes manually or on a schedule, or you can configure them to run as low-priority background tasks.

Table 12.7 Repopulation Methods for Full-Text Indexes

Rebuild Type	Description	When to Use
Full rebuild	A complete rescan of all rows followed by a complete rebuild of the full-text index. Must be performed manually or on a schedule.	Large amounts of data have changed.
Timestamp-based incremental rebuild	A rescan of the rows that have changed since the last full or incremental rebuild. The table that is indexed must contain a timestamp column and this rebuild type only updates changes that also update the timestamp column. Must be performed manually or on a schedule.	Use when a large number, but not a large percentage, of records have changed.
Change tracking	A list of all changes to indexed data is maintained. Generally performed on a schedule or as a background task when processor and memory resources are available.	With scheduled or background process updating.