NOBTAの気ままにITブログ

Azure全般 / SQL Serverに関する情報を発信していきます。

【第15回】基本から始める SQL Server【統計情報1 基本】


スポンサーリンク

第14回では「ページとエクステントに関する内容をまとめてみました。

 

今回は、SQL Server でクエリの実行プランを生成する際に参照される「統計情報」に関する内容について、自分の整理も兼ねてまとめてみようと思います。

 

 

統計情報について

SQL Server でクエリを実行する場合、オプティマイザにより実行するクエリ内容を分析し、どのインデックス、どのオペレータ (Index Scan/Index Seek、Hash/Nested Loop 結合 など) を使用してクエリの結果を取得するかを定めたクエリの実行プランが生成されますが、 最適なクエリの実行プランを生成する際に参照されるのが「統計情報」になります。

 

各種オペレータの詳細については、以下のURLを参照

 

統計情報は、「テーブルに作成されたインデックス単位」、統計情報の自動作成オプション (AUTO_CREATE_STATISTICS) が有効になっている場合は、「インデックス キーに指定されていないテーブル列単位」(手動で明示的に列の統計情報を作成することは可能) で存在します。

 

テーブルに作成されたインデックス単位

該当テーブル上にクラスタ/非クラスタ化インデックスを作成すると、インデックスを作成したタイミングでインデックスに紐づく統計情報の作成が行われます。

例えば、「PK_InsTab1」というクラスタ化インデックスを作成すると、該当インデックスに紐づく「PK_InsTab1」という名前の統計情報が作成されます。

 

f:id:nobtak:20220101165800p:plain

 

インデックス キーに指定されていないテーブル列単位

統計情報の自動作成オプション (AUTO_CREATE_STATISTICS) が有効 (既定で有効) になっている場合、クエリの条件句にインデックス キーに指定していないテーブル列を指定したうえでクエリを実行することで、テーブル列単位の列統計が自動的に作成されます。

例えば、以下のクエリでは、インデックス キーに指定していない「C2列」、「C3列」、「C4列」をクエリの条件句に指定しています。

f:id:nobtak:20220101171900p:plain

 

そのため、該当クエリを実行すると、「C2列」、「C3列」、「C4列」に紐づく列統計情報「_WA_Sys_00000002_******」、「_WA_Sys_00000003_******」、「_WA_Sys_00000004_******」が自動的に作成されます。

 

f:id:nobtak:20220101172001p:plain

 

[補足]

クエリ パフォーマンスが著しく低下するような現象が発生した場合、作成されているインデックスの統計情報を更新することがあるかと思いますが、特定インデックスの統計情報の更新では、インデックスに紐づく統計情報の更新は行われますが、列統計情報の更新は行われません。

そのため、特定のクエリのみではなく全体的にクエリ パフォーマンスが低下している場合は、列統計情報も更新するため、UPDATE STATISTICS コマンドをテーブル単位で実行することを検討すると良いかもしれません。

 

クエリ パフォーマンスが著しく低下するような現象が発生した場合には、以下 URL を参照

 

統計情報の自動更新について

統計情報は、統計の自動更新オプション (AUTO_UPDATE_STATISTICS) が有効 (既定で有効) になっている場合、DML操作 (挿入、更新、削除) により、インデックス キーに指定された列に変更が行われ、自動統計更新のしきい値を超えた場合、統計情報の自動更新処理が行われます。

自動統計更新のしきい値は、SQL Server のバージョンによって計算方法が異なります。

 

SQL Server 2014 以前

1) テーブル行数が 500 行以下の場合

500回 インデックス キーへの変更が行われた場合

 

2) テーブル行数が 500 行以下の場合

500 + (0.20 * テーブル行数)

 

[例] テーブル行数が 10万行 の場合

500 + (0.20 * 100,000) = 20,500

 

つまり、インデックス キーの変更が 20,500 回 行われた場合、統計情報の自動更新処理が行われます。

 

SQL Server 2016 以降 (Azure SQL Database 含む) + データベース互換性レベル 130 以降の場合

1) テーブル行数が 500 行以下の場合

500回 インデックス キーへの変更が行われた場合

 

2) テーブル行数が 500 行以下の場合

MIN (500 + (0.20 * テーブル行数), SQRT (1,000 * テーブル行数))

 

[例] テーブル行数が 10万行 の場合

500 + (0.20 * 100,000) = 20,500

SQRT (1,000 * 100,000) = 10,000

 

つまり、SQRT (1,000 * 100,000) = 10,000 の方が数が少ないため、インデックス キーの変更が 10,000 回 行われた場合、統計情報の自動更新処理が行われます。

SQL Server 2016 以降、新しいロジックにより、統計情報の自動更新処理が発生しやすくなっています。

 

SQRT 関数の詳細は、以下 URL を参照

 

SQL Server 2016 以降 (Azure SQL Database 含む) + データベース互換性レベル 130 未満の場合

SQL Server 2014 以前と同じ自動統計更新のしきい値が適用されます。

 

まとめ

今回は、SQL Server でクエリの実行プランを生成する際に参照される「統計情報」に関する内容について、まとめてみました。

次回は、統計情報のアーキテクチャについてまとめてみようと思います。

 

【第16回】基本から始める SQL Server【統計情報2】へ

 

※ 2022年1月 時点