SQL Server ベースのデータベースエンジンで統計情報の自動更新が発生した際の情報を取得するための方法についてまとめておきたいと思います。
Contents
統計情報の基本情報
ドキュメント
SQL Server の統計情報については次の情報を確認する機会が多いです。
これらの情報を確認することで SQL Server ベースの環境の統計情報の基本的な内容を把握することができます。
T-SQL からの情報取得
実際の統計情報がどのように格納されているか / どのタイミングで更新されたのかを T-SQL で確認したい場合には、次の情報を確認します。
- DBCC SHOW_STATISTICS (Transact-SQL)
- sys.dm_db_stats_histogram (Transact-SQL)
- sys.dm_db_stats_properties (Transact-SQL)
- sys.dm_db_incremental_stats_properties (Transact-SQL)
- STATS_DATE (Transact-SQL)
統計情報のヒストグラム / サンプリング状況 / 更新タイミングを確認する場合にはこれらの情報を組み合わせることで確認ができます。
統計情報の使用方法
統計情報は実行プラン (推定実行プラン含む) を生成する際の基数推定に使用される情報となります。
統計情報がどのように使用されるかについては、次の情報を確認します。
- カーディナリティ推定 (SQL Server)
- Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator
- SQL Server 2014’s new cardinality estimator (Part 1)
- 新しいカーディナリティ推定器での結合コンテインメントの前提条件により、クエリのパフォーマンスが低下する
統計情報更新時に取得されるロック
統計情報が更新されるタイミングでは、統計情報に対して Sch-M のロックが取得されるため、該当の統計情報を使用するクエリのコンパイルでロック競合が発生する可能性があります。
統計情報の非同期自動更新の挙動となりますが、統計情報更新中の同時実行性の低下については次の情報が参考となります。
統計情報の自動更新の情報の取得
拡張イベントを使用した統計情報の自動更新の情報の取得
統計情報の自動更新の情報ですが拡張イベントで「auto_stats」の情報を取得することで、統計情報の自動更新が発生した場合のイベントを取得することができます。
このイベントを取得することで統計情報の自動更新の発生タイミングを正確に確認することができるようになります。
統計情報の自動更新の発生ですが、統計情報の更新対象となる列を述語として含んだクエリでもシンプルなクエリでは更新が発生しない可能性があります。
そのような場合は、トレースフラグ 8757 を使用したクエリとして実行することでトリビアルプランの生成を抑制することができます。統計情報の自動更新を想定通り発生させられない場合は、次のようにトレースフラグを設定してクエリを実行してみることで、自動更新を誘発させることができる可能性があります。
SELECT TOP 1 L_COMMENT FROM LINEITEM WHERE L_PARTITION = 1 ORDER BY 1 ASC OPTION (QUERYTRACEON 8757)
統計情報の自動更新で実行されるクエリの把握
統計情報の更新が行われる際には、ヒストグラムを作成するためにデータのサンプリングが行われます。
この際、どのようなクエリが実行されているかについては「STATMAN」というキーワードでクエリの情報を取得することで確認ができます。
拡張イベントで取得する場合には、「rpc_completed」「sp_statement_completed」「sql_batch_completed」「sql_statement_completed」のイベントに対して、「sqlserver.sql_text」に「%STATMAN%」を含むクエリを取得することで、統計情報の自動更新で、どのようにデータのサンプリングが行われているかを確認することができます。
このようにイベントを取得すると、次のようなクエリが取得できます。(以下は増分統計が有効な場合の統計情報の自動更新のクエリのため、パーティションの指定が行われていますが、増分統計が無効な場合はパーティション番号が指定されずにクエリが実行されます)
-- FULL SCAN で統計情報が作成されている場合 SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [L_PARTITION] AS [SC0] FROM [dbo].[LINEITEM] WITH (READUNCOMMITTED) WHERE $PARTITION.[LINEITEM_PF]([L_PARTITION]) = 2 ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 16) -- サンプリングして統計情報が作成されている場合 SELECT StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM (SELECT [L_PARTITION] AS [SC0] FROM [dbo].[LINEITEM] TABLESAMPLE SYSTEM (7.474654e+01 PERCENT) WITH (READUNCOMMITTED) WHERE $PARTITION.[LINEITEM_PF]([L_PARTITION]) = 2) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 16)
統計情報を FULL SCAN / サンプリングしているかによってデータの取得方法が変わる (TABLESAMPLE 句によるサンプリングの有無) のですが、どのようなクエリによってヒストグラムを構成するために必要な情報が取得されているかを確認することができます。
ヒストグラムを取得する際には「READUNCOMMITTED」のトランザクション分離レベルで実行がされるため、データ取得中のロック競合の発生は抑えることができていますが、FULL SCAN の場合は、テーブルまたはパーティション全体が検索されていることが確認できます。