SQL 2005+

Removing SQL Server Index Fragmentation

by Richard Carr, published at http://www.blackwasp.co.uk/SQLIndexFragmentation.aspx

Over time, the indexes applied to tables in a SQL Server database can become fragmented because of row insertions, updates and deletions. This fragmentation can severely impact the performance of the database so should be rectified periodically.

Reorganising Indexes

Reorganising an index moves the index entries between and within pages to ensure that the logical order of pages matches the physical order. If removing an entry from a page leaves it empty, the page is removed. This means that the index may become smaller but it will not grow during the process.

You can only reorganise an index whilst the database is on-line. As this means that other users may be able to access the underlying table or view, the process uses minimal resources and any locks that are created are held only for a short time. The reorganisation will not interfere with the execution of queries and updates, although they may be slower than normal. If performance is critical you should aim to reorganise indexes when demand for the system is lowest. For example, you might run the process overnight for a system that is primarily used during office hours. Bear in mind that reorganising large indexes can take many hours.

To reorganise a single index you can use the ALTER INDEX statement, providing the index name and the table or view name, with the REORGANIZE clause. The following command reorganises the IX_Names index.

ALTER INDEX IX_Names ON Employees REORGANIZE

In my test database the result of reorganising the IX_Names index was a reduction in fragmentation from 8.27% to 0.72%:

name              FragmentationPercent
----------------- --------------------
PK_Employees      0.49
IX_Names          0.72

If you wish to reorganise all of the indexes for a table you should replace the index name with the ALL keyword, as follows:

ALTER INDEX ALL ON Employees REORGANIZE

Rebuilding an Index

You should only rebuild indexes when they are highly fragmented. The procedure drops the index completely and recreates it from scratch. You can rebuild indexes whilst the database is on-line or off-line. When on-line, the process will impact queries that would normally access the unavailable index. If performance is important, you should rebuild indexes when access requirements for the database are small. Remember that rebuilding an index is an expensive procedure that may run for several hours.

As with reorganising, rebuilding an index may cause it to shrink. However, unlike with reorganisation, it is possible for an index to become larger after rebuilding. This happens when the existing index pages contain more information than permitted by the index's fill factor. For example, data in a page that is completely full but has a fill factor of 80% will be spread over two or more pages in the rebuilt index.

There are several ways to rebuild an index. We'll look at two in this section of the article.

Rebuilding an Index Using ALTER INDEX

You can rebuild an index using the ALTER INDEX command in a similar manner to the above reorganisation samples. Instead of using the REORGANIZE clause you use the REBUILD clause.

ALTER INDEX IX_Names ON Employees REBUILD

A key benefit of this approach is the ability to rebuild multiple indexes in one statement. As with reorganisation, you can use the ALL keyword, in place of an index name, to rebuild all indexes for a single table.

ALTER INDEX ALL ON Employees REBUILD

You can also choose to rebuild a single partition of a partitioned index by specifying a partition number, as follows:

ALTER INDEX IX_Names ON Employees REBUILD Partition = 1

Rebuilding an Index Using CREATE INDEX

A second way that you can rebuild an index is to use the CREATE INDEX statement with the DROP_EXISTING clause. The advantage of this approach is that you can change the index definition, perhaps changing the columns indexed, the sort order or the fill factor, amongst other options. You can also elect to move the index between file groups.

The following statement rebuilds the IX_Names index without changing its configuration.

CREATE NONCLUSTERED INDEX IX_Names ON Employees
(
    Name ASC
) WITH DROP_EXISTING