『 SQL Server の統計情報、特にヒストグラムについて説明してみる 』 についての補足 - その2 -

　『 SQL Server の統計情報、特にヒストグラムについて説明してみる 』 で以下のような記事を書きました。

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

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となります。

  統計情報作成時には、常にすべての行を対象にするわけではありません。(テーブルに大量のレコードが存在する場合は、数％程度になります。) Row Sampled の値が非常に低い場合、正確な統計情報が作成されていない場合があります。クエリプランがおかしいと思ったら、Row Samples 数をチェックしてください。異常に低い場合は、下記のコマンドを実行します。

UPDATE STATISTICS [Production].[ProductInventory] 
                  [PK_ProductInventory_ProductID_LocationID] WITH FULLSCAN

　上記のコマンドは、対象テーブル全件をサンプル対象とします。従ってレコード数が多いテーブルを対象とした場合、システム全体に負荷をかけてしまう恐れがあります。サンプル対象を低くしたい場合は、下記のコマンドを実行します。

UPDATE STATISTICS [Production].[ProductInventory] 
                  [PK_ProductInventory_ProductID_LocationID] WITH SAMPLE 50 PERCENT

　上記のコマンドは、対象テーブルの 50% をサンプル対象とします。あと、統計情報の更新に関しては、ここの記事の内容に関しても注意すべきです。

The distribution of data is not altered when an index is rebuilt or reorganised so there is no requirement for statistics to be updated as a result of these operations. However statistics are updated/rebuilt with a 100% sample rate when an index is rebuilt but are not updated not when an index is reorganised. It is, therefore, counter-productive to update stats after a rebuild.

　自分自身、恥ずかしながら過去にインデックスの再構築実施後、統計情報を更新していた記憶があります。システム無用な負荷掛けないためにも注意しないといけないですね。  

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

　SQL Server の統計情報、特にヒストグラムについて説明してみる の補足です。統計情報はコマンドで参照することも可能です。SQL Management Studio で下記のコマンドを実行します。

USE AdventureWorks2012
dbcc show_statistics('Sales.SalesReason','PK_SalesReason_SalesReasonID')

　上記のコマンドを実行すると、下記のように出力されます。

　こちらのほうが見やすいですよね。統計基本情報、密度情報、ヒストグラムと見やすくレイアウトされています。統計情報の詳細もテキストベースではなく、上図のように見えてくれると参照しやすんですが...改善してくれないですかね？

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 はこのような情報群を用いて、最適な実行プランを作成しています。このような理由から、統計情報を常に最新状態にしておくことが如何に重要であるか理解できると思います。運用環境で統計情報の更新を怠ると、パフォーマンスダウンしてしまいますので、ご注意を。