SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

検証目的で UPDATE STATISTICS で統計情報を手動で書き換える (update_stats_stream_option)

without comments

本投稿の内容を使用すると、クエリの実行プランに大きく影響を与えますので、検証目的でクエリの実行プランを変更する場合以外、本投稿の内容は使用しないでください。

ドキュメントにも「サポートされていない」ことが明記されており、本番環境で実行して、クエリの実行に大きく影響を与えても一切の責任は取りません。
クエリの実行に影響があると、本業務に影響を与える環境に対して実施する場合は自己責任で実施してください。

ということで、検証以外では、本投稿の内容は使用しないでください。
この投稿の内容は、クエリの実行プランが大きく変わり、性能にダイレクトに影響を与えます。

本来の目的はクローンデータベースを作成する際に、検証目的で統計情報の復元を行うためのものですので、統計情報の固定に使おうとすると、予期せぬ動作を引き起こす可能性が十分にあります。

UPDATE STATISTICS の機能

SQL Server / SQL Datatabase では UPDATE STATISTICS を実行することで、統計情報を手動で更新することができます。
実データと統計情報の内容に乖離が発生した場合などの、即時更新に活用することができます。

UPDATE STATISTICS には、統計情報を手動で更新するだけでなく、「特定の統計情報に手動で設定をする」機能があります。

この機能は「update_stats_stream_option」として利用することができ、次の 3 種類のオプションを UPDATE STATISTICS で指定することができます。

<update_stats_stream_option> ::=
[ STATS_STREAM = stats_stream ]
[ ROWCOUNT = numeric_constant ]
[ PAGECOUNT = numeric_contant ]

 

この機能を使用すると、任意のタイミングの統計情報を手動で設定することができます。

このオプションについては、次のように明記されており、非サポートの機能となりますので、利用する際には細心の注意をしてください。

<update_stats_stream_option>

単に情報を示すためだけに特定されます。 サポートされていません。 将来の互換性は保証されません。

 

update_stats_stream option の取得の方法については、2 種類あります。

 

スクリプトの生成

一つ目の方法は「スクリプトの生成」の機能を利用することです。

image

ウィザードを進めると、「詳細設定」からスクリプトを作成する際のオプションを指定することができるのですが、詳細設定で「統計とヒストグラムのスクリプトを作成します」を選択します。
image

そうすると、UPDATE STATISTICS のスクリプトが生成されるのですが、詳細設定を変更しておくと、次の画像のように、STATS_STREAM の情報についても出力が行われます。
image

この UPDATE STATISTICS を実行すると、統計情報が手動で更新され、スクリプト生成時の統計情報がテーブルに反映されます。

 

DBCC SHOW_STATISTICS

もう一つの方法が DBCC SHOW_STATISTICS を実行する方法です。
この DBCC コマンドには、STATS_STREAM というオプションがあります。

 

— Syntax for SQL Server and Azure SQL Database 
 
DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )  
[ WITH [ NO_INFOMSGS ] < option > [ , n ] ] 
< option > :: = 
    STAT_HEADER | DENSITY_VECTOR | HISTOGRAM | STATS_STREAM

 

STATS_STREAM は単に情報を示すためだけに特定されます。 サポートされていません。 将来の互換性は保証されません。

 

コマンドを実行すると、次にような情報を取得することができます。

image

この情報を UPDATE STATISTICS に指定することで、取得タイミングの統計情報に手動で更新することができます。

 

 

STATS_STREAM の情報はバイナリとなっており、フォーマットについては公開されていません。
バイナリ内にチェックサムを持っているのか、適当に情報を書き換えると、適用することができませんので、「任意の統計情報を手動で生成して設定を行う」ということは難しいかと思います。(私は解析しようとして諦めました)

 

update_stats_stream_option の内容

update_stats_stream_option ですが、以下の 3 種類の情報で構成されており、各設定はオプションですので、どれか一つのみ指定することが可能です。

  1. STATS_STREAM
  2. ROWCOUNT
  3. PAGECOUNT

SQL Server の統計情報については 統計 に記載されています。

 

STATS_STREAM

統計情報のメインとなる情報です。
STATS_STREAM に含まれる情報は DBCC SHOW_STATISTICS で確認できる統計情報となります。

image

統計情報のヒストグラム (データの分布) と、密度 (データのバリエーション) については、STATS_STREAM の情報を適用することで、反映させることができます。

 

統計情報の STATS_STREAM (stats_blob) については、sys.sysobjvalues に格納されています。
この情報が統計情報の実態となりますが、一部の情報はバイナリで格納されていることが確認できますね。

image

 

 

ROWCOUNT / PAGECOUNT

統計情報というと STATS_STREAM で記載した「ヒストグラム」「密度」を思い浮かべることが多いのではないでしょうか。
これらの情報はクエリの実行プラン (実行計画) を生成するために重要な要素ではありますが、実行プランを決定するための使用しているのは、これらの情報だけではありません。

正しい統計情報では、ROWCOUNT / PAGECOUNT は次のような値となります。

image

この状態であれば、統計情報を捜査している PARTSUPP テーブルの参照では、「Clustered Index Seek」によるインデックスシークが採用されています。
image

それでは、統計情報のヒストグラムと密度を変更せずに、ROWCOUNT / PAGECOUNT を書き換えるとどうなるでしょうか。

image

データの分布の情報 (ヒストグラム / 密度) は変更していないのですが、実行プランが Seek → Scan に変わり、実行の効率が著しく低下しています。

image

ROWCOUNT と PAGECOUNT を小さな値としたことで、推定の件数が大幅に減少したため、クエリオプティマイザが少量のデータが格納されているテーブルとして認識を行ってしまったため、Scan 操作を選択しましたが、実際には大量のデータが格納されているため、実行の時間が数10倍以上劣化しています。

このような動作から、クエリの実行プランを決める際には、STATS_STREAM のヒストグラム / 密度だけでなく、「テーブルにどの程度のデータが格納されているのか?」という母数の情報も重要となるということが確認できます。(この件数の情報によって、Seek / Scan だけでなく、並列クエリとして実行するかも変わってきます)

STATS_STREAM については、sys.sysobjvalues に格納されていましたが、ROWCOUNT / PAGECOUNT は別の場所に格納されています。

 

  • ROWCOUNT : sys.partitions (実体は sys.sysrowsets)
  • PAGECOUNT : sys.allocation_units (実体は sys.sysallocunits)

として、格納が行われています。

SELECT 
    object_NAME(p.object_id) AS object_name,
    p.index_id,
    p.rows,
    a.data_pages,
    a.total_pages,
    a.used_pages
FROM 
    sys.partitions AS p
    LEFT JOIN sys.allocation_units AS a
        ON a.container_id = p.hobt_id
WHERE 
    p.object_id = OBJECT_ID('PARTSUPP')

 

image

この情報については、「UPDATE STATISTICS WITH FULLSCAN」を実行しても修正は行われないようで、正確な値に修正を行うためには、「インデックスの再構築 (REBUILD) 」を実行することで、実際の値に修正されるようです。

 

本投稿のような内容を知っておくと、一つのクエリで様々な実行プランのバリエーションを持つクエリを生成することができたりもします。

image

 

 

update_stats_stream_option は実データを持たない検証環境を作成し、推定実行プランによる検証を実行するために使用するのが目的ですが、動作をある程度把握しておくと、実データと統計情報の乖離が発生した際の挙動の調査にも使うことができるのではないでしょうか。

Written by Masayuki.Ozawa

9月 9th, 2020 at 9:06 pm

Leave a Reply