Skip to main content
Search Again

We think these articles could help you:

    See More

    ALERT: This site will be decommissioned at the end of December 2018. All existing and new Nintex knowledge content will be available within the new Knowledge Base area of the Nintex Community site. 

    Nintex Knowledge Base

    Cleaning Up Fragmented Indices

    Status: Validated

    How to find and clean fragmented indices (indexes) to mitigate SQL performance issues.
    The following t-sql command will retrieve the fragmentation on the indices. Replace 'DATABASENAME' with the name of the desired database.
    SELECT OBJECT_NAME(i.object_id) AS TableName , 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. 
    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?