Run it in the database: the auto-statistics settings, how fresh and how well sampled every statistic is, and filtered statistics that have fallen behind. A fuller version of Performance 05. Read-only, changes nothing. Tested on SQL Server 2017 and 2025.

/*
 10_Statistics_Health.sql - run IN the database: how fresh and how good every statistic is, the database's
 auto-statistics settings, and filtered statistics that have fallen behind.
 Clive Roberts DBA, 10 October 2026. Read-only. SQL Server 2016 or later. (A fuller version of 05_Statistics_Age.sql.)

 HOW TO READ IT
   Result 1 - the database's settings. AUTO_CREATE and AUTO_UPDATE should normally be ON. AUTO_UPDATE_ASYNC ON
              means queries don't wait for a stats update (the next compile gets the new stats) - good for OLTP.
   Result 2 - each statistic, worst first. flags:
     STALE            more rows changed than SQL Server's own auto-update threshold (sqrt(1000 x rows) under
                      compatibility level 130+, or 20% + 500 rows under older levels). It updates on the next query
                      that needs it - unless the table is busy and you'd rather update it in a maintenance window.
     LOW SAMPLE       a big table (over 1M rows) sampled below 5%: lopsided data can be missed. Consider
                      UPDATE STATISTICS ... WITH SAMPLE 25 PERCENT (or FULLSCAN) and PERSIST_SAMPLE_PERCENT = ON.
     NORECOMPUTE      auto-update is switched off for this statistic: it will only change when someone updates it.
     FILTERED BEHIND  a filtered statistic not updated in 7 days although its table has changed: filtered
                      statistics only auto-update on whole-table changes, so they fall behind. Update them on a
                      schedule (Ola's IndexOptimize @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y').
     NEVER UPDATED    no statistics have been built yet (empty table when created, or created WITH NORECOMPUTE).
   persisted_sample_pct  a sample rate that UPDATE STATISTICS keeps using (SQL 2016 SP1 CU4+); NULL = default.
*/
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Result 1: this database's statistics settings
SELECT name AS [database], compatibility_level, is_auto_create_stats_on AS auto_create, is_auto_update_stats_on AS auto_update,
       is_auto_update_stats_async_on AS auto_update_async, is_auto_create_stats_incremental_on AS auto_create_incremental
FROM sys.databases WHERE database_id = DB_ID();

-- Result 2: every statistic on user tables
DECLARE @compat int = (SELECT compatibility_level FROM sys.databases WHERE database_id = DB_ID());
DECLARE @persisted nvarchar(100) =
    CASE WHEN EXISTS (SELECT 1 FROM sys.all_columns WHERE object_id = OBJECT_ID(N'sys.dm_db_stats_properties') AND name = N'persisted_sample_percent')
         THEN N'sp.persisted_sample_percent' ELSE N'CONVERT(float, NULL)' END;
DECLARE @sql nvarchar(max) = N'
SELECT OBJECT_SCHEMA_NAME(s.object_id) + N''.'' + OBJECT_NAME(s.object_id) AS [table], s.name AS [statistic],
       STUFF((SELECT N'', '' + c.name FROM sys.stats_columns AS sc JOIN sys.columns AS c ON c.object_id = sc.object_id AND c.column_id = sc.column_id
              WHERE sc.object_id = s.object_id AND sc.stats_id = s.stats_id ORDER BY sc.stats_column_id FOR XML PATH(''''), TYPE).value(''.'', ''nvarchar(max)''), 1, 2, N'''') AS [columns],
       CASE WHEN s.auto_created = 1 THEN ''auto'' WHEN s.user_created = 1 THEN ''user'' ELSE ''index'' END AS made_by,
       sp.last_updated, DATEDIFF(DAY, sp.last_updated, GETDATE()) AS days_old,
       sp.rows, sp.rows_sampled, CONVERT(decimal(6,2), 100.0 * sp.rows_sampled / NULLIF(sp.rows, 0)) AS sample_pct,
       ' + @persisted + N' AS persisted_sample_pct,
       sp.modification_counter AS rows_changed, CONVERT(decimal(9,1), 100.0 * sp.modification_counter / NULLIF(sp.rows, 0)) AS pct_changed,
       s.has_filter, s.filter_definition, s.no_recompute, s.is_incremental,
       LTRIM(
         CASE WHEN sp.last_updated IS NULL THEN '' NEVER UPDATED'' ELSE '''' END
       + CASE WHEN sp.rows > 0 AND sp.modification_counter > CASE WHEN @compat >= 130 THEN SQRT(1000.0 * sp.rows) ELSE 500 + 0.2 * sp.rows END
              THEN '' STALE'' ELSE '''' END
       + CASE WHEN sp.rows > 1000000 AND 100.0 * sp.rows_sampled / NULLIF(sp.rows, 0) < 5 THEN '' LOW SAMPLE'' ELSE '''' END
       + CASE WHEN s.no_recompute = 1 THEN '' NORECOMPUTE'' ELSE '''' END
       + CASE WHEN s.has_filter = 1 AND sp.last_updated < DATEADD(DAY, -7, GETDATE())
                   AND EXISTS (SELECT 1 FROM sys.dm_db_index_usage_stats AS us WHERE us.database_id = DB_ID() AND us.object_id = s.object_id
                               AND us.last_user_update > sp.last_updated)
              THEN '' FILTERED BEHIND'' ELSE '''' END) AS flags
FROM sys.stats AS s
OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE OBJECTPROPERTY(s.object_id, ''IsUserTable'') = 1 AND OBJECTPROPERTY(s.object_id, ''IsMsShipped'') = 0
ORDER BY CASE WHEN sp.last_updated IS NULL THEN 0 ELSE 1 END, pct_changed DESC, sp.rows DESC;';
EXEC sys.sp_executesql @sql, N'@compat int', @compat;

-- Result 3: summary
SELECT COUNT(*) AS statistics_total,
       SUM(CASE WHEN s.auto_created = 1 THEN 1 ELSE 0 END) AS auto_created,
       SUM(CASE WHEN s.has_filter = 1 THEN 1 ELSE 0 END) AS filtered,
       SUM(CASE WHEN s.no_recompute = 1 THEN 1 ELSE 0 END) AS norecompute,
       SUM(CASE WHEN sp.rows > 0 AND sp.modification_counter > CASE WHEN @compat >= 130 THEN SQRT(1000.0 * sp.rows) ELSE 500 + 0.2 * sp.rows END THEN 1 ELSE 0 END) AS stale,
       MIN(sp.last_updated) AS oldest_update
FROM sys.stats AS s OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1 AND OBJECTPROPERTY(s.object_id, 'IsMsShipped') = 0;