SQL Server / Azure SQLの統計情報に関するあれこれ

こんばんは。この記事ではSQL Server / Azure SQL (Azure SQL Database / Azure SQL Managed Instance) における統計情報の動作に関するあれこれをまとめておきたいと思います。

それではまいります。

Contents

SQL Server / Azure SQLの統計情報の概要

Statistics – SQL Server | Microsoft Docs

テーブルまたはビューの列の値の分布のヒストグラムや、密度（値の重複度合）、列間の値の相関といった統計情報を含んだBLOB (Binary Large OBject)
クエリオプティマイザがクエリに対する最適なクエリプランを生成するためのインプットとなる
具体的には、オプティマイザは統計情報をもとにクエリ結果内のカーディナリティ（行数）を推定し、この情報をもとに最適な処理方法を選択（例えば、ScanかSeekの選択）する
統計情報は規定では、テーブルデータの一部をサンプリングして作成される。統計作成時のオプションを付与することで、すべてのデータを利用した（FULLSCANによる）統計情報も作成できる

統計情報はどの単位で保持できるのか？

以下のオプションがあります。

単数列
複数列

パーティション単位（増分統計）
データの一部（フィルター選択された統計）

複数列

統計は、複数列に対しても作成することができるようです。

Statistics – SQL Server | Microsoft Docs

クエリ述語に複数の列を含む場合、複数列の統計を作成することでパフォーマンスが向上する可能性がある
理由は、複数列の統計には、密度と呼ばれる２つの列の相関に関する統計情報も含めることができるため。これによってオプティマイザのカーディナリティ推定の精度が向上する
複数列の統計作成時、指定する列の順序は密度に影響を及ぼす。統計作成時に先頭に指定した列がクエリ述語で指定されないと、その統計は使われない。（例：列A、列B、列Cの順で指定した複数列の統計を作成すると、（列A）、（列Aと列B）、（列Aと列Bと列C）の３つの密度情報を持つ。ここでクエリ述語に列Bに関する条件を記述しても、この複数列統計の密度はカーディナリティ推定に使われない）
CREATE STATISTICSまたはSSMSから作成できる

CREATE STATISTICS LastFirst ON Person.Person (LastName, MiddleName, FirstName);

たしかに、密度が２パターン生成されていることが確認できます。

複数列のインデックスは、複数列のインデックスを作成した際にも作成されます。

パーティション単位で統計を作成する（増分統計）

Statistics – SQL Server | Microsoft Docs

統計をパーティション毎に作成することができるオプション
SQL Server 2014以降で利用可能
大規模なテーブルに新しいパーティションが追加された場合などに、都度テーブル全体で統計を再作成すると時間がかかって問題、というときに使えるオプション
CREATE STATISTICS T-SQLの、INCREMENTALオプションでON/OFFできる
ONにOFFに変更するとパーティション毎の統計情報は削除され、全体の統計情報が再計算される
サポートされていないシナリオがいくつかあるので利用時は要確認。

その他考慮点は以下の記事も参考になると思います。

SQL Server の統計情報の手動メンテナンスについて at SE の雑記 (engineer-memo.com)

データの一部に対して統計を作成する（フィルター選択された統計）

Statistics – SQL Server | Microsoft Docs

統計作成対象列の、特定のデータに対してのみ統計を作成することができる
場合によっては、全体の統計を作るよりもパフォーマンスが良くなる
CREATE STATISTICS T-SQLにWHERE句を付与して作成することができる

CREATE STATISTICS ContactPromotion1  
    ON Person.Person (BusinessEntityID, LastName, EmailPromotion)  
WHERE EmailPromotion = 2  
WITH SAMPLE 50 PERCENT;  
GO

フィルター選択されたインデックスを作成することでも作成される

なぜ、フィルター選択された統計情報はパフォーマンスを向上させる可能性があるか、以下に少し言及されていました。

フィルター選択された統計情報は、フィルター選択されたインデックスの行のみを対象としているため、テーブル全体の統計情報よりも正確です
フィルター選択されたインデックスの作成 – SQL Server | Microsoft Docs

