SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

統計情報のサンプリングの実行プラン

leave a comment

統計情報の更新は 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 行
  • フルスキャン

を対象として統計情報を更新しています。

# 各統計情報の更新の前にはバッファキャッシュをクリアしています。

この際の処理時間は以下のようになっています。

image


それでは、各サンプリングの処理時間の差がなぜ起きているのかを統計情報更新時の実行プランから確認してみたいと思います。

通常の実行プランの取得では、UPDATE STATISTICS の実行プランを取得することができません。

そこで、拡張イベントの [query_post_execution_showplan] を使用してプランを取得してみます。

このイベントからであれば統計情報更新の実行プランを取得することができます。

取得されたプランが以下になります。

既定のサンプリング

image

10%

image

100,000 行

image

フルスキャン

image

サンプリングの設定に応じて、クラスター化インデックスから次処理に流れているデータの量が異なっているのが確認できますね。既定のサンプリングについては件数を指定できませんが、10% / 100,000 行については指定した設定のデータが流れていることが確認できます。

フルスキャンの場合はソート等が行われずにそのまま全件が使用されていることが確認できます。

非クラスター化インデックスが設定されている場合は、フルスキャンのほうが早い場合もあるようです。

この辺の調査をするためにの方法として、拡張イベントから統計情報の作成がどのようなプランで行われているかを知っているとよさそうですね。

統計情報がどの程度のデータのサンプリングを元に作成されているかは、DBCC SHOW_STATISTICS / sys.dm_db_stats_properties (SQL Server 2012 以降) で確認することができますので、統計情報の調査としてはこれらの情報も抑えておくとよいかと。

Written by masayuki.ozawa

11月 15th, 2014 at 10:59 am

Posted in SQL Server

Tagged with

Leave a Reply

*