Explanation to Defragment All Indexes in a Database

In this detail how to generate and run a SQL Server script that could automatically identify and repair index fragmentation of all indexes within a database. Index fragmentation which is caused by the frequent updates of data can extensively result in the poor performance of a database. This granted by the script given isolates the degree of fragmentation, and re-ordering (in the case of low fragmentation) or recreates (in the case of high fragmentation) the indexes in order to attain the optimal level of performance.

Over time, indexes in SQL Server become fragmented due to frequent INSERT, UPDATE, or DELETE operations. Fragmentation reduces performance by increasing disk I/O. To improve performance, indexes should be reorganized (if mildly fragmented) or rebuilt (if heavily fragmented).

You can use a script to automatically detect and fix fragmentation for all indexes in a database.

Example Script:

1. DECLARE @TableName NVARCHAR(128);  
2. DECLARE @SQL NVARCHAR(MAX);  
3.   
4. -- Cursor to go through each table  
5. DECLARE TableCursor CURSOR FOR  
6. SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name)  
7. FROM sys.objects o  
8. WHERE o.type = 'U';  
9.   
10. OPEN TableCursor;  
11. FETCH NEXT FROM TableCursor INTO @TableName;  
12.   
13. WHILE @@FETCH_STATUS = 0  
14. BEGIN  
15.     SET @SQL = '  
16.     DECLARE @IndexName NVARCHAR(128);  
17.     DECLARE @IndexId INT;  
18.     DECLARE @Frag FLOAT;  
19.   
20.     SELECT @IndexId = index_id,   
21.            @Frag = avg_fragmentation_in_percent  
22.     FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(''' + @TableName + '''), NULL, NULL, ''LIMITED'')   
23.     WHERE index_id > 0;  
24.   
25.     IF @Frag > 30  
26.         ALTER INDEX ALL ON ' + @TableName + ' REBUILD;  
27.     ELSE IF @Frag > 5  
28.         ALTER INDEX ALL ON ' + @TableName + ' REORGANIZE;  
29.     ';  
30.       
31.     EXEC sp_executesql @SQL;  
32.     FETCH NEXT FROM TableCursor INTO @TableName;  
33. END  
34.   
35. CLOSE TableCursor;  
36. DEALLOCATE TableCursor;  

• • > 30% fragmentation → REBUILDING an index, drops the current index and recreates it. It is more efficient when indexes are very fragmented and it is more resources-demanding, therefore it is also slower.

  • 5%–30% fragmentation → REORGANIZE the Index Reorganizing of an index implies that the particular leaf-nodes are reordered to reflect the logical order of the index. This is a lightweight operation, which can be conducted online and which is applicable on indexes that are moderately fragmented.

  • < 5% fragmentation → less than 5 % 0 action required Low fragmented indexes are those that cannot be maintained.

    Conclusion:

  This script is a routine that index-repair, on an automatic basis, all the indexes within a database according to their level of fragmentation. The frequent utilization of this script will facilitate queries to be faster and occupy less space on the hard drive. We should like to arrange the script such that it executes during off-peak time or on a weekly maintenance schedule such that it does not load the database to a crawl.