In the previous article (see Index Fragmentation), I described how to detect index fragmentation using a script that utilizes system tables. According to Microsoft’s recommendations, we should reorganize (REORGANIZE) indexes with fragmentation between 5 – 30% and rebuild (REBUILD) indexes with fragmentation above 30%. We will use the script from the previous article and create an automatic script. The SQL index fragmentation fix performed in this way is simple and fast.

Automatic Index Repair Using a Script in SQL Server

The script works as follows:

• We create a preparation table @TablesToMaintenance.
• In the table, we prepare indexes to be repaired, i.e., all indexes with fragmentation greater than 5%. We also add the operation to be performed (REBUILD vs. REORGANIZE).
• In the end, using a cursor, we perform the required operation sequentially on each index (executing dynamic SQL queries).

DECLARE @TablesToMaintenance AS TABLE
(
  DB_Schema VARCHAR(255),
  DB_Table VARCHAR(255),
  DB_Index VARCHAR(255),
  INDEX_Fragmentation FLOAT,
  OperationToMaintenance VARCHAR(255)
)
-----------Příprava fragmentovaných tabulek----
INSERT INTO @TablesToMaintenance(
  DB_Schema,
  DB_Table,
  DB_Index,
  INDEX_Fragmentation,
  OperationToMaintenance
)
SELECT
   sch.name AS DB_Schema,
   obj.name AS DB_Table,
   ind.name AS DB_Index,
   stat.avg_fragmentation_in_percent AS INDEX_Fragmentation,
   CASE
     WHEN avg_fragmentation_in_percent between 5 and 30 THEN 'REORGANIZE'
     WHEN avg_fragmentation_in_percent >30 THEN 'REBUILD'
   END AS OperationToMaintenance
FROM
   sys.objects obj
   LEFT JOIN sys.schemas sch
     ON obj.schema_id= sch.schema_id
   LEFT JOIN sys.indexes ind
     ON obj.object_id=ind.object_id
   LEFT JOIN sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, 'LIMITED') AS stat
     ON ind.object_id=stat.object_id
     AND ind.index_id=stat.index_id
WHERE
   obj.type='U'
   AND ind.index_id > 0
   AND avg_fragmentation_in_percent>5

-----------Kurzor---------------------
DECLARE @SQL VARCHAR(255)
DECLARE @DB_Index VARCHAR(255)
DECLARE @DB_Schema VARCHAR(255)
DECLARE @DB_Table VARCHAR(255)
DECLARE @OperationToMaintenance VARCHAR(255)

DECLARE index_cursor CURSOR FOR (SELECT DB_Index,DB_Schema,DB_Table,OperationToMaintenance FROM @TablesToMaintenance)

OPEN index_cursor
FETCH NEXT FROM index_cursor INTO @DB_Index, @DB_Schema, @DB_Table, @OperationToMaintenance
WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @SQL= 'Alter INDEX [' + @DB_Index + '] ON [' + @DB_Schema + '].[' + @DB_Table + '] ' + @OperationToMaintenance
    EXECUTE (@SQL)
    FETCH NEXT FROM index_cursor INTO @DB_Index, @DB_Schema, @DB_Table, @OperationToMaintenance
  END
CLOSE index_cursor
DEALLOCATE index_cursor

Enjoy!

Edit 11.12.2018 – Script inside the cursor has been updated – dynamic SQL assembly. It is advisable to use square brackets because some automatically created SQL indexes may contain special characters or spaces, and executing SQL with such characters can fail. In the future, I pledge to use square brackets where they belong; it’s optional and a more professional approach.