Index Maintenance — Fragmentation, Rebuild and Reorganise

Index fragmentation degrades query performance over time. As rows are inserted, updated, and deleted, SQL Server may split index pages (B-tree page splits) to accommodate new data — leaving pages partially empty and out of logical order. Fragmentation means SQL Server reads more pages than necessary to retrieve the same data. A weekly index maintenance job using REORGANIZE (online, light defrag) or REBUILD (offline or online, full reconstruction) keeps fragmentation below thresholds where it impacts performance.

Index Maintenance #

-- ── Check fragmentation for all indexes on BlogApp ────────────────────────
SELECT
    t.name                 AS TableName,
    i.name                 AS IndexName,
    i.type_desc            AS IndexType,
    s.avg_fragmentation_in_percent AS FragPct,
    s.page_count,
    s.record_count
FROM   sys.dm_db_index_physical_stats(
           DB_ID('BlogApp'), NULL, NULL, NULL, 'LIMITED') s
JOIN   sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
JOIN   sys.tables  t ON t.object_id = i.object_id
WHERE  s.page_count > 100             -- ignore tiny indexes (fragmentation irrelevant)
  AND  i.name IS NOT NULL             -- exclude heaps
ORDER  BY s.avg_fragmentation_in_percent DESC;

-- ── Maintenance thresholds ────────────────────────────────────────────────
-- < 5%:   No action needed
-- 5-30%:  REORGANIZE (online, fast, less resource impact)
-- > 30%:  REBUILD (offline or ONLINE=ON, full reconstruction)

-- ── REORGANIZE — lightweight, always online ────────────────────────────────
ALTER INDEX IX_Posts_IsPublished_PublishedAt ON dbo.Posts REORGANIZE;

-- ── REBUILD — full reconstruction ────────────────────────────────────────
ALTER INDEX IX_Posts_IsPublished_PublishedAt ON dbo.Posts
REBUILD WITH (ONLINE = ON,           -- users can access table during rebuild
              FILLFACTOR = 90,        -- leave 10% free per page for future inserts
              SORT_IN_TEMPDB = ON);   -- use tempdb for sort, not data file

ALTER INDEX ALL ON dbo.Posts REBUILD; -- rebuild all indexes on the table

-- ── Update statistics after major data changes ────────────────────────────
UPDATE STATISTICS dbo.Posts WITH FULLSCAN;   -- full scan (most accurate)
UPDATE STATISTICS dbo.Posts;                  -- sampled (faster, less accurate)

-- ── Automated maintenance script ─────────────────────────────────────────
CREATE OR ALTER PROCEDURE dbo.usp_IndexMaintenance
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX) = '';

    SELECT @SQL += CASE
        WHEN s.avg_fragmentation_in_percent BETWEEN 5 AND 30
            THEN 'ALTER INDEX ' + QUOTENAME(i.name) +
                 ' ON ' + QUOTENAME(t.name) + ' REORGANIZE;' + CHAR(10)
        WHEN s.avg_fragmentation_in_percent > 30
            THEN 'ALTER INDEX ' + QUOTENAME(i.name) +
                 ' ON ' + QUOTENAME(t.name) +
                 ' REBUILD WITH (ONLINE=ON, FILLFACTOR=90);' + CHAR(10)
        ELSE ''
    END
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') s
    JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
    JOIN sys.tables  t ON t.object_id = i.object_id
    WHERE s.page_count > 100 AND i.name IS NOT NULL
      AND s.avg_fragmentation_in_percent >= 5;

    IF LEN(@SQL) > 0
        EXEC sp_executesql @SQL;
END;

Common Mistakes #

Mistake 1 — Rebuilding small indexes unnecessarily (overhead without benefit) #

❌ Wrong — rebuilding all indexes including tiny 5-page tables; fragmentation on small tables has no performance impact.

✅ Correct — filter by page_count > 100 (or 1000 for high-volume systems) before applying maintenance.

Mistake 2 — REBUILD without ONLINE=ON on 24/7 tables (table locked during rebuild) #

❌ Wrong — ALTER INDEX IX ON Table REBUILD without ONLINE=ON; table inaccessible for minutes or hours.

✅ Correct — REBUILD WITH (ONLINE = ON) keeps the table accessible; requires Enterprise Edition.