- A specific index
- All indexes on a table
- All indexes on an indexed view
- All indexes in a database
- All indexes in all databases
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
- 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 value||Corrective statement|
|> 5% and < = 30%||ALTER INDEX REORGANIZE|
|> 30%||ALTER INDEX REBUILD WITH (ONLINE = ON)*|
- 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.
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 Software||Rule title||Rule description||Product versions against which the rule is evaluated|
|System Center Advisor||SQL Server high fragmentations may downgrade query performance||System 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