Database Management SystemsMaintenance Tasks

Database Maintenance Tasks

LevelAdvanced

Duration90 mins

TopicMaintenance Tasks

2 / 5

Index Maintenance

Keeping Your Indexes in Peak Condition

Indexes are the turbochargers of database performance. A well-designed index can transform a 10-second query into a 10-millisecond query—a 1000x improvement. But indexes, like all data structures, degrade over time.

As data is inserted, updated, and deleted, indexes become fragmented. Pages split, empty space accumulates, and the physical ordering of data drifts from the logical ordering. What was once a lean, efficient structure becomes bloated and slow.

Index maintenance is not optional—it is essential for sustained performance.

Neglecting index maintenance is like never changing your car's oil. It works fine... until it doesn't. And when it fails, it fails catastrophically.

What You Will Learn

By the end of this page, you will understand how indexes degrade over time, master fragmentation analysis techniques, know when to rebuild versus reorganize indexes, execute online index operations with minimal disruption, and develop automated maintenance strategies for production environments.

Understanding Index Degradation

To understand why indexes need maintenance, we must first understand how they degrade. Most database indexes use B-tree or B+-tree structures, which organize data in a hierarchical, sorted manner.

How B-tree indexes become fragmented:

Page Splits: When a leaf page fills up and a new row must be inserted, the database splits the page in two. This creates two half-empty pages and may cause the new page to be allocated far from the original—destroying physical continuity.
Delete Holes: When rows are deleted, space is freed within pages. If subsequent inserts don't fill these gaps, pages remain partially empty, wasting space and I/O.
In-Place Updates: Updates that increase row size may not fit in the original location, causing row movement and leaving behind forwarding pointers.
Random Insertions: If insertions aren't sequential (like random GUIDs for primary keys), every insert potentially causes a page split, rapidly fragmenting the index.

Types of Index Fragmentation
Fragmentation Type	Description	Impact	Remediation
Internal Fragmentation	Free space within index pages (low page fullness)	More pages to scan, wasted disk/memory	Rebuild or Reorganize
External Fragmentation	Logical page order differs from physical disk order	Sequential reads become random I/O	Rebuild
Logical Fragmentation	Pages out of logical key order	Range scans less efficient	Reorganize or Rebuild
Index Bloat	Dead tuples/versions not yet vacuumed (PostgreSQL)	Index scans examine dead rows	VACUUM or REINDEX

The 8KB Page Reality

Most databases use 8KB pages. When a page splits, you get two ~4KB pages. If this happens repeatedly, you might have pages that are only 50-60% full. This means 40-50% wasted space, and twice the I/O required to read the same amount of data.

Measuring Index Fragmentation

Before deciding on maintenance actions, you must measure the current state of your indexes. Different database systems provide different tools for fragmentation analysis. Understanding the metrics is critical for making informed decisions.

fragmentation_analysis.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- SQL Server: Comprehensive fragmentation analysis
 
-- Basic fragmentation query using DMV
SELECT 
    OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc AS index_type,
    ips.index_depth,
    ips.avg_fragmentation_in_percent,
    ips.avg_page_space_used_in_percent,
    ips.page_count,
    ips.record_count,
    ips.ghost_record_count,
    CASE 
        WHEN ips.avg_fragmentation_in_percent > 30 THEN 'REBUILD'
        WHEN ips.avg_fragmentation_in_percent > 10 THEN 'REORGANIZE'
        ELSE 'OK'
    END AS recommended_action
FROM sys.dm_db_index_physical_stats(
    DB_ID(),           -- Current database
    NULL,              -- All tables
    NULL,              -- All indexes
    NULL,              -- All partitions
    'SAMPLED'          -- Mode: LIMITED, SAMPLED, or DETAILED
) ips
JOIN sys.indexes i ON ips.object_id = i.object_id 
    AND ips.index_id = i.index_id
WHERE ips.page_count > 1000  -- Ignore small indexes
  AND i.name IS NOT NULL
ORDER BY ips.avg_fragmentation_in_percent DESC;
 
-- Fragmentation modes explained:
-- LIMITED: Fastest, scans parent level pages only
-- SAMPLED: Intermediate, samples 1% of pages (min 10K pages)
-- DETAILED: Slowest, scans all pages - most accurate
 
-- For large tables, consider LIMITED for routine checks
SELECT * FROM sys.dm_db_index_physical_stats(
    DB_ID(), OBJECT_ID('orders'), NULL, NULL, 'LIMITED');

Fragmentation Thresholds

General industry guidelines: 0-10% fragmentation = OK, 10-30% = Consider maintenance, 30%+ = Maintenance recommended. However, these are guidelines, not rules. Small indexes (<1000 pages) rarely benefit from defragmentation. SSD storage reduces the penalty of physical fragmentation. Always measure before and after to validate improvement.

Rebuild vs. Reorganize: Making the Right Choice

Database systems typically offer two approaches to index maintenance: reorganization (online defragmentation) and rebuild (complete reconstruction). Understanding the differences is crucial for choosing the right approach.

Index Reorganize

