概要
-
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’