DBA Scripts: Get Statistics Health

Query plans are only as good as the statistics SQL Server uses to build them. When a table’s data distribution changes but its statistics don’t get refreshed to match, the optimizer keeps making decisions based on a picture of the data that’s no longer true, choosing a scan when a seek would be faster, or picking the wrong join order entirely. Stale statistics are one of the most common causes of a query that “used to be fast” and quietly isn’t anymore.

This script identifies statistics that are stale, sampled too thinly, or ageing with changes pending, ranked by severity, and hands back the exact UPDATE STATISTICS command to fix each one.

Why Statistics Health Matters

SQL Server’s query optimizer relies on statistics (a compact summary of data distribution) to estimate row counts and choose execution plans. When those estimates are wrong, everything downstream can be wrong too:

• Bad cardinality estimates lead to bad plan choices: wrong join type, wrong index, wrong memory grant
• Automatic updates only trigger on a threshold of row changes. On compatibility level 130 and later that is the lower of 500 + 20% of rows and SQRT(1000 × rows), so large tables still go a long time between refreshes, and the update itself waits for the next query that compiles against the statistic
• A statistic sampled at a low percentage on a large table may not represent the real data distribution accurately
• Never-updated statistics on tables that have grown significantly are a common, quiet source of bad plans

When to Run This Script

• Routine SQL Server health checks
• Investigating a query that regressed without any code change
• Before and after a large data load, migration, or bulk delete
• Reviewing a database you’ve just inherited

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-StatisticsHealth
Category    : performance
Purpose     : Identifies stale, low-sample, and never-updated statistics in the current database.
              Returns the UPDATE STATISTICS command per row for direct copy-paste remediation.
              Run in the context of the target user database.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-statistics-health/)
Requires    : SELECT on the tables, or db_owner / db_ddladmin. sys.dm_db_stats_properties returns
              nothing for a table you cannot read, and VIEW DATABASE STATE alone does not unlock it.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
-- SCOPE:CurrentDatabase
-- Fixes : the update_statement column contains the ready-to-run UPDATE STATISTICS command

DECLARE @show_all BIT = 0; -- 0 = stale / unhealthy only | 1 = all statistics
DECLARE @min_rows INT = 100; -- skip tables below this row count (reduces noise from tiny tables)
DECLARE @stale_days INT = 30; -- AGED threshold: flag stats not updated in this many days when
                                -- modification_counter > 0 (stats exist but are being ignored)

-- Dynamic update threshold (SQL 2016+ compat 130+): SQRT(1000 * rows)
-- Legacy threshold was 20% of row count; dynamic is more conservative on large tables.
-- Row counts come from sys.partitions: a statistic that was never built (created on an empty
-- table) has NULL rows in sys.dm_db_stats_properties and would otherwise fail the @min_rows filter.

WITH table_rows AS (
    SELECT p.object_id, SUM(p.rows) AS table_rows
    FROM sys.partitions p
    WHERE p.index_id IN (0, 1)
    GROUP BY p.object_id
),
stats_health AS (
    SELECT
        OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
        OBJECT_NAME(s.object_id) AS table_name,
        s.name AS stat_name,
        c.name AS leading_column,
        CASE
            WHEN s.auto_created = 0 AND s.user_created = 0 THEN 1
            ELSE 0
        END AS is_index_stat,
        s.auto_created,
        s.has_filter AS is_filtered,
        s.filter_definition,
        s.is_incremental,
        COALESCE(sp.rows, tr.table_rows) AS rows,
        sp.rows_sampled,
        CAST(sp.rows_sampled * 100.0
             / NULLIF(sp.rows, 0) AS DECIMAL(5,1)) AS sample_pct,
        sp.modification_counter,
        CAST(sp.modification_counter * 100.0
             / NULLIF(sp.rows, 0) AS DECIMAL(5,1)) AS modification_pct,
        CAST(SQRT(1000.0 * NULLIF(sp.rows, 0)) AS BIGINT) AS dynamic_update_threshold,
        sp.last_updated,
        DATEDIFF(DAY, sp.last_updated, GETDATE()) AS days_since_update,
        CASE
            WHEN sp.last_updated IS NULL
                THEN 'NEVER_UPDATED'
            WHEN sp.modification_counter >= SQRT(1000.0 * NULLIF(sp.rows, 0))
                THEN 'STALE_THRESHOLD_MET'
            WHEN sp.rows > 10000
             AND sp.rows_sampled * 100.0 / NULLIF(sp.rows, 0) < 10
                THEN 'LOW_SAMPLE_RATE'
            WHEN sp.modification_counter * 100.0 / NULLIF(sp.rows, 0) > 10
                THEN 'APPROACHING_STALE'
            WHEN sp.modification_counter > 0
             AND DATEDIFF(DAY, sp.last_updated, GETDATE()) > @stale_days
                THEN 'AGED'
            ELSE 'OK'
        END AS health_status,
        'UPDATE STATISTICS ['
            + OBJECT_SCHEMA_NAME(s.object_id) + '].['
            + OBJECT_NAME(s.object_id) + '] ['
            + s.name + '] WITH FULLSCAN;' AS update_statement
    FROM sys.stats s
    OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
    JOIN sys.objects o
        ON s.object_id = o.object_id
        AND o.type = 'U'
    LEFT JOIN sys.stats_columns sc
        ON sc.object_id = s.object_id
        AND sc.stats_id = s.stats_id
        AND sc.stats_column_id = 1
    LEFT JOIN sys.columns c
        ON c.object_id = sc.object_id
        AND c.column_id = sc.column_id
    LEFT JOIN table_rows tr
        ON tr.object_id = s.object_id
    WHERE COALESCE(sp.rows, tr.table_rows, 0) >= @min_rows
)
SELECT
    schema_name,
    table_name,
    stat_name,
    leading_column,
    is_index_stat,
    auto_created,
    is_filtered,
    filter_definition,
    is_incremental,
    rows,
    rows_sampled,
    sample_pct,
    modification_counter,
    modification_pct,
    dynamic_update_threshold,
    last_updated,
    days_since_update,
    health_status,
    update_statement