統計情報はいつ作成されるのか？

これは、データベースで「統計の自動作成」機能を有効にしているかで変わる。以下、自動作成機能についてのメモ。

ALTER DATABASE の SET オプション (Transact-SQL) – SQL Server | Microsoft Docs

規定では有効化されている。かつ有効化が推奨
ALTER DATABASEのAUTO_CREATE_STATISTICS設定でON/OFFを設定可能。
無効化すると、統計が作成されず最適でないクエリプランが生成される可能性がある
有効化していると、クエリオプティマイザが必要に応じて統計を作成してくれる
この場合、クエリ述語で利用されている列１つづつに対して作成される
統計を作成したのち、オプティマイザはその情報を使ってクエリのコンパイルを行う
有効化されているかは、sys.databasesのis_auto_create_stats_on列で確認可能。

select name, is_auto_create_stats_on from sys.databases;

統計情報はいつ更新されるのか？

これは、データベースで「統計の自動更新」が有効化されているか、有効化されている場合、さらに同期更新か非同期更新のどちらで設定されているかで変わる。

統計の自動更新機能

ALTER DATABASE の SET オプション (Transact-SQL) – SQL Server | Microsoft Docs

既定では有効化されている。かつ、有効化が推奨
ALTER DATABASEのAUTO_UPDATE_STATISTICS設定でON/OFFを設定可能。
統計が古くなっている可能性がある場合に、クエリオプティマイザが自動的に統計を更新する
統計が古くなっているかは、ここに整理されているロジックで判断される。SQL Server 2014以前と2016以降でロジックが異なる。
統計が古くなっているかのチェックは、クエリをコンパイルする前と、キャッシュされたクエリプランを実行する前に行われる。
有効化されているかは、sys.databasesのis_auto_update_stats_on列、またはEXEC sp_autostatsコマンドで確認可能。

select name, is_auto_create_stats_on, is_auto_update_stats_on from sys.databases;

EXEC sp_autostats 'テーブル名';

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

統計の同期/非同期更新

統計の自動更新タイミングには、同期/非同期の２つのオプションが選択できる。以下ポイント。

Statistics – SQL Server | Microsoft Docs

同期更新：統計が古い場合、クエリオプティマイザは統計が更新されるのを待機してからクエリをコンパイルし、実行する。その結果クエリでは常に最新の統計が使用される。規定はこちら。

非同期更新：統計が古い場合、非同期で更新するため、そのクエリでは古い統計でクエリがコンパイルされることになる。その結果そのクエリでは最適でないプランが選択される可能性があるが、通常、統計はその後すぐに更新され、次回以降の実行では最新の統計が利用される。
ALTER DATABASEのAUTO_UPDATE_STATISTICS_ASYNC設定でON/OFFを設定可能。
どちらが良いかは要件次第。

同期更新は、テーブルのTRUNCATEや大部分の行の一括更新など、データの分布が変わる操作を実行する場合に適している。その後に実行するクエリでは、確実に最新の統計が利用されるようにできる。
非同期更新は、同じクエリ、類似のクエリ、またはキャッシュされた類似のクエリプランを頻繁に実行する場合や、クエリ時間への要求が厳しく、同期更新の待ち時間を許容できない場合に選択肢となる。
この機能は「統計自動更新」機能のオプションなので、統計自動更新が無効化されている場合、AUTO_UPDATE_STATISTICS_ASYNCの設定は効果を持たない。
有効化されているかは、sys.databasesのis_auto_update_stats_async_onから確認できる

select name, is_auto_create_stats_on, is_auto_update_stats_on,is_auto_update_stats_async_on from sys.databases;

ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY設定

