SQL Server / Azure SQL Database の統計情報は、検証用途で UPDATE STATISTICS で統計情報を手動で設定することができます。
統計情報を手動で設定する際には、STATS_STREAM / ROWCOUNT / PAGECOUNT を指定することができるのですが、これらの情報がどのように DB 内に格納されているかを確認する必要があったので、情報をまとめておきたいと思います。
Contents
統計情報を手動で設定する目的
最初に記載しておくと、統計情報を手動で設定することは、検証用途でテーブルにデータが格納されていない状態でも、クエリの実行プランを確認するために使用するためのものとなります。
この機能で設定した統計情報は手動で統計情報を固定化する目的で使用するものではありません。
大量のデータが格納されているテーブルでは、クエリの実行効率の確認を行う環境にデータを移行するのには手間がかかります。
このようなケースでは「統計情報のみをコピーしたデータベース (クローンデータベース) を作成」することで、どのような実行プランが生成されるのかを確認することができます。この際に使用するのが統計情報の手動設定となります。
SQL Server ベースのデータベースエンジンでは、実行プランを生成する際には実データの内容ではなく、統計情報をベースにして実行プランの生成が行われます。これは、推定実行プランも実際の実行プランも同様となります。
つまり、統計情報が移行されていれば、実際のデータが含まれていなくても、どのような実行プランが生成されるのかを確認することができます。
データを移行しなくも、テーブルのスキーマ / 統計情報がコピーされていれば、どのような実行プランが生成されるのかを確認することができるので、このような確認の必要性が出た場合に、統計情報を手動で設定します。
実データを含まないデータベースを作成し、そのデータベースを使用して生成される実行プランを確認するために使用するというのが、本機能の主目的となっているという理解です。
統計情報の手動での設定方法
SQL Server で統計専用データベースを作成する統計スクリプトを生成する方法 で記載されている方法を使用することで、統計情報をスクリプト化することができ、このスクリプトを使用することで、統計情報を移行することができます。
上記のドキュメントでは、SSM のスクリプト生成の機能を使用しています。
SSMS のスクリプト生成機能では、出力対象として指定したテーブルの「統計のスクリプトを作成」する機能があります。デフォルトでは「統計のスクリプトを作成しません」が選択されており、統計情報のスクリプトが生成されないのですが、これを「統計とヒストグラムのスクリプトを作成します」を選択してスクリプトを作成することで、統計情報を作成するスクリプトを出力することができます。
この機能でスクリプトを出力すると、次のようなスクリプトが生成されます。
UPDATE STATISTICS [dbo].[LINEITEM2]([ClusteredIndex_on_LINEITEM_PS_636163560028609030]) WITH STATS_STREAM = 0x010000000100000000 <省略> 00E2A4200000000000000000000000000, ROWCOUNT = 4336142, PAGECOUNT = 124203, AUTO_DROP = OFF GO
このスクリプトを実行することで、テーブルにデータが含まれていなくても、統計情報を作成することができます。
スクリプト内の情報の構成
スクリプト内で、大きな情報としては次の 3 種類があります。
- STATS_STREAM
- ROWCOUNT
- PAGECOUNT
STATS_STREAM
統計情報に含まれる内容については、次の情報から確認することができます。
- 統計
- DBCC
- DMV
統計情報の主な内容としては「ヒストグラム」「密度ベクトル」の 2 種類の情報となります。
この情報がバイナリ (stats_blob / statblob) で保持されたものが STATS_STREAM となります。
私が把握できてる範囲では、この STATS_STREAM のフォーマットは情報が無く、STATS＿STREAM を手動で調整するということはできないという認識です。
情報を書き換えると、次のエラーが発生するため、内部的にはチェックサムがあるのかと思います。
メッセージ 9105、レベル 16、状態 4、行 12
指定された統計ストリームは壊れています。
統計情報の基本的な情報はこの STATS_STREAM に含まれています。
ROWCOUNT / PAGECOUNT
ROWCOUNT / PAGECOUNT については、統計情報に含まれている情報ではありません。これは実行プランの生成を行うために必要な情報となるという理解です。
統計情報には行数が含まれていますが、ヒストグラムを生成するための情報の意味が強いのではないでしょうか。
また、ページ数については統計情報には含まれていません。
UPDATE STATISTICS で ROWCOUNT / PAGECOUNT を指定した際には、次の情報が更新されています。
- ROWCOUNT: sys.partitions
- PAGECOUNT: sys.allocation_units
実行プランを生成する際に、どのインデックスを使用すればよいかを考慮する際には上記の情報も使用されているようです。(同一のインデックスの定義があった場合に、どのインデックスを使用すればよいかにこの情報が使用されていることもあります)
統計情報には含まれていないがデータの選択制を考慮するためには 行数 / ページ数も必要となるため、ROWCOUNT / PAGECOUNT を指定してどの程度のデータが格納されているのかを登録しています。
統計情報の手動設定後に統計情報の自動更新を有効にする
STATS_STREAM / ROWCOUNT / PAGECOUNT を使用して統計情報を更新した場合、UPDATE STATISTICS による統計情報の更新や統計情報の自動更新は動作しないように見えました。
統計情報を手動で設定した場合、以降、通常の統計情報と同様の動作とさせる場合には、インデックスの REBUILD を実行する必要がありそうでした。
統計情報を手動で設定したテーブル / インデックスを REBUILD することで、統計情報が最新化され、以降は通常の統計情報と同じ動作となるかと。