SQL Server Index Maintenance: Managing Fragmentation Automatically

Index fragmentation is one of those silent performance killers that creeps up on SQL Server databases over time. As data is inserted, updated, and deleted, indexes become fragmented, causing slower query performance and increased I/O operations. That's why automated index maintenance is essential for keeping databases running smoothly.

This script automates the process of identifying and fixing fragmented indexes by using SQL Server's dynamic management views to find fragmentation, then automatically choosing between REBUILD (for severe fragmentation) and REORGANIZE (for moderate fragmentation). Here's how it works and why each piece matters.

Understanding Index Fragmentation

Index fragmentation occurs when the logical order of pages in an index doesn't match the physical order on disk. This happens because:

• Page splits: When inserts occur and pages are full, SQL Server splits pages, creating fragmentation
• Deletes: Removing rows leaves gaps in index pages
• Updates: Changing values that affect index keys can cause rows to move

Fragmented indexes require more I/O operations to read the same data, leading to slower query performance. The solution is regular index maintenance using REBUILD or REORGANIZE operations.

The Complete Script

Here's the complete index maintenance script:

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON

DECLARE @TableName varchar(255);
DECLARE @IndexName varchar(255);
DECLARE @Fragmentation FLOAT;
DECLARE @IndexScript varchar(255);
DECLARE @Schema varchar(255);

SELECT 
    dbtables.[name], 
    dbindexes.[name],
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count [pages]
FROM 
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
    INNER JOIN sys.tables dbtables 
        on dbtables.[object_id] = indexstats.[object_id]
    INNER JOIN sys.schemas dbschemas 
        on dbtables.[schema_id] = dbschemas.[schema_id]
    INNER JOIN sys.indexes AS dbindexes 
        ON dbindexes.[object_id] = indexstats.[object_id]
        AND indexstats.index_id = dbindexes.index_id
WHERE 
    indexstats.database_id = DB_ID()
    AND indexstats.avg_fragmentation_in_percent >= 5.0
    AND indexstats.page_count > 10
ORDER BY 
    indexstats.page_count ASC,
    indexstats.avg_fragmentation_in_percent ASC

DECLARE TableCursor CURSOR FOR  
    SELECT 
        dbtables.[name], 
        dbindexes.[name],
        indexstats.avg_fragmentation_in_percent,
        dbschemas.[name]
    FROM 
        sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
        INNER JOIN sys.tables dbtables 
            on dbtables.[object_id] = indexstats.[object_id]
        INNER JOIN sys.schemas dbschemas 
            on dbtables.[schema_id] = dbschemas.[schema_id]
        INNER JOIN sys.indexes AS dbindexes 
            ON dbindexes.[object_id] = indexstats.[object_id]
            AND indexstats.index_id = dbindexes.index_id
    WHERE 
        indexstats.database_id = DB_ID()
        AND indexstats.avg_fragmentation_in_percent >= 5.0
        AND indexstats.page_count > 10
    ORDER BY 
        indexstats.page_count ASC,
        indexstats.avg_fragmentation_in_percent ASC;

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO
    @TableName,
    @IndexName,
    @Fragmentation,
    @Schema
 
WHILE @@FETCH_STATUS = 0 
 
BEGIN 
    IF (@Fragmentation >= 30.0)
        SET @IndexScript = 'ALTER INDEX [' + @IndexName + '] ON ' + @Schema + '.' + @TableName + ' REBUILD';
    ELSE IF (@Fragmentation >= 5.0)
        SET @IndexScript = 'ALTER INDEX [' + @IndexName + '] ON ' + @Schema + '.' + @TableName + ' REORGANIZE';
    ELSE
        SET @IndexScript = NULL;

    IF (@IndexScript IS NOT NULL)
    BEGIN
        --SELECT @IndexScript
        RAISERROR (@IndexScript, 10, 0) WITH NOWAIT
        WAITFOR DELAY '00:00:01';
        EXEC(@IndexScript); 
    END

    FETCH NEXT FROM TableCursor INTO
        @TableName,
        @IndexName,
        @Fragmentation,
        @Schema;
 
END 
 
CLOSE TableCursor;
 
DEALLOCATE TableCursor;

Understanding the SET Statements

The script starts with several SET statements that configure the SQL Server session settings. These ensure consistent behavior and prevent issues:

SET QUOTED_IDENTIFIER ON

This setting allows double quotes to be used as identifiers (like table and column names). When ON, SQL Server treats double-quoted strings as object identifiers rather than string literals. This is important when working with object names that might be reserved words or contain special characters.

