SE の雑記

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

統計情報を更新してリコンパイルを誘発させるテストを実施する場合の注意点

leave a comment

SQL Server ではクエリのリコンパイルが発生する条件としては次のようなものがあります。

image

パラメータースニッフィング等で、実行プランが大多数のクエリで効率が悪くなってしまった場合の補正として、「統計情報を更新」することでリコンパイルを誘発させることがあるのではないでしょうか。

実稼働環境であれば、統計情報の更新を行うことでリコンパイルを誘発させるということを実施するのは、それほど難しくない気がしますが、負荷がかかっていない検証環境のようなものを使用して、検証を実施しようとすると、統計情報を更新してもリコンパイルが発生しないとう状態になることがあります。

これについては、2018 年の段階で Does Updating Statistics Cause a Recompile if No Data Has Changed? で解説が行われていますが、投稿を書いている時点の最新のバージョンである、SQL Server 2019 CU15 でも同様な状態になるのかと思って確認していた際の内容を遺しておきたいと思います。

実行プランで使用されている統計情報を確認する

SQL Server 2017 以降で、データベース互換性レベル 120 (SQL Server 2014) 以降を使用している場合は、実行プランに「OptimizerStatsUsage」が追加されており、実行プランを生成する際にどのような統計情報が使用されたのかを確認することができます。

上記のバージョン / 互換性レベルとなっている場合、実行プランには次のような情報が出力されます。image

この情報を確認することで、該当の実行プランをリコンパイルさせるためには、どのような統計情報を更新すればよいのかを判断することができます。

 

単純に検証をしようとするとどうなるか?

上記の情報からどのような統計情報が使用されているかを確認することができました。

それでは、次のようなクエリを実行すると、リコンパイルは発生するでしょうか?

UPDATE STATISTICS PART
UPDATE STATISTICS NATION 
UPDATE STATISTICS PARTSUPP
UPDATE STATISTICS REGION
UPDATE STATISTICS SUPPLIER
GO

EXEC Q2

 

Q2 というストアドプロシージャで使用されているテーブルの統計情報をすべて更新しています。

統計情報の更新状況等は、次のクエリで確認をすることができます。


select OBJECT_NAME(s.object_id), STATS_DATE(s.object_id, s.stats_id),name, modification_counter, * from sys.stats  AS s
outer apply sys.dm_db_stats_properties(object_id,  stats_id)
where s.object_id IN(OBJECT_ID('PART'), OBJECT_ID('NATION'), OBJECT_ID('PARTSUPP'), OBJECT_ID('REGION'),OBJECT_ID('SUPPLIER'))

 

以下は、実行結果の一部を抜粋したものとなりますが、統計情報が一通り更新され同一の時間軸になっていることが確認できます。

image

クエリの実行~リコンパイルまでの情報を取得するために、次のような設定の拡張イベントを作成し、Q2 というストアドプロシージャの動作を確認してみます。

image

この拡張イベントで取得された情報が次の内容となるのですが、クエリリコンパイルのイベントである「sql_statement_recompile」が発生していないことが確認できます。

image

リコンパイルの発生する理由として「Statistcis changed」があり、統計情報の更新はリコンパイル理由の一つとなるはずなのですが、なぜ今回の検証ではリコンパイルが発生しなかったのでしょうか?

これが本投稿の主題となります。

 

変更が発生していない状態で統計情報を更新してもリコンパイルは発生しない

公式ドキュメントで明確化されているもの見つからず、本投稿の冒頭で紹介した記事でも同様の内容となっているのですが、ベースとなるデータを何も変更せずに、統計情報を更新してもリコンパイルは発生しないようです。

このような動作となっているのは、データの変更がない状態で統計情報が変更された場合に、リコンパイルを実施すると不要なオーバーヘッドが発生するからでしょうかね。

 

先ほどのクエリで検証を行おうとした場合は、次のようなクエリで実行すると統計情報更新に起因したリコンパイルを発生させることができます。

BEGIN TRAN
DELETE TOP (1) PERCENT FROM NATION
ROLLBACK TRAN
GO

UPDATE STATISTICS PART
UPDATE STATISTICS NATION 
UPDATE STATISTICS PARTSUPP
UPDATE STATISTICS REGION
UPDATE STATISTICS SUPPLIER
GO

EXEC Q2

 

拡張イベントに「sql_statement_recompile」が出力されており、「recompile_cause」は「Statistics changed」となっていますね。

image

統計情報の更新を行う場合に NATION というテーブルの削除をロールバックする形で実行しています。

統計情報に影響する列変更の状態の確認には、sys.dm_db_stats_properties の modification_counter を使用することができます。

image

INSERT / DELETE のような処理については、該当のテーブルのすべての列に対して影響を与える操作となりますので、テーブル内に作成されている一通りの統計情報に変更を与える操作として使用することができます。

modification_counter はロールバックをした場合もカウントされますので、BEGINTRAN / ROLLBACK TRAN のような操作を取り消した場合でも変更があったとみなされます。

このようなダミーの操作を行って、統計に関係する列に変更があったと認識させた後に、統計情報を更新することで、検証環境のような自分しか操作していない環境でも、統計情報の更新によるリコンパイルを誘発させることができます。

本番環境 (運用環境) では、頻繁にデータ投入や削除が行われており、それにより統計情報更新によるリコンパイルの誘発を行うことができるかと思いますので、本投稿のような内容は無負荷の検証環境特融の対応となると思いますが。

 

統計情報更新によるリコンパイルの検証をしたい場合は、本投稿のような挙動も意識しておくとよいのではないでしょうか。

Share

Written by Masayuki.Ozawa

2月 10th, 2022 at 9:30 am

Posted in SQL Server

Tagged with

Leave a Reply