Skip to main content
Search Again

We think these articles could help you:

    See More
    Nintex Knowledge Base

    Cleaning Up Fragmented Indices

    Summary
    How to find and clean fragmented indices (indexes) to mitigate SQL performance issues.

    Solution
    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. 
    • Was this article helpful?