なお、Azure SQL (Azure SQL DatabaseとSQL Managed Instance）では、統計の非同期更新において、追加の制御を行うことができます。（現在パブリックプレビュー）これが「ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY」設定です。

Statistics – SQL Server | Microsoft Docs

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

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

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

Sch-MとSch-S

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

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

なお、この「ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY」の動作の検証は、この記事の最後に載せています。

統計情報の確認方法

統計のプロパティの表示 – SQL Server | Microsoft Docs

SSMSから確認する

各テーブル＞統計＞プロパティから確認できます。

統計の作成対象列情報や、

統計情報そのものの内容が覗けたりする。右にスクロールするとサンプリングされた行数なども確認できる。

T-SQL

DBCC SHOW_STATISTICS

DBCC SHOW_STATISTICS (Transact-SQL) – SQL Server | Microsoft Docs

sys.stats

sys.statsを使うと、統計の一覧が確認できるようです。ただし、各統計の最終更新日時情報は含まれない模様。

sys.stats (Transact-SQL) – SQL Server | Microsoft Docs

  SELECT name AS statistics_name  
    ,stats_id  
    ,auto_created  
    ,user_created
	,is_incremental
	,has_filter
	,filter_definition
    ,no_recompute  
-- using the sys.stats catalog view  
FROM sys.stats  
-- for the Sales.SpecialOffer table  
WHERE object_id = OBJECT_ID('Sales.Store');  
GO

その他プラクティス

DO’s&DONT’s #8: やってはいけないこと – インデックス再構築 (REBUILD) 後のインデックス統計情報更新 (UPDATE STATISTICS) (microsoft.com)

【SQLServer】インデックス再構築では自動生成された統計情報は更新されない – souegg2’s blog (hatenablog.com)

（おまけ）ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITYの動作を確認してみる

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

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

ステップ０：非同期更新の有効化＆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;

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

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;

ステップ２：時間のかかるクエリを投げる

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ロックを取得する
クエリがクエリプランをコンパイルするためにロックを取得する状況を再現している

ステップ３：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';

ステップ２のクエリが回っている間に、上のクエリを実行する
ステップ２のクエリがSch-Sロックを取得していることが分かる

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

SELECT COUNT(1) AS cnt
FROM dbo.stats_wlp
WHERE c1 LIKE '%Hello%' 
OPTION (RECOMPILE);

この状態で、統計情報の更新が発生するクエリを投げる
非同期更新なのでクエリはすぐに完了する

ステップ５：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';

この状態でステップ３と同じクエリを投げてロック状態を確認すると、統計情報更新のバックグラウンド要求がSch-Mロックを待っていることが分かる（２行目）
＊ブログだとrequest_typeがCONVERTになると書いているが、実験ではWAITだった。

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

SELECT COUNT(1) AS cnt
FROM dbo.stats_wlp
WHERE c1 LIKE '%Hello%' 
OPTION (RECOMPILE);

再度ステップ４のクエリを投げると、統計情報メタデータオブジェクトのSch-Sロックの取得が必要だけどステップ５のバックグラウンド要求によるSch-Mロックの後にキューイングされているためクエリの実行がスタックする。

ステップ７：ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITYを有効化して再実行

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

以上、SQL Server / Azure SQLの統計情報についてのあれこれでした。

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

おしまい

SQL Server / Azure SQLの統計情報の概要

統計情報はどの単位で保持できるのか？

複数列

パーティション単位で統計を作成する（増分統計）

データの一部に対して統計を作成する（フィルター選択された統計）

統計情報はいつ作成されるのか？

統計情報はいつ更新されるのか？

統計の自動更新機能

統計の同期/非同期更新

ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY設定

統計情報の確認方法

SSMSから確認する

T-SQL

DBCC SHOW_STATISTICS

sys.stats

その他プラクティス

（おまけ）ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITYの動作を確認してみる

ステップ０：非同期更新の有効化＆ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY無効化

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

ステップ２：時間のかかるクエリを投げる

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

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

ステップ５：Sch-Mロックの取得を確認

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

ステップ７：ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITYを有効化して再実行

関連

コメントを残す

Profile

Categories

yutaro013_scenery

Trending Posts

アーカイブ