articles >> performance tuning >> Analyze and Fix Index Fragmentation in SQL ...

Analyze and Fix Index Fragmentation in SQL Server 2008

By : Ashish Kumar Mehta Nov 27, 2008

Page 3 / 3


Perform Reorgainize Index Operation on Clustered Index of FindAndFixFragmentation Table
First let us perform REORGANIZE Index operation on the clustered index, and then execute the query as shown in the snippet to find the fragmentation on FindAndFixFragmentation table.

/* Reorganize [CL_FindAndFixFragmentation_Index] index on  FindAndFixFragmentation */
ALTER INDEX [CL_FindAndFixFragmentation_Index] ON FindAndFixFragmentation
REORGANIZE;
GO



Once we have performed the REORGANIZE Index operation you can see that the value for AvgPageFragmentation has changed from 99.0049751243781 to 5.70469798657718, which means index fragmentation is much better that how it was earlier. And at the same time the value for PageCounts has also come down from 603 to 298, this is considerable improvement.

Perform Rebuild Index Operation on Clustered Index of FindAndFixFragmentation Table
Now let us perform REBUILD Index operation on the clustered index, when you are using the Rebuild index operation it basically drops and recreates the index. The important thing what we need to see is does this results in reducing the index fragmentation further down from 5.70469798657718. Once you have performed the Rebuild operation execute the query as shown in the snippet to check the fragmentation on FindAndFixFragmentation table.

/* Rebuild [CL_FindAndFixFragmentation_Index] index on  FindAndFixFragmentation */
ALTER INDEX [CL_FindAndFixFragmentation_Index] ON FindAndFixFragmentation
REBUILD WITH (FILLFACTOR = 90, ONLINE=ON)
GO



You can see that the value for AvgPageFragmentation is back to 0.341296928327645, which means the fragmentation is same as it was when we began this exercise. And at the same time the value for PageCounts is back to 293. This proves that using REBUILD Index operation is better than REORGANIZE Index operation.

Reorganize Index
Reorganize Index uses minimal system resources and it is performed online. The biggest advantage is it does not require locks for long time therefore it does not block updates or other user queries. If the index fragmentation ranges in between 5% to 30% then it is better to perform Reorganize Index.

Rebuild Index
Rebuild Index basically drops and recreates the index; this is by far the best approach. If the index fragmentation is greater than 30% then the best strategy will be to use Rebuild Index instead of Reorganize Index.

Conclusion
Database Administrators should always make sure that fragmentation of indexes is handled on time. If the indexes are fragmented then the query response will not only be very slow; the data storage will also require more disk space. In this article you have seen an example where the clustered index gets fragmented over time and the steps which you need to perform to resolve index fragmentation issues.