Statistics Maintenance

Automated stale statistics detection and update. Identifies statistics with high modification counts relative to table size and runs targeted UPDATE STATISTICS. Processes databases individually to avoid silent failures. All objects in the dbo schema in DBA_Utilities.

Replaces Ola Hallengren's IndexOptimize for statistics-only maintenance. Instead of updating everything on a schedule, it identifies genuinely stale statistics based on modification rates and table size, then updates only those.

Key proc: dbo.sp_StatisticsMaintenance

How It Works

The proc loops through each database individually and identifies stale statistics based on these criteria:

Condition	Threshold	Rationale
No modifications + old	> 30 days since update	Stats may be outdated even without changes
Has modifications + aging	> 7 days since update	Allow batching rather than constant updates
Large tables with mods	> 5% modification rate	High-row tables need tighter thresholds
Small tables with mods	> 20% modification rate	Small tables tolerate more churn

Exclusions: CDC schema, empty tables, system tables.

Parameters

Parameter	Default	Description
`@DatabaseName`	NULL	NULL = all user databases. Supports comma-delimited list and LIKE wildcards
`@Run`	0	0 = display what would be updated (dry run). 1 = execute the updates
`@StatisticsSample`	NULL	NULL = SQL Server default. 100 = FULLSCAN. 1–99.99 = sample percentage
`@TableName`	NULL	NULL = all tables. Supports CSV with optional schema prefix (`dbo.MyTable`)
`@DetailOutput`	0	0 = minimal output (database progress only). 1 = detailed results per statistic

Logging

Every update is logged to dbo.StatisticsUpdate:

Column	Description
Database, Schema, Table, StatisticName	Identifies the specific statistic updated
LastUpdated	When the stat was last updated before this run
DaysSinceUpdate	How stale the stat was
ModificationCount	Number of row modifications since last update
RowCount, ModificationPct	Table size and modification rate
Outcome	`Success` or `Failure`
Run	Groups updates from a single execution

Log entries older than 30 days are automatically purged.

Usage

Basic

-- Dry run: see what would be updated (all databases)
EXEC dbo.sp_StatisticsMaintenance @Run = 0;

-- Execute updates on all databases
EXEC dbo.sp_StatisticsMaintenance @Run = 1;

-- Single database with verbose output
EXEC dbo.sp_StatisticsMaintenance
    @DatabaseName = 'MyDatabase',
    @Run = 1,
    @DetailOutput = 1;

Full Scan on Specific Table

EXEC dbo.sp_StatisticsMaintenance
    @DatabaseName = 'MyDatabase',
    @TableName = 'dbo.LargeTable',
    @StatisticsSample = 100,
    @Run = 1;

Wildcard Database Filter

EXEC dbo.sp_StatisticsMaintenance
    @DatabaseName = 'MyApp_%',
    @Run = 1;

Check the Log

-- Recent updates
SELECT * FROM dbo.StatisticsUpdate
WHERE UpdateDate > DATEADD(DAY, -1, GETDATE())
ORDER BY UpdateDate DESC;

-- Summary by run
SELECT Run, MIN(UpdateDate) AS RunDate,
       COUNT(*) AS StatsUpdated,
       SUM(CASE WHEN Outcome = 'Success' THEN 1 ELSE 0 END) AS Succeeded,
       SUM(CASE WHEN Outcome = 'Failure' THEN 1 ELSE 0 END) AS Failed
FROM dbo.StatisticsUpdate
GROUP BY Run
ORDER BY Run DESC;