第15回では「統計情報」に関する内容をまとめてみました。
今回は、「統計情報」のアーキテクチャなどに関する内容について、自分の整理も兼ねてまとめてみようと思います。
統計情報の自動更新のタイミングについて
統計情報は、統計の自動更新オプション (AUTO_UPDATE_STATISTICS) が有効 (既定で有効) になっている場合、自動統計更新のしきい値を超えたタイミングで、統計情報の自動更新処理が行われることを「【第15回】基本から始める SQL Server【統計情報1】」で紹介しました。
統計情報の自動更新は、「同期更新」(既定)、「非同期更新」の二つの設定があり、設定によって統計情報の自動更新が行われるタイミングが異なります。
「同期更新」(既定) の場合
統計情報の自動更新は、クエリの実行プランが生成されるタイミングで実施されます。
具体的には、簡易的に記載すると以下のような流れで統計情報の自動更新処理が行われます。
1) クライアント (アプリケーション) からのクエリの実行要求を SQL Server が受信
2) オプティマイザによりクエリの実行プランを生成するタイミングで、自動統計更新のしきい値をチェック
3) 自動統計更新のしきい値を超えていた場合、統計情報の自動更新処理を実施
4) 統計情報の自動更新処理が完了後、クエリの実行プランを生成
5) クエリを実行し、クエリを実行した結果セットをクライアント (アプリケーション) 側に送信
つまり、クライアント (アプリケーション) 側からクエリの実行要求が行われた後に統計情報の自動更新処理が行われます。 そのため、クエリタイムアウト値を設定している場合でかつ、統計情報の自動更新処理が行われた場合、クエリの処理時間 (クエリの実行プラン生成＋クエリの実行) に統計情報の自動更新処理時間が含まれることになります。
そのため、通常 クエリタイムアウト値に指定した時間に近い時間で完了するクエリを実行する際に、同時に統計情報の自動更新処理が行われてしまうと、クエリタイムアウトが発生しやすい状況になる点に注意が必要です。
「非同期更新」の場合
統計情報の自動更新は、非同期で行われることになります。
「同期更新」では、自動統計更新のしきい値を超えていた場合、統計情報の自動更新処理が実施された後にクエリの実行プランを生成することが可能になりますが、「非同期更新」では、タイミングによっては、統計情報の自動更新が必要な状態においても、古い統計情報を使用し、最適ではないクエリの実行プランが生成される可能性があります。
しかしながら、クライアント (アプリケーション) 側からクエリの実行要求が行われた後に統計情報の自動更新処理が行われなくなるため、通常 クエリタイムアウト値に指定した時間に近い時間で完了するクエリが実行された場合においても、クエリタイムアウトが発生しやすい状況になることを回避できることが期待できます。
但し、最適ではないクエリの実行プランが生成されたことにより、クエリの処理時間が延び、クエリタイムアウトが発生ような状況が発生する可能性はあります。
統計情報のアーキテクチャについて
統計情報には、テーブル列の値の分布を示す「ヒストグラム (histogram)」、特定の列もしくは列の組み合わせにおける重複の数 (一意性) に関する情報を示す「密度ベクトル (Density Vector)」などの情報が格納されています。
ヒストグラム (Histogram)
統計情報の最初のキー列から列値に基づいて、最大200の区間 (ヒストグラム区間) に分割し、区間毎に以下の項目値が作成されます。
- RANGE_HI_KEY : ヒストグラム区間の上限(最初)の列値
- RANGE_ROWS : ヒストグラム区間内に列値がある行の予測数
- EQ_ROWS : ヒストグラム区間の上限(最初)の列値と等しい行の予測数
- DISTINCT_RANGE_ROWS : ヒストグラム区間内にある一意の列値を持つ行の予測数
- AVG_RANGE_ROWS : ヒストグラム区間内にある一意の列値を持つ行の予測数の平均値
[例] テーブル「Instab1」、統計情報「_WA_Sys_00000002_24927208」のヒストグラム情報 (統計ヘッダ情報含む) を取得する場合
|
DBCC SHOW_STATISTICS (Instab1,_WA_Sys_00000002_24927208 ) WITH STAT_HEADER DBCC SHOW_STATISTICS (Instab1,_WA_Sys_00000002_24927208 ) WITH HISTOGRAM |
この例では、13万行あるテーブルからサンプルとして 44,441行が抽出され、ヒストグラムが作成されています。
本テーブルの該当列(「c2」列)の値は、「TEST」～「TEST5」の５パターンしかないため、「EQ_ROWS」(ヒストグラム区間の上限(最初)の列値と等しい行の予測数) の値が実際の行数と類似の値になっています。
[例] テーブル「Instab1」、統計情報「PK_InsTab1」のヒストグラム情報を取得する場合
| DBCC SHOW_STATISTICS (Instab1, PK_InsTab1) WITH HISTOGRAM GO |
クラスタ化インデックス「PK_InsTab1」のキー値に指定された本テーブルの該当列(「c1」列)の値は、一意制約が付いているため、列値はすべて一意の値になっており、「DISTINCT_RANGE_ROWS」(ヒストグラム区間内にある一意の列値を持つ行の予測数) の値が実際の行数と類似の値になっています。
なお、ヒストグラムより、クエリの条件句に指定された値が「RANGE_HI_KEY」のどの範囲に属するか、また、属する範囲は一意性が高いのか、低いのかといった情報をクエリの実行プランを生成するオプティマイザに渡すことが可能になります。
そのため、統計情報が古い (ヒストグラムが正しくない) 状態では、正しい情報がオプティマイザに渡されないため、最適なクエリの実行プランを生成することが出来ない可能性があり、最適なクエリの実行プランを生成させるために、統計情報は可能な限り最新の状態に更新する必要があります。
DBCC SHOW_STATISTICS コマンドの詳細については、以下 URL を参照
※ ヒストグラム情報については、動的管理ビュー「sys.dm_db_stats_histogram」から取得することが可能です。
密度ベクトル (Density Vector)
特定の列もしくは列の組み合わせにおける重複の数 (一意性) 毎に、密度 (Density : 計算式 「1/重複の数」) が作成されます。
[例] テーブル「Instab1」、統計情報「IX_InsTab1」の密度ベクトル情報を取得する場合
| DBCC SHOW_STATISTICS (Instab1, IX_InsTab1) WITH DENSITY_VECTOR GO |
この例では、列「c1」の密度、列「c1, c2」の組み合わせの密度が作成されています。
- All density : 密度 (Density : 計算式 「1/重複の数」)
- Average Length : 特定の列もしくは列の組み合わせの平均長 (バイト数)
- Columns : 特定の列もしくは列の組み合わせ
密度が低いほど一意性が高く (カーディナリティが高い) 、密度が高いほど一意性が低い (カーディナリティが低い) と判断できます。
クエリの実行プランを生成するオプティマイザでは、密度ベクトル情報を参照し、インデックス スキャン操作 (Index Scan)、インデックス シーク操作 (Index Seek) を実施した場合のコストを算出しているため、統計情報が古い (密度ベクトルが正しくない) 状態では、正しい情報がオプティマイザに渡されないため、最適なクエリの実行プランを生成することが出来ない可能性があり、最適なクエリの実行プランを生成させるために、統計情報は可能な限り最新の状態に更新する必要があります。
まとめ
今回は、「統計情報」のアーキテクチャなどに関する内容について、まとめてみました。「統計情報」が最新の状態でない場合、最適なクエリの実行プランを生成することができない可能性があるため、「統計情報」は可能な限り、最新の状態に保つようにすることをお勧めします。
また、今回は紹介していませんが、データ量の多いテーブルに対してパーティションを作成している場合は、統計情報の増分更新 (INCREMENTAL) 機能により、パーティション毎に統計情報の作成／自動更新が可能となり、統計情報の自動更新に要する時間を短縮させることが可能になります。但し、SQL Server 2014 以降のバージョンで使用可能な点に注意してもらえればと思います。
統計情報の詳細な更新方法については、以下のURLを参照
【第17回】基本から始める SQL Server【サービスパック (SP)、累積的な更新プログラム (CU)、重要な更新プログラム (GDR/QFE)】 へ
※ 2022年1月 時点
