Azure SQL Databaseにおける統計情報の作成・更新動作まとめ

こんばんは。この記事ではふと気になったAzure SQL Databaseにおける統計情報の動作に関するあれこれをまとめておきたいと思います。

それではまいります。

統計情報の自動更新

明記された公式ドキュメントを見つけることができませんでしたが、手元で確認した限り”規定で自動更新される”ようでした。以下コマンドで、AUTO_UPDATE_STATISTICS設定が規定で”ON”になっていたことから判断できます。

EXEC sp_autostats 'テーブル名';

参考:https://docs.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/sp-autostats-transact-sql?view=sql-server-ver15

自動更新の基準については、以下に説明があります。

https://docs.microsoft.com/ja-jp/sql/relational-databases/statistics/statistics?view=sql-server-ver15#auto_update_statistics-option

また、以下の通りAzure SQL Databaseに限らずほとんどのSQL Serverデータベースで基本的に有効化しておくことが推奨されています。

https://docs.microsoft.com/ja-jp/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-ver15#auto_update_statistics

統計情報の自動作成

また、以下コマンドではもう少し広範な設定値を確認できます。この結果より、自動作成も規定で有効化されていることが分かります。

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

以下の通り、こちらもAzure SQL DatabaseによらずSQL Serverで基本的に推奨されている構成となります。

https://docs.microsoft.com/ja-jp/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-ver15#auto_create_statistics

なお、INCREMENTALオプションは規定で無効化されていますが、これについては以下に説明があります。

https://docs.microsoft.com/ja-jp/sql/relational-databases/statistics/statistics?view=sql-server-ver15#incremental

統計情報の同期更新/非同期更新

さて、上のクエリ実行結果に含まれているis_auto_update_stats_aysnc_onについてもう少しみてみましょう。

is_auto_update_stats_async_onは、統計更新の同期・非同期更新に関する設定で、Azure SQL Databaseでは規定で同期更新(is_auto_update_stats_async_on = 0)となっています。

https://docs.microsoft.com/ja-jp/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver15#arguments

https://docs.microsoft.com/ja-jp/sql/relational-databases/statistics/statistics?view=sql-server-ver15#auto_update_statistics_async

前提として、統計の更新には同期更新と非同期更新の2種類があります。

  • 同期更新:クエリには常に最新の統計が使用され、それをもとにコンパイルおよび実行される。統計が古い場合、クエリオプティマイザは統計が更新されるのを待機してからクエリがコンパイルされ、実行される。
  • 非同期更新:既存の統計が古い場合でも、既存の統計を使用してクエリがコンパイルされる。クエリをコンパイルするときに統計が古い場合、最適ではないクエリ プランを選択する場合があるが、 通常、統計はその後すぐに更新される。

それぞれ、以下のような場合に適しているようです。

同期更新が推奨されるシナリオ

  • テーブルのTRUNCATEや大部分の行の一括更新など、データの分布が変わる操作を実行する場合

非同期更新が推奨されるシナリオ

  • 同じクエリ、類似のクエリ、またはキャッシュされた類似のクエリプランを頻繁に実行する場合。
  • 厳しいタイムアウト時間が設定されたアプリケーションの場合。同期統計を待機していることで、統計更新中のクエリがタイムアウトする可能性がある。

ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY設定

本題はここからで、Azure SQL Database(とSQL Managed Instance)では、この動作に関して追加の制御を行うことができます。(この2サービスのみで使える設定で、かつ現在パブリックプレビューのようです)これが「ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY」設定です。

統計の非同期更新が有効になっている場合に、この構成を有効にすると、統計の非同期更新が他の同時実行中のセッションをブロックすることを防ぐために、非同期更新のバックグラウンド要求が優先度の低いキューに割り当てられるようになります。

どういうことでしょう。公式ドキュメントでは以下のように説明されています。

  • 統計の非同期更新は、バックグラウンド要求によって行われる。
  • 統計の非同期更新では、更新した統計情報をデータベースに書き込む準備ができた時点で統計メタデータオブジェクトに対するスキーマ変更ロック(Sch-M)の取得を試みる。
  • 別のセッションが同じオブジェクトに対して既にロックを保持している場合、その間統計の非同期更新がブロックされる
  • 逆もしかり(統計の非同期更新がスキーマ安定性ロック(Sch-S)を必要としている別セッションをブロックする)
  • なので、クエリのコンパイルや統計の更新が非常に頻繁に行われる状況では、非同期更新を使用するとロックのブロックによる同時実行の問題がおきやすくなる
  • ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITYを有効にすると、他の要求が既存の統計情報を使用してクエリをコンパイルしている間、バックグラウンド要求は優先度の低い別のキューでスキーマ修正ロック(Sch-M)取得の待機を行い、他セッションのロックが解放されるとバックグラウンド要求が処理されるようになる。(=つまりバックグラウンド要求の優先度が落とされる?)

参考:https://docs.microsoft.com/ja-jp/sql/relational-databases/statistics/statistics?view=sql-server-ver15#auto_update_statistics_async

Sch-MとSch-S

こちらをご参照ください。

https://qiita.com/maaaaaaaa/items/38fd95b142b07acf7700