SET ARITHABORT ON

ARITHABORT terminates a query when an overflow or divide-by-zero error occurs. Setting it ON is a best practice because it prevents queries from continuing with invalid data, which is especially important during maintenance operations. This setting can also affect index usage in some scenarios.

SET NUMERIC_ROUNDABORT OFF

This setting controls error generation when rounding causes a loss of precision. Setting it OFF prevents errors from being raised during rounding operations. When ARITHABORT is ON, NUMERIC_ROUNDABORT is typically set to OFF to avoid conflicts.

SET CONCAT_NULL_YIELDS_NULL ON

When this is ON, concatenating a NULL value with any string results in NULL. This ensures predictable string concatenation behavior in the script when building dynamic SQL statements.

SET ANSI_NULLS ON

This setting controls NULL comparison behavior. When ON, any comparison with NULL using = or != returns NULL (unknown) rather than TRUE or FALSE. This is the SQL standard behavior and ensures consistent NULL handling.

SET ANSI_PADDING ON

When ON, strings are padded to their defined length, and trailing blanks in varchar values are significant. This ensures consistent string comparison behavior.

SET ANSI_WARNINGS ON

This setting enables ANSI standard warning messages for conditions like divide-by-zero, NULL in aggregate functions, and string truncation. Keeping warnings enabled helps identify potential issues during index maintenance.

How the Script Works

Step 1: Identify Fragmented Indexes

The first SELECT statement uses sys.dm_db_index_physical_stats to identify fragmented indexes. This dynamic management view provides physical statistics about indexes, including fragmentation percentages.

The query filters for indexes where:

