Explanation of How to Defragment All Indexes in a Database in SQL Server

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;  

Explanation of Logic:

• > 30% fragmentation → REBUILD the index (more effective but uses more resources).

• 5%–30% fragmentation → REORGANIZE the index (lightweight).

• < 5% fragmentation → No action needed.

Conclusion:

This script helps automatically defragment all indexes in a database based on fragmentation level. Regular maintenance like this ensures faster query performance and more efficient disk usage. It's a best practice to schedule this during low-usage hours or as part of weekly maintenance.