Statistics & Index Maintenance

The optimiser doesn't read your data when it builds a plan — it reads statistics. Stale or missing stats lead to wildly wrong cardinality estimates, which lead to bad plans. Index fragmentation, meanwhile, slows scans and read-ahead.

What Are Statistics?

A statistic is a histogram + density vector summarising the distribution of values in one or more columns. Each index implicitly carries stats on its key columns. SQL Server creates additional column stats automatically when AUTO_CREATE_STATISTICS is on (the default).

-- Database-level setting
ALTER DATABASE HR SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE HR SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE HR SET AUTO_UPDATE_STATISTICS_ASYNC OFF;

Inspecting Statistics

-- All stats on a table
SELECT  s.name              AS stat_name,
        s.auto_created,
        s.user_created,
        sp.last_updated,
        sp.rows,
        sp.rows_sampled,
        sp.modification_counter
FROM    sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE   s.object_id = OBJECT_ID('hr.employees');

-- Histogram and density (legacy but informative)
DBCC SHOW_STATISTICS ('hr.employees', 'IX_employees_dept');

DBCC SHOW_STATISTICS returns three sections:

1. Header — last update time, sample size, rows
2. Density vector — selectivity per leading-column subset
3. Histogram — up to 200 steps describing the value distribution

Updating Statistics

-- Update one specific stat
UPDATE STATISTICS hr.employees IX_employees_dept;

-- Update ALL stats on the table (sampling — fast)
UPDATE STATISTICS hr.employees;

-- Full scan (more accurate, slower — needed on tables with skew)
UPDATE STATISTICS hr.employees WITH FULLSCAN;

-- Sample at a specific percentage
UPDATE STATISTICS hr.employees WITH SAMPLE 25 PERCENT;

-- Specific number of rows
UPDATE STATISTICS hr.employees WITH SAMPLE 100000 ROWS;

The default sampling rate scales with table size — typically a few percent. For tables with strong skew, schedule periodic WITH FULLSCAN jobs.

sp_updatestats is the database-wide variant — sweeps every table, but only refreshes stats whose modification_counter is non-zero:

EXEC sp_updatestats;

Auto Update Threshold

SQL Server auto-updates a stat when modifications cross a threshold. Modern threshold (2016+ with compatibility level 130 or trace flag 2371): roughly SQRT(1000 * rowcount). Older threshold: 20% of rows + 500 — too high for big tables, hence the change.

Index Fragmentation

Fragmentation comes in two flavours:

Inspect with sys.dm_db_index_physical_stats:

SELECT  OBJECT_SCHEMA_NAME(ips.object_id) AS [schema],
        OBJECT_NAME(ips.object_id)        AS [table],
        i.name                            AS index_name,
        ips.index_type_desc,
        ips.avg_fragmentation_in_percent,
        ips.page_count
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN    sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE   ips.page_count > 1000           -- ignore tiny indexes
  AND   ips.avg_fragmentation_in_percent > 10
ORDER BY ips.avg_fragmentation_in_percent DESC;

REORGANIZE vs REBUILD

Industry rule of thumb (originated by Paul Randal):

-- Reorg (online, lower CPU, slower)
ALTER INDEX IX_orders_date ON hr.orders REORGANIZE;

-- Rebuild
ALTER INDEX IX_orders_date ON hr.orders REBUILD;

-- Online rebuild (Enterprise / Azure SQL DB)
ALTER INDEX IX_orders_date ON hr.orders REBUILD WITH (ONLINE = ON);

-- Rebuild ALL indexes on a table
ALTER INDEX ALL ON hr.orders REBUILD;

Fill Factor

Fill factor controls how full each leaf page is filled when an index is built or rebuilt. Lower values leave room for inserts/updates, reducing future page splits — at the cost of larger indexes.

-- Build a fresh index with 80% fill factor (20% headroom)
CREATE INDEX IX_orders_date ON hr.orders (order_date)
    WITH (FILLFACTOR = 80);

-- Apply at rebuild time
ALTER INDEX IX_orders_date ON hr.orders REBUILD WITH (FILLFACTOR = 80);

-- Default for the instance (avoid changing globally — set per index)
EXEC sp_configure 'fill factor', 80;

Use a lower fill factor (70–85) for hot tables with many updates landing in the middle of the index; leave 100 (the default) for read-heavy or append-only tables.

A Simple Maintenance Pattern

Combine stats updates and index maintenance in a weekend job:

DECLARE @sql NVARCHAR(MAX);

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT
        CASE
            WHEN ips.avg_fragmentation_in_percent > 30
                THEN N'ALTER INDEX [' + i.name + '] ON [' +
                     OBJECT_SCHEMA_NAME(i.object_id) + '].[' +
                     OBJECT_NAME(i.object_id) + '] REBUILD;'
            WHEN ips.avg_fragmentation_in_percent > 5
                THEN N'ALTER INDEX [' + i.name + '] ON [' +
                     OBJECT_SCHEMA_NAME(i.object_id) + '].[' +
                     OBJECT_NAME(i.object_id) + '] REORGANIZE;'
        END
    FROM   sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
    JOIN   sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
    WHERE  ips.page_count > 1000
      AND  ips.avg_fragmentation_in_percent > 5
      AND  i.name IS NOT NULL;

OPEN c;
FETCH NEXT FROM c INTO @sql;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @sql;
    EXEC sp_executesql @sql;
    FETCH NEXT FROM c INTO @sql;
END;
CLOSE c;
DEALLOCATE c;

For production environments, use community scripts like Ola Hallengren's Maintenance Solution rather than rolling your own.

Best Practices

• Keep AUTO_UPDATE_STATISTICS on; consider AUTO_UPDATE_STATISTICS_ASYNC for large OLTP systems.
• Schedule a weekly UPDATE STATISTICS with FULLSCAN on top tables; daily on volatile ones.
• Use the 5%/30% fragmentation rule to choose REORGANIZE or REBUILD.
• Always update stats after a REORGANIZE.
• Don't fix fragmentation on small (< 1000 page) indexes — the overhead doesn't pay back.

Summary

• Statistics drive plan quality; auto-update keeps them fresh, but full-scan updates are sometimes needed.
• sys.dm_db_stats_properties reveals how stale stats are; DBCC SHOW_STATISTICS reads the histogram.
• sys.dm_db_index_physical_stats reveals fragmentation; pick REORGANIZE for moderate, REBUILD for heavy.
• FILLFACTOR reserves leaf-page headroom for hot tables.
• Use Ola Hallengren or similar community scripts in production rather than ad-hoc maintenance.