SQL SERVER: 4 Possible Methods to Resolve Index Fragmentation

For OLTP databases, index fragmentation is a major problem for query performance. We have four methods to resolve this fragmentation problem. Let’s explore these methods one by one.

1. Dropping and Re-creating Fragmented Indexes

One of the easiest ways to avoid this fragmentation problem is dropping and recreating the targeted index.

Benefits:

·         Major benefit of this method is that we can completely remove both internal and external fragmentation

          Drawbacks:

·     We can’t drop a unique index (clustered or non-clustered) used for primary key directly. First we have to delete all the foreign keys that reference this primary key. So it is most time consuming method.

·     If we drop clustered index we have to rebuild all non-clustered indexes on target table.

·    If we drop an index on production server, during drop and recreate time, query performance will become very low due to unavailability of proper index.

·    Other queries accessing same table can face BLOCKING problem.

2.          Using DROP_EXISTING Keyword

DROP_EXISTING keyword in CREATE INDEX statement drops and create existing index in single atomic step

CREATE INDEX YourIndexName ON TableName(ColumnName)

WITH (DROP_EXISTING = ON)

Benefits:

·         This method is helpful to avoid overhead of non-clustered index rebuilding when we drop a clustered index but still provide benefits of Drop & Create method.

          Drawbacks:

·     If column on which index is created is being used for primary or unique foreign key and somehow we omit UNIQUE keyword in CREATE statement, It will generate error.

·    Blocking of queries same to first method

3.         ALTER INDEX REBUILD

ALTER INDEX REBUILD rebuilds an index assigning fresh pages to reduce both internal and external fragmentation to a minimum and is considered most appropriate way to avoid fragmentation.

ALTER INDEX YourIndexName ON TableName REBUILD

Benefits:

·         Most useful method to remove internal and external fragmentation with out any ambiguity defined in first two methods.

·         Using ONLINE keyword in ALTER INDEX REBUILD query, we somehow can avoid query blocking problem but obviously process of rebuilding of an index will be slow.

·         With following single query you can re-build all the indexes of a table.

ALTER INDEX ALL ON TableName

          Drawbacks:

·     Other then blocking ALTER INDEX REBUILD has only one problem. You have to re-execute the ALTER query if somehow process of rebuild was interrupted.

4.    ALTER INDEX REORGANIZE

     ALTER INDEX REORGANIZE reduces the fragmentation without any rebuilding process. ALTER INDEX REORGANIZE reduces fragmentation through following steps

a.     Rearranges the existing leaf pages of index in logical order to reduce external fermentation

b.     And to reduce internal fermentation it compacts the rows with in the pages and removes resultant empty pages

c.      ALTER INDEX REORGANIZE works in steps and performs locking for a small period. If a page is found already locked by another query, it just leave the page as it and moves forward.

   ALTER INDEX YourIndexName ON TableName REORGANIZE

Benefits:

·         It can work without disturbing other queries running on server.

·         It preserves the work intermediately so if ALTER INDEX REORGANIZE query is interrupted work is never roll backed.

          Drawbacks:

·     It can’t reduce fragmentation effectively as compared to ALTER INDEX REBUILD

·    It can take more time then rebuild when index is highly fragmented

Try your best to avoid first method. Second one is good when you actually need to drop and recreate index, but still try to avoid using this for de-fregmentation process. Use ALTER INDEX REORGANIZE when fragmentation is up to 40 % (avg_fregmentation_in_percent value of dm_db_index_physical_stats) and if percentage of fragmentation increases from 40% you must use ALTER INDEX REBUILD