•Operation: Defragments leaf level by physically reordering pages
•Logging: Minimally logged, small transaction log impact
•Locking: Online operation, allows concurrent access
•Resources: Lower CPU and memory requirements
•Interruptible: Can be stopped and resumed
•Best for: Moderate fragmentation (10-30%), 24/7 systems
•Limitation: Only fixes leaf-level fragmentation

Index Rebuild

•Operation: Drops and recreates entire index structure
•Logging: Fully logged (unless BULK_LOGGED recovery)
•Locking: Offline by default, locks table (online option available)
•Resources: Higher CPU, memory, and disk requirements
•Interruptible: Must complete or roll back entirely
•Best for: Heavy fragmentation (30%+), fill factor changes
•Benefit: Completely optimizes index structure

rebuild_reorganize.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- SQL Server: Reorganize vs Rebuild examples
 
-- REORGANIZE: Online, interruptible, minimal logging
ALTER INDEX IX_orders_customer 
ON orders REORGANIZE;
 
-- Reorganize with LOB compaction
ALTER INDEX IX_orders_details 
ON orders REORGANIZE WITH (LOB_COMPACTION = ON);
 
-- Reorganize specific partition
ALTER INDEX IX_orders_date 
ON orders REORGANIZE PARTITION = 5;
 
-- REBUILD: More thorough, can be online
-- Offline rebuild (fastest, but locks table)
ALTER INDEX IX_orders_customer 
ON orders REBUILD;
 
-- Online rebuild (concurrent access, slower, Enterprise only)
ALTER INDEX IX_orders_customer 
ON orders REBUILD WITH (ONLINE = ON);
 
-- Rebuild with new fill factor
ALTER INDEX IX_orders_customer 
ON orders REBUILD WITH (
    FILLFACTOR = 80,     -- 80% fill = 20% free space for inserts
    ONLINE = ON,
    SORT_IN_TEMPDB = ON  -- Use tempdb for sorting
);
 
-- Rebuild ALL indexes on a table
ALTER INDEX ALL ON orders REBUILD;
 
-- Resumable rebuild (SQL Server 2017+)
ALTER INDEX IX_orders_customer ON orders REBUILD
WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60);
 
-- If interrupted, resume with:
ALTER INDEX IX_orders_customer ON orders RESUME;
 
-- Check resumable operation status
SELECT * FROM sys.index_resumable_operations;

Fill Factor Considerations

Fill factor (or fillfactor) controls how full pages are when an index is built/rebuilt. Lower fill factor (e.g., 80%) leaves 20% free space for new inserts, reducing page splits but using more disk. Higher fill factor (e.g., 100%) maximizes read performance but causes more splits during writes. Choose based on your read/write ratio.

Online Index Operations in Production

Production databases that must remain available 24/7 require online index operations that allow concurrent reads and writes. Understanding how these work—and their limitations—is essential for maintaining high-availability systems.

Online Index Operation Support by Database
Operation	SQL Server	PostgreSQL	MySQL/InnoDB
Online Rebuild	Enterprise Edition with ONLINE=ON	REINDEX CONCURRENTLY (12+)	ALTER TABLE... ALGORITHM=INPLACE
Online Create	CREATE INDEX... WITH (ONLINE=ON)	CREATE INDEX CONCURRENTLY	ALTER TABLE... LOCK=NONE
Resumable Index	Yes (2017+)	No native support	No native support
Parallel Build	Yes (MAXDOP option)	Yes (max_parallel_maintenance_workers)	Some operations (8.0+)
Progress Tracking	sys.dm_exec_requests	pg_stat_progress_create_index	performance_schema

online_operations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
-- SQL Server: Online index operations best practices
 
-- Online rebuild with all options
ALTER INDEX IX_orders_date ON orders REBUILD
WITH (
    ONLINE = ON,                    -- Allow concurrent access
    RESUMABLE = ON,                 -- Can pause and resume
    MAX_DURATION = 120,             -- Max minutes before auto-pause
    WAIT_AT_LOW_PRIORITY (          -- Handle locks gracefully
        MAX_DURATION = 1,           -- Wait 1 minute for locks  
        ABORT_AFTER_WAIT = SELF     -- Kill rebuild, not blockers
    ),
    SORT_IN_TEMPDB = ON,            -- Reduce production DB I/O
    MAXDOP = 4                      -- Limit parallelism
);
 
-- Monitor online index operations
SELECT 
    r.session_id,
    r.command,
    r.percent_complete,
    r.estimated_completion_time / 1000 / 60 AS est_minutes_remaining,
    t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command LIKE '%INDEX%';
 
-- Track blocking during online rebuild
SELECT 
    blocking_session_id,
    session_id,
    wait_type,
    wait_time,
    wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
 
-- Handle long-running online operations
-- Pause if taking too long
ALTER INDEX IX_orders_date ON orders PAUSE;
 
-- Check paused operation status
SELECT 
    name AS index_name,
    state_desc,
    percent_complete,
    start_time,
    last_pause_time
FROM sys.index_resumable_operations;
 
-- Resume later
ALTER INDEX IX_orders_date ON orders RESUME
WITH (MAXDOP = 8);  -- Can change options on resume

Online Operation Trade-offs

Online operations prevent blocking but are slower (often 2-3x) than offline equivalents. They also require more disk space (building new structure alongside old) and may increase transaction log usage. For very large indexes during maintenance windows, offline operations may be more efficient.