(おまけ)ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITYの動作を確認してみる

うーむ、SQL Server初心者な私にはまだしっくりきません。

ものは試しです。英語ですがこちらのブログでこの動作を検証する手順が公開されていましたので、こちらに従って私も実験してみました。

https://techcommunity.microsoft.com/t5/azure-sql-blog/improving-concurrency-of-asynchronous-statistics-update/ba-p/1441687

ステップ0:非同期更新の有効化&ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY無効化

手元のAzure SQL Databaseに対して以下を実行します。

ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS_ASYNC ON;
ALTER DATABASE SCOPED CONFIGURATION SET ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = OFF;

ステップ1:テストテーブルと統計情報の作成

DROP TABLE IF EXISTS dbo.stats_wlp;

CREATE TABLE dbo.stats_wlp
(
c1 varchar(200) NOT NULL CONSTRAINT df_stats_wlp_c1 DEFAULT ('Hello World.')
);

CREATE STATISTICS stats_wlp_stat1 ON dbo.stats_wlp (c1);

次いで、テストテーブルに30000行のデータを追加します。

INSERT INTO stats_wlp (c1)
SELECT TOP (30000) 'Hello World.' AS c1 
FROM sys.all_columns AS ac1
CROSS JOIN sys.all_columns AS ac2;

ステップ2:時間のかかるクエリを投げる

SELECT t1.c1, s.object_id, s.stats_id
FROM dbo.stats_wlp t1
CROSS JOIN dbo.stats_wlp t2
CROSS JOIN dbo.stats_wlp t3
CROSS JOIN dbo.stats_wlp t4
CROSS JOIN sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.name = 'stats_wlp_stat1';
  • このクエリでは、統計情報読み込みのために統計メタデータオブジェクトに対してSch-Sロックを取得する
  • クエリがクエリプランをコンパイルするためにロックを取得する状況を再現している

ステップ3:Sch-Sロックが取得されていることを確認

SELECT l.resource_type, l.resource_subtype, l.resource_description, l.request_mode, l.request_type, l.request_status, l.request_session_id, l.resource_lock_partition, r.blocking_session_id, r.command, r.status, r.wait_type
FROM sys.dm_tran_locks AS l
INNER JOIN sys.dm_exec_requests AS r
ON l.request_session_id = r.session_id
WHERE resource_type = 'METADATA'
      AND
      resource_subtype = 'STATS';
  • ステップ2のクエリが回っている間に、上のクエリを実行する
  • ステップ2のクエリがSch-Sロックを取得していることが分かる

ステップ4:統計情報の非同期更新を発生させる

SELECT COUNT(1) AS cnt
FROM dbo.stats_wlp
WHERE c1 LIKE '%Hello%' 
OPTION (RECOMPILE);
  • この状態で、統計情報の更新が発生するクエリを投げる
  • 非同期更新なのでクエリはすぐに完了する

ステップ5:Sch-Mロックの取得を確認

SELECT l.resource_type, l.resource_subtype, l.resource_description, l.request_mode, l.request_type, l.request_status, l.request_session_id, l.resource_lock_partition, r.blocking_session_id, r.command, r.status, r.wait_type
FROM sys.dm_tran_locks AS l
INNER JOIN sys.dm_exec_requests AS r
ON l.request_session_id = r.session_id
WHERE resource_type = 'METADATA'
      AND
      resource_subtype = 'STATS';
  • この状態でステップ3と同じクエリを投げてロック状態を確認すると、統計情報更新のバックグラウンド要求がSch-Mロックを待っていることが分かる(2行目)
  • *ブログだとrequest_typeがCONVERTになると書いているが、実験ではWAITだった。

ステップ6:再び統計情報の非同期更新を発生させてロック状況を確認

SELECT COUNT(1) AS cnt
FROM dbo.stats_wlp
WHERE c1 LIKE '%Hello%' 
OPTION (RECOMPILE);
  • 再度ステップ4のクエリを投げると、統計情報メタデータオブジェクトのSch-Sロックの取得が必要だけどステップ5のバックグラウンド要求によるSch-Mロックの後にキューイングされているためクエリの実行がスタックする。

ステップ7:ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITYを有効化して再実行

  • ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITYを有効化してステップ1~5を繰り返すと、バックグラウンド要求がSch-Mロック & LOW_PRIORITY_WAIT状態になった。
  • この状態でステップ6のクエリを実行すると、今度はスタックすることなくすぐに実行される

以上、Azure SQL Databaseの統計情報の作成・更新動作についてのまとめメモでした。

少しでも参考になりましたら幸いです。

おしまい

この記事を気に入っていただけたらシェアをお願いします!

コメントを残す

メールアドレスが公開されることはありません。

ABOUT US
Yuu113
初めまして。ゆうたろうと申します。 兵庫県出身、東京でシステムエンジニアをしております。現在は主にデータ分析、機械学習を活用してビジネスモデリングに取り組んでいます。 日々学んだことや経験したことを整理していきたいと思い、ブログを始めました。旅行、カメラ、IT技術、江戸文化が大好きですので、これらについても記事にしていきたいと思っています。