Call now: (800) 766-1884  


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA

 SQL Server Scripts
 Scripts Menu




  SQL Server Tips by Robin Schumacher

Removing Fragmentation

What should be done when fragmentation does indeed exist in one or more of the objects? The standard prescription is to perform a reorganization; however, not all reorganization methods are created equal. There are four main approaches in SQL Server 7 and 2000 that can be taken to perform reorganization. Each approach has its positive and negative points:

  • Drop/Create Index: This is the most basic way to reorganize indexes; however, this option cannot be used if the index supports a constraint. Plus, it is not the best thing to do during normal business operations, as the index is naturally unavailable for use during construction. Worse yet, for clustered index creations, the entire table is unavailable for use (read or write) and for non-clustered index creations, only read operations are allowed on the parent table.

  • Create Index with drop_existing: This option gets the DBA around the problem of not being able to re-create an index that supports a constraint; however, all other headaches identified above with the drop/create index method still apply.

  • DBCC DBREINDEX: This method allows the DBA to rebuild one or more indexes without having to know their Data Definition Language (DDL) definition. This command can also be safely issued if the indexes support constraints on a table. Other advantages include the rebuilding of statistics during the operation and the fact that the DBCC command can make use of multi-processor environments and run quite fast in such settings. Drawbacks to this method include the fact that it is also an offline operation and can negate object use during the utility run. It is also an atomic transaction, so if it is stopped before completion, all defragmentation work is lost.

  • DBCC INDEXDEFRAG: Available for SQL Server 2000 and higher, this method has the distinct advantage of being an online operation, meaning the DBA can reorganize while the objects stay available for use. Unlike DBCC DBREINDEX, this method can be stopped without any of the previous defragmentation work being lost. Of course, there has to be some penalty to pay for all the positives associated with this method. The way DBCC INDEXDEFRAG manages to be an online operation is that it skips over pages that are in use during its run, meaning that it is possible to miss some fragmentation. Also, DBCC INDEXDEFRAG is not as fast as DBCC DBREINDEX because of its inability to use multiple processors for its work, and it doesn’t rebuild object statistics. This operation can also make extent scan fragmentation worse. It eliminates extents but does not improve the physical order of the remaining extents.

Tables are not mentioned at all in the above reorganization methods. It is rare to have to rebuild standard heap tables, tables without clustered indexes. Fragmentation matters little in heap tables because the data rows do not have to be kept in order. Further, the SQL Server does a good job of keeping the data pages full because it will reuse space left empty from DELETEs and such. The main space headache facing most heap tables is row forwarding. In the special case in which a heap table is exhibiting signs of fragmentation, a clustered index on the table, with a high fillfactor, can be created and the index dropped after completion. The other option is to copy all the data out of the table, issue a TRUNCATE or drop/recreate the table and copy all the data back in.

In SQL Server 2005, all the previously mentioned options are available, but there are also the two following additions:

  • ALTER INDEX REORGANIZE: This option reorders the leaf level pages of the index in logical order. The good news is that this operation is online, so concurrency issues will not be encountered. The down side is that it is not as complete as a drop/create statement. This option replaces the DBCC INDEXREFRAG command.

  • ALTER INDEX REBUILD: This option replaces DBCC DBREINDEX, but is online in nature unless the index is eXtensible Markup Language (XML). It is also possible to rebuild just one or more partitions of an index instead of the entire index.
    In SQL Server 2005, the index can also be rebuilt online via the create index…drop_existing statement by utilizing the new online=on clause. An example of this approach might be:

    create nonclustered index emp_ssn
    on dbo.emp(emp_ssn)
    with (drop_existing=on,online=on)

The above book excerpt is from:

High-Performance SQL Server DBA
Tuning & Optimization Secrets

ISBN: 0-9761573-6-5
Robin Schumacher  


Burleson Consulting Remote DB Administration







Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.

Burleson Consulting
SQL Server database support


Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter