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

Article translations Article translations
Article ID: 2755960 - View products that this article applies to.
Expand all | Collapse all

Summary

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.

Collapse this tableExpand this table
avg_fragmentation_in_percent valueCorrective statement
> 5% and < = 30% ALTER INDEX REORGANIZE
> 30%ALTER INDEX REBUILD WITH (ONLINE = ON)*
Notes:
  • 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:
How to reorganize and rebuild indexes

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.
Collapse this tableExpand this table
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

Properties

Article ID: 2755960 - Last Review: September 27, 2012 - Revision: 1.0
Applies to
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2012 Web
Keywords: 
kbsurveynew kbhowto kbexpertiseadvanced KB2755960

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com