SQL Server の統計情報、特にヒストグラムについて説明してみる
SQL Server の統計情報、特にヒストグラムの見方について説明したいと思います。まず、統計の復習として、ヒストグラムとは?
ヒストグラム(英: histogram[1])とは、縦軸に度数、横軸に階級をとった統計グラフの一種で、データの分布状況を視覚的に認識するために主に統計学や数学、画像処理等で用いられる。柱図表[1]、度数分布図、柱状グラフともいう。
また、工業分野では、パレート図、チェックシート、管理図、特性要因図、層別法、散布図と並んで、品質管理のためのQC七つ道具として知られている。
※ヒストグラムの例
思い出しましたか?遠い昔の記憶が蘇ってきたかと思います。(どうでしょうか?) それでは、SQL Server における統計情報をいつも記事で使用している AdventureWorks のデータベースを用いて説明します。(レコード件数の少ない、 SalesReason テーブルを用います。SalesReason テーブルは下図の通りです。)
まず、統計情報の参照方法です。SQL Server Management Studio のオブジェクトエクスプローラーから、赤枠部分をダブルクリックします。
統計情報画面が表示されます。詳細(Details)をクリックします。
統計情報の詳細が表示されます。パッと見わからないですよね。
上図の内容を説明していきたいと思います。まずは統計の基本情報です。
Name Updated Rows Rows Sampled Steps Density Average Key Length String Index -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PK_SalesReason_SalesReasonID Mar 14 2012 1:14PM 10 10 6 1 4 NO 10
上記の情報は下記の列から構成されています。
- Name:索引の名称です。
- Updated:統計情報が更新された日時です。
- Rows:このテーブルのレコード件数です。
- Rows Sampled:統計情報を作成するために、サンプリング抽出された行数です。この件数は多いほど精度が高いです。
- Steps:ヒストグラムのデータ区間の数です。(最大200区間です。)
- Density:これは現在のバージョン(2012等)では使用されていません。
- Average Key Length:平均のキー長です。
- String Index:列に文字列が存在する場合は、YES。今回は存在しないので、NOとなります。
次に、統計の密度情報です。
All Density Average Length Columns -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0.1 4 SalesReasonID
- All Density:密度は 1 をその列のデータをDISTINCTした数で割ったものです。今回の場合は、1 / 10 = 0.1 となります。密度が低いほど、行の絞りこみが可能で、効率的ということになります。
- Average Length:列の平均長です。
- Columns:統計対象の列名です。
最後にヒストグラムです。
Histogram Steps RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 0 1 0 1 3 1 1 1 1 5 1 1 1 1 7 1 1 1 1 9 1 1 1 1 10 0 1 0 1
- RANGE_HI_KEY:ステップの上限キーです。
- RANGE_ROWS:ステップ数内の行数です。上限は含みません。
- EQ_ROWS:ヒストグラム区間の上限と列値が等しい行の予測数です。
- DISTINCT_RANGE_ROWS:ヒストグラム区間内にある個別の列値を持つ行の予測数です。上限は含みません。
- AVG_RANGE_ROWS:ヒストグラム区間内にある重複する列値を持つ行の平均数です。上限は含みません。
上記の内容だけでは中々理解しづらいと思いますので、これらをわかりやすくするために、実際に今回のデータを使用し、エクセルでヒストグラムを作成したいと思います。
まず、エクセルに対象のデータを以下のように貼り付け、RANGE_HI_KEY をデータ区間上限値として入力します。
次に、『データ』タブの『データ分析』をクリックします。
ヒストグラムをクリックし、OKボタンをクリックします。
入力範囲およびデータ区間を選択し、OKボタンをクリックします。
今回のデータに関するヒストグラムが生成できました。
上記の内容を理解することで、SQL Server のヒストグラムの各項目に関して理解できると思います。SQL Server はこのような情報群を用いて、最適な実行プランを作成しています。このような理由から、統計情報を常に最新状態にしておくことが如何に重要であるか理解できると思います。運用環境で統計情報の更新を怠ると、パフォーマンスダウンしてしまいますので、ご注意を。