DBAtlas · Database operations atlas

Statistics health: when updated, how many rows changed

SQL ServerMaintenance

Command

USE [Musteri];
SELECT OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
       OBJECT_NAME(s.object_id)        AS table_name,
       s.name                          AS stats_name,
       s.auto_created, s.has_filter, s.no_recompute,
       sp.last_updated,
       sp.rows, sp.rows_sampled,
       CAST(100.0 * sp.rows_sampled / NULLIF(sp.rows, 0) AS DECIMAL(5,1))         AS sample_pct,
       sp.modification_counter,
       CAST(100.0 * sp.modification_counter / NULLIF(sp.rows, 0) AS DECIMAL(9,1)) AS modified_pct,
       CAST(CASE WHEN 500 + 0.20 * sp.rows < SQRT(1000.0 * sp.rows) THEN 500 + 0.20 * sp.rows
                 ELSE SQRT(1000.0 * sp.rows) END AS BIGINT)                       AS auto_update_threshold,
       sp.persisted_sample_percent
FROM   sys.stats s
  CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE  OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY sp.modification_counter DESC;

SELECT name, is_auto_create_stats_on, is_auto_update_stats_on, is_auto_update_stats_async_on
FROM   sys.databases
WHERE  name = DB_NAME();

What it does

Shows for each statistic the last update time, sampling rate, rows modified since the last update and the threshold at which auto update fires (dynamic threshold for compat 130+).

When to use it

For plans with a large gap between estimated and actual rows, after a large data load, or when checking whether the statistics maintenance job is effective.

Caution

The threshold formula applies to compat level 130+ (MIN(500 + 20%, SQRT(1000 x rows))); lower compat levels use the 20% + 500 rule. Statistics never updated (empty tables) return NULL. Skewed columns with low sample_pct may need FULLSCAN or PERSIST_SAMPLE_PERCENT. Read-only query; safe.

Open in DBAtlas →

Related