SQL Server Re-indexing Tips

Datamation content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

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 should I do one over the other?
How do I know my indexes need to be defragmented?
How can I keep my indexes from becoming fragmented?
RELATED NEWS AND ANALYSIS

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.

are a few ways to setup your indexes to be defragmented or your tables rebuild. The easiest way is to simply use the Maintenance Plans, which are included with all editions of SQL Server other than the Express Edition. These Maintenance Plans allow you to perform a variety of database maintenance operations including index defragmentation and index rebuilding.

If you are a more advanced with your database, or simply prefer more control over what is happening, then you can manually write T/SQL code that will rebuild or defragment your indexes. If you are using SQL Server 2008 or below, you can use the DBCC INDEXDEFRAG statement (this statement will be removed in SQL Server Kilimanjaro). Starting with SQL Server 2005 the recommended way (required starting in SQL Server Kilimanjaro) to defragment indexes is by using the ALTER INDEX statement with the REORGANIZE keyword.

ALTER INDEX ALL ON HumanResources.Employee
REORGANIZE;

To rebuild your indexes you can use the older DBCC DBREINDEX statement (this statement will be removed in SQL Server Kilimanjaro). Starting with SQL Server 2005 you should use the ALTER INDEX statement with the REBUILD keyword to rebuild your indexes.

ALTER INDEX ALL ON HumanResources.Employee
REBUILD WITH (FILLFACTOR = 75, ONLINE = ON);

How do I know my indexes need to be defragmented?

You look at the level of fragmentation that your indexes have. The higher the level of fragmentation the slower statements against the indexes will run. Up through SQL Server 2008 you can use the DBCC SHOWCONTIG statement (like the other DBCC statements in this article, this statement will be removed in SQL Server Kilimanjaro). Starting in SQL Server 2005 you can use the sys.dm_db_index_physical_stats dynamic management function to see what the fragmentation ration of your indexes are.

SELECT *
FROM sys.dm_db_index_physical_stats (db_id(‘YourDatabase’), object_id(‘YourTable’), NULL, NULL, NULL)

One of the columns returned from this statement is avg_fragmentation_in_percent. The lower the number the better.

How can I keep my indexes from becoming fragmented?

There is no way to keep indexes from becoming fragmented. All that can be done is to keep it under control.

If your indexes are becoming fragmented too quickly then you need to decrease the interval that you defragment your indexes (run the job more often). If your indexes are still becoming too fragmented then consider rebuilding them and allocating more white space by lowering the fill factor percentage. If you are at 90% fill factor, and your indexes are becoming heavily fragmented after a couple of days, you may need to consider lowering your fill factor to 60-70%. While this will take up more disk space it will reduce the amount of fragmentation that occurs.

This article was first published on EnterpriseITPlanet.com.