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 never updated at all, 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
• Statistics that are updated automatically only trigger on a threshold of row changes, calculated as SQRT(1000 × rows) on modern compatibility levels; large tables can go a long time between automatic refreshes
• 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    : VIEW DATABASE STATE
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT 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.

WITH 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,
        sp.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
    WHERE ISNULL(sp.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 (never updated, past the dynamic staleness threshold, thinly sampled on a large table, or aging 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

• health_status = NEVER_UPDATED — highest priority. The optimizer has never had real data distribution to work from for this statistic.
• health_status = STALE_THRESHOLD_MET — the modification counter has crossed the dynamic threshold (SQRT(1000 × rows)). The optimizer may be working from meaningfully outdated distribution data.
• health_status = LOW_SAMPLE_RATE — sampled below 10% on a table over 10,000 rows. The sample may not represent the true distribution, especially on skewed data.
• health_status = APPROACHING_STALE — modification percentage over 10% but not yet past the dynamic threshold. Worth watching, not yet urgent.
• health_status = AGED — has pending modifications and hasn’t been updated in over 30 days. Low modification volume but old.
• update_statement — a ready-to-run UPDATE STATISTICS ... WITH FULLSCAN command for that exact statistic.

---

How to Fix Statistics Health

Run the update_statement column’s commands for anything at NEVER_UPDATED or 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, review the SQL Server Agent maintenance job schedule (sql/maintenance/Generate-IndexMaintenanceScript.sql can generate one) rather than fixing it manually every time.

---

Best Practices

• Include a regular statistics maintenance job as part of routine index maintenance, not a separate, forgotten task.
• 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.
• Re-run this script after any statistics maintenance to confirm the fix actually landed.

---

Related Scripts

You may also find these scripts useful:

• Query and Performance Tuning (hub)
• Implicit Conversions
• Query Performance Deep-Dive
• Top CPU Queries / Top CPU, I/O, and Duration Queries
• Fix “Msg 207: Invalid Column Name”
• DBA Scripts: The Complete Guide, the map across every script on this site

---

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.