Automated Index Maintenance Strategies

Manual index maintenance doesn't scale. Production databases may have hundreds or thousands of indexes across many tables. Implementing intelligent, automated maintenance is essential for sustainable operations.

Key Principles for Automated Maintenance

•Assess Before Acting — Check fragmentation levels before maintenance. Don't waste resources on healthy indexes.
•Tiered Approach — Apply different actions based on fragmentation level: ignore < 10%, reorganize 10-30%, rebuild > 30%.
•Size Threshold — Ignore very small indexes (< 1000 pages). Maintenance overhead exceeds benefit.
•Workload Awareness — Schedule during low-activity periods. Use online operations during peak hours if necessary.
•Logging and Auditing — Record what was done, when, and how long it took. Essential for troubleshooting and optimization.
•Error Handling — Scripts must handle failures gracefully and continue with remaining indexes.

automated_maintenance.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
-- SQL Server: Comprehensive automated index maintenance
 
CREATE OR ALTER PROCEDURE dbo.MaintainIndexes
    @FragmentationThresholdReorg FLOAT = 10.0,
    @FragmentationThresholdRebuild FLOAT = 30.0,
    @MinPageCount INT = 1000,
    @OnlineOnly BIT = 0,
    @MaxDOP INT = 4,
    @DebugMode BIT = 0
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @StartTime DATETIME2;
    DECLARE @IndexName SYSNAME;
    DECLARE @TableName SYSNAME;
    DECLARE @FragPct FLOAT;
    DECLARE @Action VARCHAR(20);
    
    -- Create logging table
    IF OBJECT_ID('dbo.IndexMaintenanceLog') IS NULL
    BEGIN
        CREATE TABLE dbo.IndexMaintenanceLog (
            LogID INT IDENTITY PRIMARY KEY,
            ExecutionTime DATETIME2 DEFAULT SYSDATETIME(),
            TableName SYSNAME,
            IndexName SYSNAME,
            FragmentationBefore FLOAT,
            Action VARCHAR(20),
            DurationMs INT,
            Status VARCHAR(20),
            ErrorMessage NVARCHAR(MAX)
        );
    END;
    
    -- Build maintenance list
    DECLARE maintenance_cursor CURSOR FOR
    SELECT 
        OBJECT_SCHEMA_NAME(ips.object_id) + '.' + 
            OBJECT_NAME(ips.object_id) AS table_name,
        i.name AS index_name,
        ips.avg_fragmentation_in_percent AS frag_pct,
        CASE 
            WHEN ips.avg_fragmentation_in_percent >= @FragmentationThresholdRebuild 
                THEN 'REBUILD'
            WHEN ips.avg_fragmentation_in_percent >= @FragmentationThresholdReorg 
                THEN 'REORGANIZE'
            ELSE 'SKIP'
        END AS action
    FROM sys.dm_db_index_physical_stats(
        DB_ID(), NULL, NULL, NULL, 'LIMITED'
    ) ips
    JOIN sys.indexes i 
        ON ips.object_id = i.object_id AND ips.index_id = i.index_id
    WHERE i.name IS NOT NULL
      AND ips.page_count >= @MinPageCount
      AND ips.avg_fragmentation_in_percent >= @FragmentationThresholdReorg
    ORDER BY ips.page_count DESC;  -- Largest first
    
    OPEN maintenance_cursor;
    FETCH NEXT FROM maintenance_cursor INTO 
        @TableName, @IndexName, @FragPct, @Action;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @StartTime = SYSDATETIME();
        
        BEGIN TRY
            IF @Action = 'REBUILD'
            BEGIN
                SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON ' + 
                          @TableName + ' REBUILD';
                IF @OnlineOnly = 1
                    SET @SQL = @SQL + ' WITH (ONLINE = ON, MAXDOP = ' + 
                              CAST(@MaxDOP AS VARCHAR) + ')';
            END
            ELSE IF @Action = 'REORGANIZE'
            BEGIN
                SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON ' + 
                          @TableName + ' REORGANIZE';
            END;
            
            IF @DebugMode = 1
                PRINT @SQL;
            ELSE
                EXEC sp_executesql @SQL;
            
            INSERT INTO dbo.IndexMaintenanceLog 
                (TableName, IndexName, FragmentationBefore, 
                 Action, DurationMs, Status)
            VALUES (@TableName, @IndexName, @FragPct, @Action,
                    DATEDIFF(MILLISECOND, @StartTime, SYSDATETIME()), 
                    'SUCCESS');
        END TRY
        BEGIN CATCH
            INSERT INTO dbo.IndexMaintenanceLog 
                (TableName, IndexName, FragmentationBefore, 
                 Action, DurationMs, Status, ErrorMessage)
            VALUES (@TableName, @IndexName, @FragPct, @Action,
                    DATEDIFF(MILLISECOND, @StartTime, SYSDATETIME()), 
                    'FAILED', ERROR_MESSAGE());
        END CATCH;
        
        FETCH NEXT FROM maintenance_cursor INTO 
            @TableName, @IndexName, @FragPct, @Action;
    END;
    
    CLOSE maintenance_cursor;
    DEALLOCATE maintenance_cursor;
    
    -- Report summary
    SELECT 
        Action,
        COUNT(*) AS IndexCount,
        SUM(DurationMs) / 1000 AS TotalSeconds,
        SUM(CASE WHEN Status = 'FAILED' THEN 1 ELSE 0 END) AS Failures
    FROM dbo.IndexMaintenanceLog
    WHERE ExecutionTime > DATEADD(HOUR, -1, SYSDATETIME())
    GROUP BY Action;
