Skip to main content
Search Again

We think these articles could help you:

    See More
    Nintex Knowledge Base

    Cleaning Up Fragmented Indices

    Status: Validated

    TOPIC
    How to find and clean fragmented indices (indexes) to mitigate SQL performance issues.
    INSTRUCTIONS
    The following t-sql command will retrieve the fragmentation on the indices. Replace 'DATABASENAME' with the name of the desired database.
    Use DATABASENAME
    GO
    SELECT OBJECT_NAME(i.object_id) AS TableName ,i.name AS TableIndexName ,phystat.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') phystat
    inner JOIN sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id
    WHERE phystat.avg_fragmentation_in_percent > 10
    Rebuild or reorganize any index with greater than 10% fragmentation. For more information, see Reorganize and Rebuild Indexes in Microsoft documentation. 
    ADDITIONAL INFORMATION
    Fragmentation of indexes can occur when there are a large number of read-write or delete operations occurring on a database. This can cause logical pages to be out of place with where they should be located within the data file. Fragmented databases can cause slow query times and SQL performance degradation. Index Fragmentation should be kept under 10%. If the index is 5%-30% then reorganization is recommended. If it is greater than 30% then it is necessary to rebuild.
    • Was this article helpful?