SQL to Check Statistics Last-Updated Time per Table in SQL Server

この文書の適用条件

そのまま実行できるコマンド

-- 対象: SQL Server 2008 R2 SP2 / 2012 SP1 以降
-- 権限: 対象DBへの接続権限（sys.stats はメタデータ可視性ルールに従う）
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    SCHEMA_NAME(o.schema_id)              AS schema_name,
    o.name                                AS table_name,
    s.name                                AS stats_name,
    s.auto_created                        AS is_auto_created,
    STATS_DATE(s.object_id, s.stats_id)   AS last_updated,
    sp.rows                               AS table_rows,
    sp.rows_sampled                       AS rows_sampled,
    sp.modification_counter               AS rows_modified_since_update
FROM sys.stats AS s
INNER JOIN sys.objects AS o
        ON s.object_id = o.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE o.is_ms_shipped = 0          -- システムオブジェクトを除外
  AND o.type IN ('U', 'V')         -- ユーザーテーブルとビュー
ORDER BY sp.modification_counter DESC, last_updated ASC;

-- 対象: SQL Server 2008（sys.dm_db_stats_properties が使えない環境）
-- 権限: 対象DBへの接続権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    SCHEMA_NAME(o.schema_id)            AS schema_name,
    o.name                              AS table_name,
    s.name                              AS stats_name,
    s.auto_created                      AS is_auto_created,
    s.no_recompute                      AS is_no_recompute,
    STATS_DATE(s.object_id, s.stats_id) AS last_updated
FROM sys.stats AS s
INNER JOIN sys.objects AS o
        ON s.object_id = o.object_id
WHERE o.is_ms_shipped = 0
  AND o.type IN ('U', 'V')
ORDER BY last_updated ASC;

-- 対象: SQL Server 2008 以降
-- 権限: 対象テーブルの所有者 または db_owner / db_ddladmin
-- 変更作業: あり（統計更新 → 該当オブジェクトのプラン再コンパイル）
-- Production 実行: 可能だが I/O とCPUを消費するため時間帯を選ぶこと
UPDATE STATISTICS [dbo].[SampleTable] ([IX_SampleTable_01])
    WITH FULLSCAN;

結果の読み方

こういう状況で使います

• The same query suddenly started taking longer from a certain day
• The estimated row count in the execution plan differs greatly from the actual row count
• Only a specific process has been slow right after a large data load or deletion
• Statistics are left to auto-update, but it is unclear when they were actually last updated

考えられる原因（可能性の高い順）

1. 01

   The auto-update threshold has not been reached

   AUTO_UPDATE_STATISTICS updates an object the next time it is referenced after the number of modified rows exceeds the threshold. Tables with more rows take longer to reach the threshold, so changes can accumulate while execution plans are still built from outdated statistics.

2. 02

   Sampling rate is low and the distribution no longer matches reality

   Default sampling does not read every row. For columns with highly skewed values, the row-count estimate derived from sampling can diverge from the actual data.

3. 03

   Automatic statistics updates are disabled

   If the AUTO_UPDATE_STATISTICS database option is OFF, or NO_RECOMPUTE is set on individual statistics, they remain stale until explicitly updated.

4. 04

   A scheduled maintenance job is failing

   If an index rebuild or statistics update job stops due to an error, the last-updated time stays frozen at the date the job stopped.

確認手順

1. 1

   List the last-updated time of statistics

   参照のみ

   Run the read-only SQL above to find statistics where `last_updated` is old or `rows_modified_since_update` is large.

2. 2

   Check the database options

   参照のみ

   Run `SELECT name, is_auto_update_stats_on, is_auto_create_stats_on, is_auto_update_stats_async_on FROM sys.databases;` to check the auto-update settings.

3. 3

   Find statistics with NO_RECOMPUTE set

   参照のみ

   Run `SELECT name FROM sys.stats WHERE no_recompute = 1;` to find statistics excluded from auto-update.

4. 4

   Compare estimated vs. actual row counts in the query execution plan

   低

   Capture the actual execution plan and identify operators where the estimated and actual row counts differ significantly. If the difference is small, statistics are not the cause.

対応方法

バージョン・環境による違い

これで解決しない場合に確認すること

• Have you compared estimated vs. actual row counts in the execution plan?

  Check whether any operator shows a 10x or greater difference. If there is no such difference, suspect something other than statistics — index design, parameter sniffing, or wait statistics.

• Have you checked wait statistics?

  The remediation differs depending on whether the wait is CPU, I/O, or lock related. If CXPACKET dominates, move on to checking the degree of parallelism.

• Have you checked for parameter sniffing effects?

  If the same query's execution time varies greatly depending on the parameter value, the issue may be plan reuse rather than statistics.

• Have you checked the execution history of maintenance jobs?

  Check `msdb.dbo.sysjobhistory` to confirm the statistics-update job has not been failing.

この文書の根拠と限界

よくある質問

この文書がカバーする質問

• I want to check the last-updated time of statistics in SQL Server
• I want to know how to use STATS_DATE
• I want a list of when statistics were last updated per table

リスク表示の意味

• 参照のみデータと設定を変更しません。
• 低影響は限定的ですが、権限と負荷の確認が必要です。
• 中性能・ロック・コストに影響する可能性があります。
• 高障害・データ損失・復旧作業が発生する可能性があります。
• 専門家レビュー必須本番適用前に別途レビューが必須です。

GIIPの対応範囲

Running this check once is not difficult. But continuously checking the status across multiple SQL Server or Aurora MySQL environments, and responding the moment signs of degradation appear, requires an operational framework. At GIIP, AI agents and human experts continuously monitor multiple databases on AWS and Azure along with roughly 30 web services. Items like statistics freshness — easy to check once but hard to keep watching — are built into routine monitoring, with a human judgment call triggered only when something changes.

関連サービス

• データベース性能チューニング
• 24時間監視の対象を確認する