• avg_fragmentation_in_percent >= 5.0: Only indexes with at least 5% fragmentation
• page_count > 10: Only indexes with more than 10 pages (small indexes don't benefit from maintenance)
• database_id = DB_ID(): Only indexes in the current database

Results are ordered by page count (ascending) and fragmentation (ascending), so smaller, less fragmented indexes are processed first. This is a safety measure - if you need to stop the script, you've at least fixed the smaller indexes first.

Step 2: Process Each Index with a Cursor

The script uses a cursor to iterate through each fragmented index. While cursors can be slower than set-based operations, they're appropriate here because:

• Each index needs individual processing
• We want to control the order of operations
• We need to build and execute dynamic SQL for each index

Step 3: Choose REBUILD or REORGANIZE

For each index, the script decides whether to REBUILD or REORGANIZE based on fragmentation level:

• Fragmentation >= 30%: Uses ALTER INDEX ... REBUILD
• Fragmentation >= 5% but < 30%: Uses ALTER INDEX ... REORGANIZE
• Fragmentation < 5%: No action (though this shouldn't happen due to the WHERE clause)

REBUILD vs REORGANIZE: When to Use Each

REBUILD

REBUILD completely rebuilds the index from scratch. It:

• Eliminates fragmentation completely
• Updates statistics automatically
• Reclaims disk space
• Requires more resources (CPU, I/O, locks)
• Can be done online (SQL Server 2012+) with WITH (ONLINE = ON)

Use REBUILD for severe fragmentation (30%+) because it's more thorough and efficient for heavily fragmented indexes.

REORGANIZE

REORGANIZE physically reorganizes leaf-level pages to match logical order. It:

• Reduces fragmentation but doesn't eliminate it completely
• Uses fewer resources than REBUILD
• Can be interrupted and resumed
• Is always online (doesn't block queries)
• Doesn't update statistics automatically

Use REORGANIZE for moderate fragmentation (5-30%) because it's faster and less resource-intensive, while still providing significant benefit.

Step 4: Execute the Maintenance

For each index, the script:

1. Builds the ALTER INDEX statement dynamically
2. Uses RAISERROR ... WITH NOWAIT to output the command immediately (so you can see progress)
3. Waits 1 second (WAITFOR DELAY '00:00:01') to prevent overwhelming the server
4. Executes the maintenance command
5. Moves to the next index

How to Use This Script

Step 1: Review Fragmentation First

Before running the maintenance script, run just the SELECT query to see what will be processed:

SELECT 
    dbtables.[name], 
    dbindexes.[name],
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count [pages]
FROM 
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
    INNER JOIN sys.tables dbtables 
        on dbtables.[object_id] = indexstats.[object_id]
    INNER JOIN sys.schemas dbschemas 
        on dbtables.[schema_id] = dbschemas.[schema_id]
    INNER JOIN sys.indexes AS dbindexes 
        ON dbindexes.[object_id] = indexstats.[object_id]
        AND indexstats.index_id = dbindexes.index_id
WHERE 
    indexstats.database_id = DB_ID()
    AND indexstats.avg_fragmentation_in_percent >= 5.0
    AND indexstats.page_count > 10
ORDER BY 
    indexstats.page_count ASC,
    indexstats.avg_fragmentation_in_percent ASC

This shows you how many indexes will be processed and their fragmentation levels. Review this to ensure the script will run for a reasonable amount of time.

Step 2: Run During Maintenance Window

While REORGANIZE is online and REBUILD can be online (with the ONLINE option), index maintenance still consumes resources. Run this script during a maintenance window or low-activity period to minimize impact on production workloads.

Step 3: Execute the Full Script

Run the complete script in SQL Server Management Studio. The RAISERROR ... WITH NOWAIT statements will show you the progress in real-time, so you can see which indexes are being processed.

Step 4: Monitor Progress

Watch the Messages tab in SSMS to see each command being executed. The script processes indexes from smallest to largest, so you'll see progress as it works through the list.

Important Considerations

Transaction Log Space

Index maintenance operations, especially REBUILD, generate significant transaction log activity. Ensure you have:

• Sufficient transaction log space
• Appropriate backup strategy (full or bulk-logged recovery models)
• Consider using WITH (ONLINE = ON) for REBUILD operations on Enterprise Edition

Resource Usage

Index maintenance is resource-intensive. Consider:

• Running during maintenance windows
• Adjusting the WAITFOR DELAY if needed to reduce load
• Using MAXDOP option to control parallelism for REBUILD operations
• Running on a subset of indexes if needed (modify the WHERE clause)

Large Indexes

For very large indexes, REBUILD operations can take hours. Consider:

• Using WITH (ONLINE = ON) to allow queries during rebuild
• Breaking large indexes into separate maintenance jobs
• Monitoring and potentially adjusting the fragmentation threshold

Enhanced Version with Online Rebuild

For SQL Server Enterprise Edition, you can enhance the script to use online rebuilds:

IF (@Fragmentation >= 30.0)
    SET @IndexScript = 'ALTER INDEX [' + @IndexName + '] ON ' + @Schema + '.' + @TableName + ' REBUILD WITH (ONLINE = ON)';
ELSE IF (@Fragmentation >= 5.0)
    SET @IndexScript = 'ALTER INDEX [' + @IndexName + '] ON ' + @Schema + '.' + @TableName + ' REORGANIZE';

Online rebuilds allow queries to continue accessing the table during maintenance, but they require Enterprise Edition and use more resources than offline rebuilds.

Automating with SQL Agent Jobs

To automate this maintenance, create a SQL Server Agent job that runs on a schedule:

1. Create a new SQL Server Agent job
2. Add a step with this script
3. Schedule it to run during maintenance windows (e.g., weekly during low-activity periods)
4. Configure notifications for job completion/failure

Be careful with automation - you may want to add additional checks or logging before scheduling this to run automatically.

Best Practices

• Run regularly: Schedule index maintenance weekly or monthly depending on your database's update patterns
• Monitor fragmentation: Track fragmentation levels over time to understand your maintenance needs
• Consider fill factor: If indexes fragment quickly, consider adjusting fill factor to leave more space for inserts
• Update statistics: After index maintenance, consider updating statistics (REBUILD does this automatically, but REORGANIZE doesn't)
• Test first: Run the SELECT query first to understand what will be processed
• Monitor performance: Track query performance before and after maintenance to measure improvement

When Not to Use This Script

This script may not be appropriate if:

• You have very large indexes that would take too long to maintain
• Your maintenance window is too short
• You need more granular control over which indexes are maintained
• You want to use Ola Hallengren's IndexOptimize (a popular third-party solution)
• You need to maintain specific indexes with custom options

Conclusion

This script automates index maintenance by identifying fragmented indexes and choosing the appropriate maintenance operation based on fragmentation level. REBUILD for severe fragmentation (30%+), REORGANIZE for moderate fragmentation (5-30%), and skip small indexes that don't benefit from maintenance.

Regular index maintenance is essential for maintaining query performance as databases grow and change. This script provides a solid foundation for automated index maintenance, but remember to monitor resource usage, transaction log space, and adjust thresholds based on your specific environment.

Start by running the SELECT query to see what needs maintenance, then run the full script during a maintenance window. For production environments, consider enhancing it with online rebuilds, better logging, and scheduling it as a SQL Agent job for regular automated maintenance.