Automating Defragmentation

My database schema contains many tables, so manually checking and defragmenting tables is a slow and laborious process. How can I automate defragmentation in SQL Server?

Table fragmentation occurs when you make data modifications (using INSERT, UPDATE, and DELETE statements) against the table. Because these modifications aren't usually distributed equally among the table's rows, the fullness of each page can vary over time. For queries that scan part or all of a table, such table fragmentation can cause additional page reads, which hinder parallel data scanning. SQL Server doesn't automatically defragment a database. And although a heap (a table without a clustered index) isn't logically fragmented by its nature, its pages can become physically fragmented during data modification. You can eliminate such fragmentation by temporarily adding a clustered index or by unloading and reloading the data.

To defragment indexes, you can use the DBCC DBREINDEX command in SQL Server 2000, 7.0, and 6.5. In SQL Server 2000, you can also use the DBCC INDEXDEFRAG command. DBCC INDEXDEFRAG allows more data availability than DBCC DBREINDEX but is slightly less powerful. DBCC INDEXDEFRAG works at the database-file level rather than at the object level and doesn't move data between files, whereas DBCC DBREINDEX completely reallocates the object. Listing 1 shows sample code that uses DBCC INDEXDEFRAG to defragment a database when fragmentation is more than 10 percent. Note that the code sample checks only for logical-scan fragmentation. You can modify the sample to add a similar check for extent-scan fragmentation.