SQL Server 統計情報更新の動作とSQLまとめ

概要

• SQL Serverではデフォルトで自動統計情報更新が有効で、色々条件はあるがざっくり20%以上のデータ変動があったら自動で更新される。
  http://tech.kou.asia/?p=1672

• この場合、件数が増えると20%に達するまでに時間がかかる。
  それを回避するためにトレースフラグ2371をオンにすることで動的閾値を利用した統計更新を実現できる。
  http://azwoo.hatenablog.com/entry/2013/02/14/125848

• 商用環境では想定外の動きになるのを防止するため自動更新機能は利用せず自前で統計情報更新の処理を実装する事を検討。
  https://docs.microsoft.com/en-us/archive/blogs/jpsql/on-12

• デフォルトでは統計情報の自動更新はInsert/Update文の一部として実行されるが、"AUTO_UPDATE_STATISTICS_ASYNC"をONにする事により非同期に実行する事ができる。
  https://docs.microsoft.com/en-us/archive/blogs/jpsql/on-12
  https://fyts.hatenadiary.org/entry/20081112/async

SQL

統計情報の自動作成

設定状況確認

select
  name 
  ,is_auto_create_stats_on
  ,is_auto_create_stats_incremental_on
  ,is_auto_update_stats_on
  ,is_auto_update_stats_async_on
from sys.databases order by name

列名	データ型	説明
is_auto_create_stats_on	bit	1 = AUTO_CREATE_STATISTICS は ON です。
is_auto_create_stats_incremental_on	bit	自動統計の増分オプションの既定の設定を示します。 0 = 自動作成の統計は非増分です。 1 = 可能な場合は、自動作成の統計情報は増分されます。 適用対象: SQL Server 2014 (12.x) 以降。
is_auto_update_stats_on	bit	1 = AUTO_UPDATE_STATISTICS は ON です。
is_auto_update_stats_async_on	bit	1 = AUTO_UPDATE_STATISTICS_ASYNC は ON です。

設定変更

ALTER DATABASE データベース名 SET AUTO_CREATE_STATISTICS ON|OFF
ALTER DATABASE データベース名 SET AUTO_UPDATE_STATISTICS_ASYNC ON|OFF

トレースフラグ2371

• 確認
  DBCC TRACESTATUS(2371)

• 有効化
  DBCC TRACEON(2371)

• 無効化
  DBCC TRACEOFF(2371)

統計情報の操作

更新の実行

EXEC sp_updatestats

オブジェクトの統計情報更新状況確認

SELECT
    so.name
    , ss.name
    , ss.auto_created
    , ss.user_created
    , ss.no_recompute
    , STATS_DATE(ss.object_id, ss.stats_id)
FROM
    sys.objects AS so
    LEFT JOIN
        sys.stats AS ss
    ON
        ss.object_id = so.object_id
WHERE
    type = ‘U’
ORDER BY
    so.name

最終更新日時・更新回数の確認

SELECT
    OBJECT_NAME(dsp.object_id) 
    , dsp.object_id 
    , ss.name
    , dsp.last_updated 
    , dsp.rows
    , dsp.rows_sampled 
    , dsp.steps 
    , dsp.unfiltered_rows 
    , dsp.modification_counter 
FROM
    sys.stats AS ss 
    CROSS APPLY 
    sys.dm_db_stats_properties(ss.object_id, ss.stats_id) AS dsp 
WHERE
    OBJECT_SCHEMA_NAME(dsp.object_id, DB_ID()) <> ‘sys’