Microsoft SQL Server – optimize index maintenance to improve query performance and reduce resource consumption
SQL Server Index and Statistics Maintenance – ‚IndexOptimize‘ is the SQL Server Maintenance Solution’s stored procedure for rebuilding and reorganizing indexes and updating statistics
Define thresholds for a common guideline is to:
Reorganize indexes with 5% to 30% fragmentation
Rebuild indexes with greater than 30% fragmentation
If using SQL Server Enterprise Edition use the ONLINE=ON option for index rebuilds to allow the database to remain available during the operation minimizing downtime