END;
GO
 
-- Execute maintenance
EXEC dbo.MaintainIndexes @OnlineOnly = 1, @MaxDOP = 2;

Maintenance Windows and Scheduling

Even with online operations, index maintenance consumes resources and can impact performance. Careful scheduling is essential to balance maintenance needs with application requirements.

Index Maintenance Scheduling Strategies
Strategy	Description	Best For	Considerations
Nightly Window	Run all maintenance in 4-6 hour overnight window	Traditional business apps, batch systems	Must complete before business hours; prioritize critical indexes
Continuous Trickle	Small maintenance tasks spread throughout day	24/7 systems with no true downtime	Use online ops; throttle to minimize impact
Weekend Deep Clean	Major rebuilds on weekends, minor during week	Mixed workload environments	Reorganize during week, rebuild weekends
Event-Driven	Trigger maintenance after bulk loads or major changes	Data warehouse, batch processing	Integrate with ETL pipelines
Threshold-Based	Maintain only when fragmentation exceeds threshold	Resource-constrained environments	Risk of maintenance clustering

Maintenance Window Best Practices

•Prioritize by Impact — Start with largest, most fragmented, most frequently used indexes. If time runs out, critical indexes are already done.
•Estimate Duration — Track historical maintenance times. Use this to predict if maintenance will fit in available window.
•Plan for Overrun — If maintenance might exceed window, have a graceful stop point. Don't leave indexes in inconsistent state.
•Coordinate with Backups — Don't run maintenance during backups. Both compete for I/O and may extend backup times.
•Monitor Resource Usage — Track CPU, I/O, and memory during maintenance. Adjust parallelism if impacting other operations.
•Test in Non-Production First — Validate maintenance scripts in lower environments before production deployment.

Adaptive Maintenance

Modern maintenance scripts can be adaptive: start with online operations during business hours, switch to offline during maintenance windows for faster completion. Track maintenance velocity and adjust strategy based on available time.

Special Index Maintenance Considerations

Beyond standard B-tree indexes, modern databases support specialized index types that have unique maintenance requirements.

Specialized Index Maintenance
Index Type	Fragmentation Behavior	Maintenance Approach
Columnstore (SQL Server)	Compressed segments; deleted rows in delta stores	REORGANIZE merges delta stores; REBUILD recreates compressed segments
Full-Text	Inverted index with word mappings	REORGANIZE refreshes change tracking; REBUILD recreates catalog
Spatial (R-Tree)	Similar to B-tree but for multi-dimensional data	Rebuild when query performance degrades; no simple fragmentation metric
Filtered	Subset of rows indexed	Same maintenance as regular indexes; verify filter predicate after schema changes
GIN/GiST (PostgreSQL)	Generalized inverted/search tree indexes	REINDEX; check for pending updates in fastupdate lists
BRIN (PostgreSQL)	Block range indexes	Very compact; generally don't need maintenance; REINDEX if data distribution changes

specialized_maintenance.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- Columnstore index maintenance
 
-- Check columnstore fragmentation
SELECT 
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    SUM(rg.deleted_rows) AS total_deleted_rows,
    SUM(rg.total_rows) AS total_rows,
    100.0 * SUM(rg.deleted_rows) / NULLIF(SUM(rg.total_rows), 0) 
        AS delete_ratio_pct,
    COUNT(CASE WHEN rg.state = 1 THEN 1 END) AS open_rowgroups,
    COUNT(CASE WHEN rg.state = 2 THEN 1 END) AS closed_rowgroups,
    COUNT(CASE WHEN rg.state = 3 THEN 1 END) AS compressed_rowgroups
FROM sys.column_store_row_groups rg
JOIN sys.indexes i ON rg.object_id = i.object_id 
    AND rg.index_id = i.index_id
GROUP BY i.object_id, i.name
ORDER BY delete_ratio_pct DESC;
 
-- Columnstore maintenance
-- REORGANIZE: Merges delta stores, compresses open rowgroups
ALTER INDEX CCI_orders ON orders REORGANIZE;
 
-- REORGANIZE with COMPRESS_ALL_ROW_GROUPS
ALTER INDEX CCI_orders ON orders REORGANIZE 
WITH (COMPRESS_ALL_ROW_GROUPS = ON);
 
-- REBUILD: Complete recreation (use when highly fragmented)
ALTER INDEX CCI_orders ON orders REBUILD WITH (ONLINE = ON);
 
-- Full-text index maintenance
-- Check full-text catalog status
SELECT 
    name AS catalog_name,
    is_importing,
    item_count,
    FULLTEXTCATALOGPROPERTY(name, 'PopulateStatus') AS pop_status
FROM sys.fulltext_catalogs;
 
-- Rebuild full-text catalog
ALTER FULLTEXT CATALOG FT_OrdersCatalog REBUILD;
 
