You are currently offline, waiting for your internet to reconnect

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

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

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.

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:
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
Properties

Article ID: 2755960 - Last Review: 09/27/2012 17:27:00 - Revision: 1.0

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

  • kbsurveynew kbhowto kbexpertiseadvanced KB2755960
Feedback
lay:none;" onerror="var m=document.createElement('meta');m.name='ms.dqp0';m.content='true';document.getElementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?">