都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

都内でサラリーマンやってます。SQL Server を中心とした (2023年からは Azure も。) マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。英語の勉強のため、英語の

SQL Server の統計情報、特にヒストグラムについて説明してみる

 SQL Server の統計情報、特にヒストグラムの見方について説明したいと思います。まず、統計の復習として、ヒストグラムとは?

ヒストグラム(英: histogram[1])とは、縦軸に度数、横軸に階級をとった統計グラフの一種で、データの分布状況を視覚的に認識するために主に統計学や数学、画像処理等で用いられる。柱図表[1]、度数分布図、柱状グラフともいう。

また、工業分野では、パレート図、チェックシート、管理図、特性要因図、層別法、散布図と並んで、品質管理のためのQC七つ道具として知られている。

 ※ヒストグラムの例

f:id:koogucc11:20131012192041j:plain

 思い出しましたか?遠い昔の記憶が蘇ってきたかと思います。(どうでしょうか?) それでは、SQL Server における統計情報をいつも記事で使用している AdventureWorks のデータベースを用いて説明します。(レコード件数の少ない、 SalesReason テーブルを用います。SalesReason テーブルは下図の通りです。)

f:id:koogucc11:20131012202734j:plain

 まず、統計情報の参照方法です。SQL Server Management Studio のオブジェクトエクスプローラーから、赤枠部分をダブルクリックします。

f:id:koogucc11:20131012194234j:plain

 統計情報画面が表示されます。詳細(Details)をクリックします。

f:id:koogucc11:20131012194450j:plain

 統計情報の詳細が表示されます。パッと見わからないですよね。

f:id:koogucc11:20131012194657j:plain

 上図の内容を説明していきたいと思います。まずは統計の基本情報です。

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 をデータ区間上限値として入力します。

f:id:koogucc11:20131012212732j:plain

 次に、『データ』タブの『データ分析』をクリックします。

f:id:koogucc11:20131012212913j:plain

 ヒストグラムをクリックし、OKボタンをクリックします。

f:id:koogucc11:20131012213052j:plain

 入力範囲およびデータ区間を選択し、OKボタンをクリックします。 

f:id:koogucc11:20131012213246j:plain

 今回のデータに関するヒストグラムが生成できました。

f:id:koogucc11:20131012213612j:plain

 上記の内容を理解することで、SQL Serverヒストグラムの各項目に関して理解できると思います。SQL Server はこのような情報群を用いて、最適な実行プランを作成しています。このような理由から、統計情報を常に最新状態にしておくことが如何に重要であるか理解できると思います。運用環境で統計情報の更新を怠ると、パフォーマンスダウンしてしまいますので、ご注意を。