-- Reorganize full-text index (process pending changes)
ALTER FULLTEXT INDEX ON orders REORGANIZE;

Summary: Mastering Index Maintenance

Index maintenance is a critical ongoing responsibility for database administrators. Without regular attention, indexes degrade, query performance suffers, and what was once a fast system becomes frustratingly slow.

Key Takeaways

•Indexes degrade through normal operations — Page splits, deletions, and random insertions cause internal and external fragmentation.
•Measure before maintaining — Use fragmentation analysis queries to assess index health. Don't waste resources on healthy indexes.
•Reorganize for moderate fragmentation — Less resource-intensive, online, interruptible. Best for 10-30% fragmentation.
•Rebuild for heavy fragmentation — Complete reconstruction. More thorough but more expensive. Best for 30%+ fragmentation.
•Online operations enable 24/7 availability — But come with trade-offs: slower, more disk space, higher transaction log usage.
•Automation is essential at scale — Implement intelligent maintenance scripts that assess, prioritize, execute, and log.
•Schedule strategically — Balance maintenance needs against application workload. Coordinate with backups and other operations.

What's next:

Indexes aren't the only thing that needs regular attention. In the next page, we'll explore Log Management—how to manage transaction logs, archive historical data, implement log rotation, and prevent logs from consuming all available storage.

Page Complete

You now understand index degradation, fragmentation analysis, rebuild vs. reorganize decisions, online operations, and automated maintenance strategies. Apply these principles to keep your database indexes in peak condition.

2 / 5

Loading learning content...

Database Management SystemsMaintenance Tasks

Database Maintenance Tasks

LevelAdvanced

Duration90 mins

TopicMaintenance Tasks

2 / 5

Index Maintenance

Keeping Your Indexes in Peak Condition

Index maintenance is not optional—it is essential for sustained performance.

Neglecting index maintenance is like never changing your car's oil. It works fine... until it doesn't. And when it fails, it fails catastrophically.

What You Will Learn

Understanding Index Degradation

How B-tree indexes become fragmented:

Page Splits: When a leaf page fills up and a new row must be inserted, the database splits the page in two. This creates two half-empty pages and may cause the new page to be allocated far from the original—destroying physical continuity.
Delete Holes: When rows are deleted, space is freed within pages. If subsequent inserts don't fill these gaps, pages remain partially empty, wasting space and I/O.
In-Place Updates: Updates that increase row size may not fit in the original location, causing row movement and leaving behind forwarding pointers.
Random Insertions: If insertions aren't sequential (like random GUIDs for primary keys), every insert potentially causes a page split, rapidly fragmenting the index.

Types of Index Fragmentation
Fragmentation Type	Description	Impact	Remediation
Internal Fragmentation	Free space within index pages (low page fullness)	More pages to scan, wasted disk/memory	Rebuild or Reorganize
External Fragmentation	Logical page order differs from physical disk order	Sequential reads become random I/O	Rebuild
Logical Fragmentation	Pages out of logical key order	Range scans less efficient	Reorganize or Rebuild
Index Bloat	Dead tuples/versions not yet vacuumed (PostgreSQL)	Index scans examine dead rows	VACUUM or REINDEX

The 8KB Page Reality

Measuring Index Fragmentation

fragmentation_analysis.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- SQL Server: Comprehensive fragmentation analysis
 
-- Basic fragmentation query using DMV
SELECT 
    OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc AS index_type,
    ips.index_depth,
    ips.avg_fragmentation_in_percent,
    ips.avg_page_space_used_in_percent,
    ips.page_count,
    ips.record_count,
    ips.ghost_record_count,
    CASE 
        WHEN ips.avg_fragmentation_in_percent > 30 THEN 'REBUILD'
        WHEN ips.avg_fragmentation_in_percent > 10 THEN 'REORGANIZE'
        ELSE 'OK'
    END AS recommended_action
FROM sys.dm_db_index_physical_stats(
    DB_ID(),           -- Current database
    NULL,              -- All tables
    NULL,              -- All indexes
    NULL,              -- All partitions
    'SAMPLED'          -- Mode: LIMITED, SAMPLED, or DETAILED
) ips
JOIN sys.indexes i ON ips.object_id = i.object_id 
    AND ips.index_id = i.index_id
WHERE ips.page_count > 1000  -- Ignore small indexes
  AND i.name IS NOT NULL
ORDER BY ips.avg_fragmentation_in_percent DESC;
 
-- Fragmentation modes explained:
-- LIMITED: Fastest, scans parent level pages only
-- SAMPLED: Intermediate, samples 1% of pages (min 10K pages)
-- DETAILED: Slowest, scans all pages - most accurate
 
-- For large tables, consider LIMITED for routine checks
SELECT * FROM sys.dm_db_index_physical_stats(
    DB_ID(), OBJECT_ID('orders'), NULL, NULL, 'LIMITED');

Fragmentation Thresholds

Rebuild vs. Reorganize: Making the Right Choice

Index Reorganize

•Operation: Defragments leaf level by physically reordering pages
•Logging: Minimally logged, small transaction log impact
•Locking: Online operation, allows concurrent access
•Resources: Lower CPU and memory requirements
•Interruptible: Can be stopped and resumed
•Best for: Moderate fragmentation (10-30%), 24/7 systems
•Limitation: Only fixes leaf-level fragmentation

