SQL Server 2000 index fragmentation

The main SQL server command for verifying index fragmentation is DBCC showcontig. The following sample code is used to determine the index fragmentation of the authors table in the pubs database:

Use pubs Go DBCC showcontig ('authors ') Go

To determine the index fragmentation of the Cluster Index of the authors table in the pubs database, run the following command:

Use pubs Go DBCC showcontig ('authors ', 1) Go

As a reference point, the value 1 shows the clustered index. A specific non-clustered index is displayed between 2 and 255 values.

An empirical rule for determining whether to re-create an index is to check whether the scan density is lower than 90%.

Understanding Our data based on Tip 1: If you notice that a specific table always has a low scanning density, you can consider reducing the fill factor and pad_index by 5% to 10% to reduce fragmentation.

As the data grows, the database increases and the transaction running time increases, and the maintenance window will count every second. Use our backup server or back up a recent production database to a development/test server, and then execute the DBCC command. In this way, we can view the database fragmentation in a timely manner, so that the maintenance window can be focused only on the re-built index. Once we have this data, we can execute one of the commands listed in Tip 4 to recreate the broken index. Then, run DBCC showcontig again to verify whether the index fragmentation has been corrected. This will validate our scripts and roughly estimate the time required by the production system.

　Tip 5: rebuild a broken index

Index maintenance is the key to ensuring the optimal configuration of indexes. The "index rebuild options" table summarizes the typical methods used to maintain indexes.

Index rebuild options
ID	Description	Recommendations	Sample Code
1	Drop Index Create Index	Run: if the system does not have a user, when the index change of the table requires that the old index be deleted and replaced with the new index configuration, when the clustered index of the table changes, because all non-clustered indexes depend on Clustered indexes, they need to be rebuilt.	Use pubs Go Drop index authors. au_id_ind Go   Create clustered index au_id_ind on authors (au_id) Go
2	DBCC dbreindex	Execution: when the system does not have users, when there is reference integrity between tables and maintenance is required, when the index configuration modifies the fill factor required by the clustered index, it supports an atomic transaction, to ensure that no index is lost.	Use pubs Go DBCC dbreindex (authors, '', 70) Go
3	DBCC indexdefrag	Run the command when the system has no user. Used to clear fragments of an index.	Use pubs Go DBCC indexdefrag (pubs, authors, au_id_ind Go

　Tip 6: configure the database

Disk-level fragmentation is related to index fragmentation. It is a non-contiguous file system caused by file deletion and Logical Disk File System Restructuring. It reduces the performance of I/O-sensitive processes. The following suggestions can minimize disk fragmentation:

• Prepare a dedicated disk for the database
• Writing a database to a disk in a continuous manner
• Pre-allocate the corresponding database size for large data (for example, prepare 10 Gb space for a 100 GB database that increases by 1 GB per month)
• Do not automatically increase or decrease the database

　　Summary

Index fragmentation is critical to high-performance databases. Identifying useful indexes and maintaining them will ensure high performance throughout the application process. Good luck!