統計情報の更新は UPDATE STATISTICS で実行することができますが、更新の際にはサンプリングの数を設定することができます。
このサンプリングの設定と統計情報更新時の実行プランについて、まとめてみたいと思います。
以下のようなクエリで 4,000,000 件格納されているテーブルの統計情報の更新を行ってみます。
# クラスター化インデックスのみが設定されているテーブルに対して実行しています。非クラスター化インデックスが設定されているテーブルの場合は、本投稿の動作とは多少異なってきます。
SET NOCOUNT ON GO DBCC DROPCLEANBUFFERS DECLARE @date datetime = (SELECT GETDATE()) UPDATE STATISTICS StatsTest(PK_StatsTest) SELECT DATEDIFF(ms, @date, GETDATE()) AS [Default] GO DBCC DROPCLEANBUFFERS DECLARE @date datetime = (SELECT GETDATE()) UPDATE STATISTICS StatsTest(PK_StatsTest) WITH SAMPLE 10 PERCENT SELECT DATEDIFF(ms, @date, GETDATE()) AS [10 Percent] GO DBCC DROPCLEANBUFFERS DECLARE @date datetime = (SELECT GETDATE()) UPDATE STATISTICS StatsTest(PK_StatsTest) WITH SAMPLE 100000 ROWS SELECT DATEDIFF(ms, @date, GETDATE()) AS [100000 ROWS] GO DBCC DROPCLEANBUFFERS DECLARE @date datetime = (SELECT GETDATE()) UPDATE STATISTICS StatsTest(PK_StatsTest) WITH FULLSCAN SELECT DATEDIFF(ms, @date, GETDATE()) AS [Full Scan] GO
上から、
- 既定のサンプリング
- 10%
- 10,000 行
- フルスキャン
を対象として統計情報を更新しています。
# 各統計情報の更新の前にはバッファキャッシュをクリアしています。
それでは、各サンプリングの処理時間の差がなぜ起きているのかを統計情報更新時の実行プランから確認してみたいと思います。
通常の実行プランの取得では、UPDATE STATISTICS の実行プランを取得することができません。
そこで、拡張イベントの [query_post_execution_showplan] を使用してプランを取得してみます。
このイベントからであれば統計情報更新の実行プランを取得することができます。
取得されたプランが以下になります。
既定のサンプリング
10%
100,000 行
フルスキャン
サンプリングの設定に応じて、クラスター化インデックスから次処理に流れているデータの量が異なっているのが確認できますね。既定のサンプリングについては件数を指定できませんが、10% / 100,000 行については指定した設定のデータが流れていることが確認できます。
フルスキャンの場合はソート等が行われずにそのまま全件が使用されていることが確認できます。
非クラスター化インデックスが設定されている場合は、フルスキャンのほうが早い場合もあるようです。
この辺の調査をするためにの方法として、拡張イベントから統計情報の作成がどのようなプランで行われているかを知っているとよさそうですね。
統計情報がどの程度のデータのサンプリングを元に作成されているかは、DBCC SHOW_STATISTICS / sys.dm_db_stats_properties (SQL Server 2012 以降) で確認することができますので、統計情報の調査としてはこれらの情報も抑えておくとよいかと。