Index Rebuild

•Operation: Drops and recreates entire index structure
•Logging: Fully logged (unless BULK_LOGGED recovery)
•Locking: Offline by default, locks table (online option available)
•Resources: Higher CPU, memory, and disk requirements
•Interruptible: Must complete or roll back entirely
•Best for: Heavy fragmentation (30%+), fill factor changes
•Benefit: Completely optimizes index structure

rebuild_reorganize.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- SQL Server: Reorganize vs Rebuild examples
 
-- REORGANIZE: Online, interruptible, minimal logging
ALTER INDEX IX_orders_customer 
ON orders REORGANIZE;
 
-- Reorganize with LOB compaction
ALTER INDEX IX_orders_details 
ON orders REORGANIZE WITH (LOB_COMPACTION = ON);
 
-- Reorganize specific partition
ALTER INDEX IX_orders_date 
ON orders REORGANIZE PARTITION = 5;
 
-- REBUILD: More thorough, can be online
-- Offline rebuild (fastest, but locks table)
ALTER INDEX IX_orders_customer 
ON orders REBUILD;
 
-- Online rebuild (concurrent access, slower, Enterprise only)
ALTER INDEX IX_orders_customer 
ON orders REBUILD WITH (ONLINE = ON);
 
-- Rebuild with new fill factor
ALTER INDEX IX_orders_customer 
ON orders REBUILD WITH (
    FILLFACTOR = 80,     -- 80% fill = 20% free space for inserts
    ONLINE = ON,
    SORT_IN_TEMPDB = ON  -- Use tempdb for sorting
);
 
-- Rebuild ALL indexes on a table
ALTER INDEX ALL ON orders REBUILD;
 
-- Resumable rebuild (SQL Server 2017+)
ALTER INDEX IX_orders_customer ON orders REBUILD
WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60);
 
-- If interrupted, resume with:
ALTER INDEX IX_orders_customer ON orders RESUME;
 
-- Check resumable operation status
SELECT * FROM sys.index_resumable_operations;

Fill Factor Considerations

Online Index Operations in Production

Online Index Operation Support by Database
Operation	SQL Server	PostgreSQL	MySQL/InnoDB
Online Rebuild	Enterprise Edition with ONLINE=ON	REINDEX CONCURRENTLY (12+)	ALTER TABLE... ALGORITHM=INPLACE
Online Create	CREATE INDEX... WITH (ONLINE=ON)	CREATE INDEX CONCURRENTLY	ALTER TABLE... LOCK=NONE
Resumable Index	Yes (2017+)	No native support	No native support
Parallel Build	Yes (MAXDOP option)	Yes (max_parallel_maintenance_workers)	Some operations (8.0+)
Progress Tracking	sys.dm_exec_requests	pg_stat_progress_create_index	performance_schema

online_operations.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
-- SQL Server: Online index operations best practices
 
-- Online rebuild with all options
ALTER INDEX IX_orders_date ON orders REBUILD
WITH (
    ONLINE = ON,                    -- Allow concurrent access
    RESUMABLE = ON,                 -- Can pause and resume
    MAX_DURATION = 120,             -- Max minutes before auto-pause
    WAIT_AT_LOW_PRIORITY (          -- Handle locks gracefully
        MAX_DURATION = 1,           -- Wait 1 minute for locks  
        ABORT_AFTER_WAIT = SELF     -- Kill rebuild, not blockers
    ),
    SORT_IN_TEMPDB = ON,            -- Reduce production DB I/O
    MAXDOP = 4                      -- Limit parallelism
);
 
-- Monitor online index operations
SELECT 
    r.session_id,
    r.command,
    r.percent_complete,
    r.estimated_completion_time / 1000 / 60 AS est_minutes_remaining,
    t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command LIKE '%INDEX%';
 
-- Track blocking during online rebuild
SELECT 
    blocking_session_id,
    session_id,
    wait_type,
    wait_time,
    wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
 
-- Handle long-running online operations
-- Pause if taking too long
ALTER INDEX IX_orders_date ON orders PAUSE;
 
-- Check paused operation status
SELECT 
    name AS index_name,
    state_desc,
    percent_complete,
    start_time,
    last_pause_time
FROM sys.index_resumable_operations;
 
-- Resume later
ALTER INDEX IX_orders_date ON orders RESUME
WITH (MAXDOP = 8);  -- Can change options on resume

Online Operation Trade-offs

Automated Index Maintenance Strategies

Key Principles for Automated Maintenance

•Assess Before Acting — Check fragmentation levels before maintenance. Don't waste resources on healthy indexes.
•Tiered Approach — Apply different actions based on fragmentation level: ignore < 10%, reorganize 10-30%, rebuild > 30%.
•Size Threshold — Ignore very small indexes (< 1000 pages). Maintenance overhead exceeds benefit.
•Workload Awareness — Schedule during low-activity periods. Use online operations during peak hours if necessary.
•Logging and Auditing — Record what was done, when, and how long it took. Essential for troubleshooting and optimization.
•Error Handling — Scripts must handle failures gracefully and continue with remaining indexes.

