SQL query performance might decrease when the SQL Server Database instance has high index fragmentation


SQL query performance might decrease if the indexes are heavily fragmented in the Microsoft SQL Server Database instance. You can use the sys.dm_db_index_physical_stats system function to detect the index fragmentation for the following cases:
  • A specific index
  • All indexes on a table
  • All indexes on an indexed view
  • All indexes in a database
  • All indexes in all databases
To determine the fragmentation that is greater than 30 percent, run the following SQL query script:

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName,i.name AS IndexName, indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID WHERE indexstats.avg_fragmentation_in_percent > 30 AND i.index_id = indexstats.index_id
To determine the best method to correct the fragmentation, refer to the following guidelines:
  • Use the ALTER INDEX REBUILD WITH (ONLINE = ON)* command for the objects whose average percent fragmentation is more than 30 percent.
  • Use the ALTER INDEX REORGANIZE command for the objects whose average percent fragmentation is between 5 to 30 percent.

avg_fragmentation_in_percent valueCorrective statement
  • These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. However, the actual values may vary from case to case. You must experiment to determine the best threshold for your environment.
  • Very low levels of fragmentation (less than five percent) should not be addressed by either of these commands. This is because the benefit from removing such a small amount of fragmentation is usually greatly outweighed by the cost of reorganizing or rebuilding the indexes.

More Information

For more information about how to reorganize and rebuild the indexes, go to the following Microsoft website:
The following table provides more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product against which the rule is evaluated.
Rule SoftwareRule titleRule descriptionProduct versions against which the rule is evaluated
System Center AdvisorSQL Server high fragmentations may downgrade query performanceSystem Center Advisor checks the logical fragmentation by using the avg_fragmentation_in_percent value in sys.dm_db_index_physical_stats for each SQL Server Database instance index. If the average fragmentation for the index or heap is greater than five percent, this alert is generated. Follow the instructions from this article to resolve the problems that are related to the high index fragmentations.SQL Server 2008

SQL Server 2008 R2

SQL Server 2012

Article ID: 2755960 - Last Review: Sep 27, 2012 - Revision: 1