FROM stats_health
WHERE @show_all = 1
   OR health_status <> 'OK'
ORDER BY
    CASE health_status
        WHEN 'NEVER_UPDATED' THEN 1
        WHEN 'STALE_THRESHOLD_MET' THEN 2
        WHEN 'LOW_SAMPLE_RATE' THEN 3
        WHEN 'APPROACHING_STALE' THEN 4
        WHEN 'AGED' THEN 5
        ELSE 6
    END,
    modification_counter DESC,
    schema_name,
    table_name;

The script evaluates every statistic in the current database against four unhealthy patterns (past the dynamic staleness threshold, thinly sampled on a large table, more than 10% modified, or ageing with pending modifications) and returns only the ones that need attention, each with a ready-to-run UPDATE STATISTICS command.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Check statistics health in a specific database:
.\run.ps1 Get-StatisticsHealth -Database YourDatabaseName

# To run against a remote sql server:
.\run.ps1 Get-StatisticsHealth -ServerInstance SQLSERVER01 -Database YourDatabaseName

This script lives in the repo at:

• sql/performance/queries/Get-StatisticsHealth.sql
• powershell/wrappers/performance/queries/Get-StatisticsHealth.ps1

Example Output

Run against a single database (-Database RandomLab in this example): at 14.2% of rows modified since the last update on IX_IndexDemo_Status_Unused, this statistic hasn’t crossed the dynamic staleness threshold yet, but it’s heading there, exactly what APPROACHING_STALE is meant to catch before it becomes a problem.

Understanding the Results

How to Fix Statistics Health

Run the update_statement column’s commands for anything at STALE_THRESHOLD_MET first; those are the highest-impact fixes. WITH FULLSCAN gives the most accurate statistics but takes longer and uses more resources than a sampled update, better suited to a maintenance window on large tables.

-- Example: update a single statistic with a full scan
UPDATE STATISTICS [dbo].[IndexDemo] [IX_IndexDemo_Status_Unused] WITH FULLSCAN;

-- Or update all statistics on a table (sampled, faster)
UPDATE STATISTICS [dbo].[IndexDemo];

If stale statistics are a recurring problem rather than a one-off, schedule it rather than fixing it by hand every time: Generate Index Maintenance Jobs writes a DBA - Statistics Update Agent job that runs sp_updatestats on every online user database.

Best Practices

• Include a regular statistics maintenance job as part of routine index maintenance, not a separate, forgotten task. An index rebuild already refreshes that index’s statistics with a full scan; a reorganize does not, and neither touches column statistics.
• After a large bulk load or delete, update statistics manually rather than waiting for the automatic threshold to trigger.
• Consider AUTO_UPDATE_STATISTICS_ASYNC for busy OLTP systems where synchronous statistics updates cause noticeable query stalls, which show up as WAIT_ON_SYNC_STATISTICS_REFRESH.
• Re-run this script after any statistics maintenance to confirm the fix actually landed.

Microsoft Learn: sys.stats · sys.dm_db_stats_properties

Frequently Asked Questions

Summary

Stale statistics are an easy thing to overlook because they don’t throw an error, they just quietly make the optimizer’s job harder. A query that regressed with no code change is one of the first things worth checking statistics health for.

Run this script as part of routine health checks, and always after a large data load or bulk delete, since those are exactly the events that push statistics out of date fastest.

Related