Fix SQL Server Index Fragmentation with One Simple Script (REBUILD or REORGANIZE)

SQL index fragmentation is a common issue in SQL Server that can lead to performance degradation if left unaddressed.

Rebuild or Reorganize Indexes Based on Fragmentation Level

This SQL script helps identify fragmented indexes in your SQL Server database and generates appropriate maintenance commands to either REBUILD or REORGANIZE them based on their fragmentation level.

• REBUILD is suggested when fragmentation is above 30%, which completely reconstructs the index to improve performance.
• REORGANIZE is suggested when fragmentation is between 5% and 30%, which is a lighter operation that defragments the leaf level of the index.
• No action is taken for indexes with less than 5% fragmentation.

The script returns:

• The table name
• The index name
• Index type
• Fragmentation percentage
• The SQL command (REBUILD / REORGANIZE) ready to be executed

This is particularly useful for regular index maintenance tasks in Microsoft Dynamics 365 Finance & Operations environments where performance tuning is critical.

<!-- Prism.js library -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/prism/1.29.0/themes/prism-tomorrow.min.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.29.0/prism.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.29.0/components/prism-csharp.min.js"></script>
 
<!-- X++ Code Block -->
<pre><code class="language-sql">
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,

ind.name AS IndexName, indexstats.index_type_desc AS IndexType,

indexstats.avg_fragmentation_in_percent,

'ALTER INDEX ' + QUOTENAME(ind.name)  + ' ON ' +  QUOTENAME(OBJECT_SCHEMA_NAME( ind.OBJECT_ID)) + '.' +QUOTENAME(object_name(ind.object_id)) +

CASE    WHEN indexstats.avg_fragmentation_in_percent>30 THEN ' REBUILD '

        WHEN indexstats.avg_fragmentation_in_percent>=5 THEN ' REORGANIZE '

        ELSE NULL END as [SQLQuery]  -- if <5 not required, so no query needed

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats

INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id

    AND ind.index_id = indexstats.index_id

WHERE

--indexstats.avg_fragmentation_in_percent , e.g. >10, you can specify any number in percent

ind.Name is not null

ORDER BY indexstats.avg_fragmentation_in_percent DESC
</code></pre>

SQL Query Explanation

This SQL script analyzes index fragmentation across all tables in the current SQL Server database using the dynamic management view sys.dm_db_index_physical_stats.

• OBJECT_NAME(ind.OBJECT_ID): Returns the name of the table for each index.
• ind.name: The name of the index.
• indexstats.index_type_desc: The type of index (e.g., CLUSTERED, NONCLUSTERED).
• indexstats.avg_fragmentation_in_percent: Shows how fragmented each index is, as a percentage.
• SQLQuery column: Dynamically generates the appropriate maintenance command:
  • REBUILD for indexes with fragmentation > 30%.
  • REORGANIZE for fragmentation between 5% and 30%.
  • No action is recommended for indexes below 5%.

It joins:

• sys.indexes: to get index metadata.
• sys.dm_db_index_physical_stats: to get fragmentation info.

The result is sorted by fragmentation level in descending order, highlighting the most fragmented indexes at the top.

This query is ideal for maintaining index performance in Microsoft Dynamics 365 Finance & Operations environments, especially in systems with large datasets or frequent transactions.

Conclusion

Efficient index maintenance is a critical component of database performance optimization, particularly in large-scale ERP systems like Microsoft Dynamics 365 Finance and Operations (D365 F&O). The SQL script above provides a comprehensive overview of index fragmentation within your database, allowing administrators and technical consultants to make informed decisions on whether to reorganize or rebuild indexes based on fragmentation levels.

By dynamically generating the appropriate SQL commands (REBUILD or REORGANIZE) depending on the degree of fragmentation, this query eliminates the guesswork and simplifies the index maintenance process. Highly fragmented indexes (above 30%) are rebuilt for maximum efficiency, while moderately fragmented ones (between 5% and 30%) are reorganized to enhance performance without the overhead of a full rebuild. Indexes with minimal fragmentation are skipped, which helps avoid unnecessary resource usage.

This approach aligns with best practices in D365 F&O environments, where performance bottlenecks in forms, reports, or batch jobs are often traced back to poorly maintained indexes. Incorporating this SQL script into a regular maintenance schedule can help ensure faster data retrieval, reduced blocking, and improved overall responsiveness of your ERP system.

Moreover, automated monitoring of index fragmentation can serve as a proactive measure against system slowdowns, especially in high-transaction environments such as finance, supply chain, or warehouse management modules. When used regularly, it not only extends the life of your database resources but also supports scalability as your business grows.

At Codefine, we use this and similar techniques as part of our Performance Optimization services, helping organizations fine-tune their Dynamics 365 F&O infrastructure. Whether you’re troubleshooting slow-running queries or planning for long-term system stability, intelligent index management is a foundational step.

Ultimately, keeping indexes healthy is not just a database task—it’s a business enabler. A well-optimized ERP system directly contributes to faster decision-making, smoother operations, and better user experiences across departments.

Learn more about our services here.