SQL Server Re-indexing Tips

Defragment or rebuild? This guide will help you decide and assist you in developing a database maintenance plan that balances performance, uptime and ease of management.
Posted February 2, 2009

Denny Cherry

(Page 1 of 2)

One of the biggest misconceptions about SQL Server is that it is a set it and forget it database platform. Like the other database platforms in the enterprise space, Microsoft SQL Server needs a little bit of TLC every once and a while to keep it running at its peak performance.

One of the most important things that you can do in order to keep Microsoft SQL Server running at its fullest is rebuilding or defragmenting your indexes. Notice that the word "or" is in there, not the word "and". This is because rebuilding and defragmenting (or defragging) your indexes produces the same end result. The difference is the path that you take, namely the operations that SQL Server performs to get to the end result.

Neither the rebuilding or defragmenting operations are specifically faster than the other. The basic rule of thumb to work by is that if it is faster to defragment an index than rebuild it then defragment it; while if it is faster to rebuild an index than to defragment it you should rebuild it. Generally speaking, if an index is less than 30 percent fragmented, then you will have better luck defragmenting the index. If the index is over 30 percent fragmented then you will have better luck rebuilding it.

What these operations are actually doing

When you build an index you set a parameter on the index that is key to how quickly your index will become fragmented. This setting is the fill factor percentage. This setting tells the SQL Server that when a new page is allocated to the index, what percentage of space within that page should be allocated for new data pages. This fill factor setting is also used when the index is defragmented or rebuilt.

When you rebuild your indexes, essentially what happens is that the index is deleted from the database, and a new index is created with the same name using all the same settings.

When you defrag your indexes, SQL Server starts at the beginning of the index and starts putting all the rows into the correct order, moving rows from one data page to another and freeing up space so that the data pages have the correct amount of free space in them as defined by the fill factor.

When should I do one over the other?

As noted above, an important factor is the amount of time it will take to complete each operation (usually using the amount of fragmentation as a guide). However there are some other factors to consider. If you are using any edition of SQL Server 2000 or older, or any edition of SQL Server 2005 and higher that is not Enterprise Edition, then index rebuilds are an offline operation.

What this means is that no users will be able to access the data within the table while the index rebuild is happening. In SQL Server 2005, users of the Enterprise Edition were given the ability to do online index rebuilds. This ability allows you to rebuild indexes without taking the index offline and without locking the table while the index is being rebuilt.

Defragmenting indexes on the other hand is an online operation. This means that while the index defragmentation process is happening, your users will be able to access the data.

Because of this key difference, you may opt to perform index defragmenting even though your indexes are heavily fragmented especially if you cannot afford the downtime to rebuild your indexes.

If you are using SQL Server 2005/2008 Enterprise Edition and opt for the online index rebuilding, do keep in mind that rebuilding indexes online is a longer operation than rebuilding them offline. If you can afford the downtime, an offline index rebuild is recommended since it is faster.

However tables using the TEXT, NTEXT and IMAGE data types cannot have their indexes rebuilt online. If you specify the ONLINE=ON flag when rebuilding the indexes the index rebuild command will fail with an error message and the index will not be rebuild.

If you need to change one of the options that you specified when creating the index, you will need to rebuild the index. When you defragment the index you cannot change the fill factor, or any of the other SET options.

Page 1 of 2

1 2
Next Page

Tags: data, Microsoft, server, IT, Enterprise

0 Comments (click to add your comment)
Comment and Contribute


(Maximum characters: 1200). You have characters left.