automated_maintenance.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
-- SQL Server: Comprehensive automated index maintenance
 
CREATE OR ALTER PROCEDURE dbo.MaintainIndexes
    @FragmentationThresholdReorg FLOAT = 10.0,
    @FragmentationThresholdRebuild FLOAT = 30.0,
    @MinPageCount INT = 1000,
    @OnlineOnly BIT = 0,
    @MaxDOP INT = 4,
    @DebugMode BIT = 0
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @StartTime DATETIME2;
    DECLARE @IndexName SYSNAME;
    DECLARE @TableName SYSNAME;
    DECLARE @FragPct FLOAT;
    DECLARE @Action VARCHAR(20);
    
    -- Create logging table
    IF OBJECT_ID('dbo.IndexMaintenanceLog') IS NULL
    BEGIN
        CREATE TABLE dbo.IndexMaintenanceLog (
            LogID INT IDENTITY PRIMARY KEY,
            ExecutionTime DATETIME2 DEFAULT SYSDATETIME(),
            TableName SYSNAME,
            IndexName SYSNAME,
            FragmentationBefore FLOAT,
            Action VARCHAR(20),
            DurationMs INT,
            Status VARCHAR(20),
            ErrorMessage NVARCHAR(MAX)
        );
    END;
    
    -- Build maintenance list
    DECLARE maintenance_cursor CURSOR FOR
    SELECT 
        OBJECT_SCHEMA_NAME(ips.object_id) + '.' + 
            OBJECT_NAME(ips.object_id) AS table_name,
        i.name AS index_name,
        ips.avg_fragmentation_in_percent AS frag_pct,
        CASE 
            WHEN ips.avg_fragmentation_in_percent >= @FragmentationThresholdRebuild 
                THEN 'REBUILD'
            WHEN ips.avg_fragmentation_in_percent >= @FragmentationThresholdReorg 
                THEN 'REORGANIZE'
            ELSE 'SKIP'
        END AS action
    FROM sys.dm_db_index_physical_stats(
        DB_ID(), NULL, NULL, NULL, 'LIMITED'
    ) ips
    JOIN sys.indexes i 
        ON ips.object_id = i.object_id AND ips.index_id = i.index_id
    WHERE i.name IS NOT NULL
      AND ips.page_count >= @MinPageCount
      AND ips.avg_fragmentation_in_percent >= @FragmentationThresholdReorg
    ORDER BY ips.page_count DESC;  -- Largest first
    
    OPEN maintenance_cursor;
    FETCH NEXT FROM maintenance_cursor INTO 
        @TableName, @IndexName, @FragPct, @Action;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @StartTime = SYSDATETIME();
        
        BEGIN TRY
            IF @Action = 'REBUILD'
            BEGIN
                SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON ' + 
                          @TableName + ' REBUILD';
                IF @OnlineOnly = 1
                    SET @SQL = @SQL + ' WITH (ONLINE = ON, MAXDOP = ' + 
                              CAST(@MaxDOP AS VARCHAR) + ')';
            END
            ELSE IF @Action = 'REORGANIZE'
            BEGIN
                SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON ' + 
                          @TableName + ' REORGANIZE';
            END;
            
            IF @DebugMode = 1
                PRINT @SQL;
            ELSE
                EXEC sp_executesql @SQL;
            
            INSERT INTO dbo.IndexMaintenanceLog 
                (TableName, IndexName, FragmentationBefore, 
                 Action, DurationMs, Status)
            VALUES (@TableName, @IndexName, @FragPct, @Action,
                    DATEDIFF(MILLISECOND, @StartTime, SYSDATETIME()), 
                    'SUCCESS');
        END TRY
        BEGIN CATCH
            INSERT INTO dbo.IndexMaintenanceLog 
                (TableName, IndexName, FragmentationBefore, 
                 Action, DurationMs, Status, ErrorMessage)
            VALUES (@TableName, @IndexName, @FragPct, @Action,
                    DATEDIFF(MILLISECOND, @StartTime, SYSDATETIME()), 
                    'FAILED', ERROR_MESSAGE());
        END CATCH;
        
        FETCH NEXT FROM maintenance_cursor INTO 
            @TableName, @IndexName, @FragPct, @Action;
    END;
    
    CLOSE maintenance_cursor;
    DEALLOCATE maintenance_cursor;
    
    -- Report summary
    SELECT 
        Action,
        COUNT(*) AS IndexCount,
        SUM(DurationMs) / 1000 AS TotalSeconds,
        SUM(CASE WHEN Status = 'FAILED' THEN 1 ELSE 0 END) AS Failures
    FROM dbo.IndexMaintenanceLog
    WHERE ExecutionTime > DATEADD(HOUR, -1, SYSDATETIME())
    GROUP BY Action;
END;
GO
 
-- Execute maintenance
EXEC dbo.MaintainIndexes @OnlineOnly = 1, @MaxDOP = 2;

Maintenance Windows and Scheduling

Even with online operations, index maintenance consumes resources and can impact performance. Careful scheduling is essential to balance maintenance needs with application requirements.

