Best way to deal with Index Fragmentation

Fragmentation can hurt the performance of your query very badly. It is one of the key factor behind poor performing database. Many a times, DBA takes it very easy and respond when there is complete slowdown of the application. DBA respond to the situation in reactive manner instead of the proactive manner.

It is essential to removes fragmentation and reclaims disk space. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. Heavily fragmented indexes could cause increase in IO. An increased IO will cause increase in physical and logical reads as data is scattered across the multiple pages, which could actually accommodate in comparatively less number of pages if there is no fragmentation.

The solution to this could be :

  1. Reorganize Index : Index should be reorganized when index fragmentation is between 5% to 30%
  2. Rebuild Index : Index should be rebuild when index fragmentation is greater  than 30%

Index rebuild is one of the costly operation in SQL Server. It requires more CPU and it locks the database resources. Although, SQL Server has provided ONLINE option while rebuilding the indexes. Choosing ONLINE option will make the index available during the rebuild. But, we can’t ignore the cost associated with index rebuild.

Do we really need to wait till the fragmentation reaches 30%? If you ask me then NO. We should not wait and allow the fragmentation to reach the 30%. We should take the proactive measure and reorganize the indexes if the fragmentation reaches the 5%. We can implement some SQL Job which should run daily and check all the indexes with fragmentation 5% or more and reorganize them.

Query to check the index fragmentation :

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc





Evaluate the suggestions given in this blog post wisely before applying it in Production environment.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s