【SQL Server】統計情報のヒストグラムと実行プランの予測行数

SQL Serverでは実行プランの決定における情報のうちの一つとして統計情報があります。
その統計情報のうちデータの分布を表すヒストグラムについて説明します。
統計情報は下記クエリで取得できます。

-- 引数は1つ目がテーブル名、2つ目が統計名
-- 結果セットをまとめて取得
DBCC SHOW_STATISTICS (EMPLOYEES, EMP_EMP_ID_PK) WITH NO_INFOMSGS

-- ヒストグラムのみ個別に結果セットを取得
DBCC SHOW_STATISTICS (EMPLOYEES, EMP_EMP_ID_PK) WITH HISTOGRAM,NO_INFOMSGS

ヒストグラムとして取得できる情報は以下となります。

カラム名	説明
RANGE_HI_KEY	ヒストグラム区間の上限の列値
RANGE_ROWS	ヒストグラム区間内 (上限は除く) に列値がある行の予測数
EQ_ROWS	ヒストグラム区間の上限と列値が等しい行の予測数
DISTINCT_RANGE_ROWS	ヒストグラム区間内 (上限は除く) にある個別の列値を持つ行の予測数
AVG_RANGE_ROWS	ヒストグラム区間内 (上限は除く) にある重複する列値を持つ行の平均数 (DISTINCT_RANGE_ROWS > 0 の場合 RANGE_ROWS / DISTINCT_RANGE_ROWS)

※他の結果セットとして取得したものについては詳しくはMSDNを参照してください。
https://msdn.microsoft.com/ja-jp/library/ms174384.aspx

ヒストグラムについて

SQLServerが統計情報として取得して認識している列情報のデータ分布がここからわかります。
例えばDISTINCT_RANGE_ROWSでは、頻度ヒストグラムとしての算出結果が取得されています。
試しに、DBCCコマンドで得られたDISTINCT_RANGE_ROWSを棒グラフでPythonでプロットすると次のような結果が得られます。

import pyodbc
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

pd.set_option('line_width', 100)

conn = pyodbc.connect(
                      r'DRIVER={ODBC Driver 13 for SQL Server};'
                      r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;'
                      r'DATABASE=sales;'
                      r'UID=sa;'
                      r'PWD=system'
                      )

query = r'DBCC SHOW_STATISTICS (EMPLOYEES, EMP_EMP_ID_PK) WITH HISTOGRAM'

df = pd.read_sql(
                 query
                ,conn
                )

# create graph(BAR)
df.plot(
        x = ['RANGE_HI_KEY'],
        y = ['DISTINCT_RANGE_ROWS'],         
        kind = 'bar',
        width = 1,
        alpha = 0.5, 
        figsize = (10, 5)
        )

同様に本統計情報の列情報であるEMPLOYEE_IDについて実際の分布を同じビンでヒストグラムのグラフをプロットすると次のようになります。

import pyodbc
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

pd.set_option('line_width', 100)

conn = pyodbc.connect(
                      r'DRIVER={ODBC Driver 13 for SQL Server};'
                      r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;'
                      r'DATABASE=sales;'
                      r'UID=sa;'
                      r'PWD=system'
                      )


query = r'SELECT EMPLOYEE_ID FROM EMPLOYEES'

df1 = pd.read_sql(
                 query
                ,conn
                )

# create graph(HISTOGRAM)
df1.plot(
        y = ['EMPLOYEE_ID'],
        kind = 'hist',
        bins = [100,115,135,159,182,205,230,242,250,276,285,293,315,355,379,407,419,443,503,539,559,619,671,699,711,747,771,795,815,823,831,903,959,999,1071,1135,1151,1175,1199,1223,1295,1367,1375,1383,1407,1447,1487,1559,1575,1595,1619,1643,1667,1691,1715,1739,1763,1787,1811,1835,1911,1975,2039,2103,2167,2231,2295,2359,2423,2487,2551,2615,2679,2743,2807,2812,2813],
        alpha = 0.5, 
        figsize = (10, 5)
        )

同じ結果が出力できていることがわかります。
尚、今回は統計情報のサンプリングが100％であるため一致しています。統計情報としてサンプリングしている割合が少ない場合、実際の分布とSQLServerの認識している分布が異なるため予測行数と実際の行数に乖離がでることになります。
サンプリングの件数や割合を確認するためには上記のDBCCコマンドか、もしくは上記DBCCコマンドをJOINしたコマンドで確認できます。

-- Rows Sampledがサンプリング数
DBCC SHOW_STATISTICS (EMPLOYEES, EMP_EMP_ID_PK) WITH STAT_HEADER,NO_INFOMSGS

-- SAMPLE_PCTがサンプリングの割合
DBCC SHOW_STATISTICS (EMPLOYEES, EMP_EMP_ID_PK) WITH STAT_HEADER JOIN DENSITY_VECTOR,NO_INFOMSGS

統計情報の更新はサンプリング数を明示的に指定して行うこともできますが、対象の件数の多い場合にサンプリングの割合を多くするとその分更新に時間がかかりますので注意が必要です。

UPDATE STATISTICS EMPLOYEES(EMP_EMP_ID_PK)
	WITH SAMPLE 20 PERCENT

予測行数について

試しに用意したテーブルにてヒストグラムを確認します。

この状態でcolumn3が'10005743'と'10005744'であるものを検索する場合の推定の実行プランを見てみます。

◆column3が'10005744'を検索

予測行数がヒストグラムのEQ_ROWSと一致していることがわかります。

◆column3が'10005743'を検索

'10005743'はRANGE_HI_KEYとは一致していません。この値は、今回の統計情報のヒストグラム区間内に含まれている状態ですので、そのヒストグラム区間に含まれる重複する列値を持つ行の平均数としてAVG_RANGE_ROWS(RANGE_ROWS / DISTINCT_RANGE_ROWS)が、実行プランの予測行数として表示されています。

予測行数と実際の行数に乖離があると適切な実行プランでクエリを実行することができませんので、パフォーマンスが大きく低下する恐れがありますので注意が必要です。