Index Maintenance Scheduling Strategies
Strategy	Description	Best For	Considerations
Nightly Window	Run all maintenance in 4-6 hour overnight window	Traditional business apps, batch systems	Must complete before business hours; prioritize critical indexes
Continuous Trickle	Small maintenance tasks spread throughout day	24/7 systems with no true downtime	Use online ops; throttle to minimize impact
Weekend Deep Clean	Major rebuilds on weekends, minor during week	Mixed workload environments	Reorganize during week, rebuild weekends
Event-Driven	Trigger maintenance after bulk loads or major changes	Data warehouse, batch processing	Integrate with ETL pipelines
Threshold-Based	Maintain only when fragmentation exceeds threshold	Resource-constrained environments	Risk of maintenance clustering

Maintenance Window Best Practices

•Prioritize by Impact — Start with largest, most fragmented, most frequently used indexes. If time runs out, critical indexes are already done.
•Estimate Duration — Track historical maintenance times. Use this to predict if maintenance will fit in available window.
•Plan for Overrun — If maintenance might exceed window, have a graceful stop point. Don't leave indexes in inconsistent state.
•Coordinate with Backups — Don't run maintenance during backups. Both compete for I/O and may extend backup times.
•Monitor Resource Usage — Track CPU, I/O, and memory during maintenance. Adjust parallelism if impacting other operations.
•Test in Non-Production First — Validate maintenance scripts in lower environments before production deployment.

Adaptive Maintenance

Special Index Maintenance Considerations

Beyond standard B-tree indexes, modern databases support specialized index types that have unique maintenance requirements.

Specialized Index Maintenance
Index Type	Fragmentation Behavior	Maintenance Approach
Columnstore (SQL Server)	Compressed segments; deleted rows in delta stores	REORGANIZE merges delta stores; REBUILD recreates compressed segments
Full-Text	Inverted index with word mappings	REORGANIZE refreshes change tracking; REBUILD recreates catalog
Spatial (R-Tree)	Similar to B-tree but for multi-dimensional data	Rebuild when query performance degrades; no simple fragmentation metric
Filtered	Subset of rows indexed	Same maintenance as regular indexes; verify filter predicate after schema changes
GIN/GiST (PostgreSQL)	Generalized inverted/search tree indexes	REINDEX; check for pending updates in fastupdate lists
BRIN (PostgreSQL)	Block range indexes	Very compact; generally don't need maintenance; REINDEX if data distribution changes

specialized_maintenance.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- Columnstore index maintenance
 
-- Check columnstore fragmentation
SELECT 
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    SUM(rg.deleted_rows) AS total_deleted_rows,
    SUM(rg.total_rows) AS total_rows,
    100.0 * SUM(rg.deleted_rows) / NULLIF(SUM(rg.total_rows), 0) 
        AS delete_ratio_pct,
    COUNT(CASE WHEN rg.state = 1 THEN 1 END) AS open_rowgroups,
    COUNT(CASE WHEN rg.state = 2 THEN 1 END) AS closed_rowgroups,
    COUNT(CASE WHEN rg.state = 3 THEN 1 END) AS compressed_rowgroups
FROM sys.column_store_row_groups rg
JOIN sys.indexes i ON rg.object_id = i.object_id 
    AND rg.index_id = i.index_id
GROUP BY i.object_id, i.name
ORDER BY delete_ratio_pct DESC;
 
-- Columnstore maintenance
-- REORGANIZE: Merges delta stores, compresses open rowgroups
ALTER INDEX CCI_orders ON orders REORGANIZE;
 
-- REORGANIZE with COMPRESS_ALL_ROW_GROUPS
ALTER INDEX CCI_orders ON orders REORGANIZE 
WITH (COMPRESS_ALL_ROW_GROUPS = ON);
 
-- REBUILD: Complete recreation (use when highly fragmented)
ALTER INDEX CCI_orders ON orders REBUILD WITH (ONLINE = ON);
 
-- Full-text index maintenance
-- Check full-text catalog status
SELECT 
    name AS catalog_name,
    is_importing,
    item_count,
    FULLTEXTCATALOGPROPERTY(name, 'PopulateStatus') AS pop_status
FROM sys.fulltext_catalogs;
 
-- Rebuild full-text catalog
ALTER FULLTEXT CATALOG FT_OrdersCatalog REBUILD;
 
-- Reorganize full-text index (process pending changes)
ALTER FULLTEXT INDEX ON orders REORGANIZE;

Summary: Mastering Index Maintenance

Key Takeaways

•Indexes degrade through normal operations — Page splits, deletions, and random insertions cause internal and external fragmentation.
•Measure before maintaining — Use fragmentation analysis queries to assess index health. Don't waste resources on healthy indexes.
•Reorganize for moderate fragmentation — Less resource-intensive, online, interruptible. Best for 10-30% fragmentation.
•Rebuild for heavy fragmentation — Complete reconstruction. More thorough but more expensive. Best for 30%+ fragmentation.
•Online operations enable 24/7 availability — But come with trade-offs: slower, more disk space, higher transaction log usage.
•Automation is essential at scale — Implement intelligent maintenance scripts that assess, prioritize, execute, and log.
•Schedule strategically — Balance maintenance needs against application workload. Coordinate with backups and other operations.

What's next:

Page Complete

2 / 5