SQL Server 2012/2008 R2 新機能: 列変更カウンタ (colmodctr)
神谷 雅紀
SQL Server Escalation Engineer
SQL Server 7.0 と 2000 では、統計情報の自動更新が行われるタイミングは、前回の統計更新以降に変更された行数に基づいて決定され、更新された行数は、 sysindexes システムテーブルの rowmodctr により知ることができました。SQL Server 2005 以降では、統計情報の自動更新は、Microsoft SQL Server 2005 のクエリ オプティマイザが使用する統計情報やSQL Server 2005 のバッチのコンパイル、再コンパイル、およびプランのキャッシュに関する問題で述べられているように、行の変更数ではなく列の変更数に基づいて行われるようになりました。しかし、SQL Server 2005 以降には、sysindexes.rowmodctr に相当するような、変更数を確認することのできる正式な機能はありませんでした。
SQL Server 2012 Service Pack 1 および SQL Server 2008 R2 Service Pack 2 で、その変更数を確認することのできる機能を持った DMF が追加されました。 sys.dm_db_stats_properties の modification_counter 列がそれです。
sys.dm_db_stats_properties (Transact-SQL)
https://msdn.microsoft.com/ja-jp/library/jj553546.aspx
関連情報
※ トレースフラグ 2371 自体は KB や Blog として公開されている情報ですが、一般的にはあまり知られていないようなので、関連情報としてここに記載します。
SQL Server 2012 と SQL Server 2008 R2 SP1 以降では、統計情報の自動更新を行うかどうかのしきい値の計算方法として、Microsoft SQL Server 2005 のクエリ オプティマイザが使用する統計情報で述べられている方法とは異なる新たな方法が導入されています。この新しい方法は、トレースフラグ 2371 が設定されている場合にのみ有効となります。
この新しいしきい値の計算方法は、大量の行を含む大きなテーブルのある環境で使用されることを想定しています。トレースフラグ 2371 により有効となる新しいしきい値計算が使用されない場合は、テーブル全体の約 20% が更新された場合に、統計更新の自動更新が行われます。一方、トレースフラグ 2371 により新しいしきい値計算が有効になると、テーブル内の行数が多くなるほどパーセンテージが低くなり、例えば、100,000 行が格納されているテーブルではしきい値は約 10%、1,000,000 行では約 3.2% となります。
大量の行を含む大きなテーブルで、普段はテーブル全体の 20% 未満の更新しか行われないために統計情報の自動更新が実行されないようなテーブルであっても、トレースフラグ 2371 を設定することで、自動更新が行われる頻度は高くなり、統計情報をより新しい状態に維持できるようになります。
Changes to automatic update statistics in SQL Server – traceflag 2371
Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server