最近、SQL Server の基本動作をいろいろと再勉強しているのですが、その中で統計情報のドキュメントを一通り読み直していました。
TPC-H の LINEITEM を使用して、いくつかのパターンで統計情報のメンテナンスを手動で実施しながら動作の確認を行った際のメモを。
統計情報の手動メンテナンスですが、次の 2 つの処理により実施を行うことが多いのではないでしょうか?
統計情報の情報は、次のようなクエリで確認をすることができます。
SELECT OBJECT_NAME(s.object_id) AS object_name, s.name, s.stats_id, isp.partition_number, STATS_DATE(s.object_id, s.stats_id) AS stats_date, CAST(sp.last_updated AS datetime2(3)) AS last_updated, sp.modification_counter, sp.steps, sp.rows, sp.rows_sampled, CAST(sp.rows_sampled * 1.0 / sp.rows * 100 as numeric(5,2)) AS sample_percent, sp.persisted_sample_percent, s.is_incremental, isp.rows, isp.rows_sampled, CAST(isp.rows_sampled * 1.0 / isp.rows * 100 as numeric(5,2)) AS sample_percent, isp.steps, CAST(isp.last_updated AS datetime2(3)) AS last_updated, isp.modification_counter FROM sys.stats AS s OUTER APPLY sys.dm_db_stats_properties(s.object_id , s.stats_id) AS sp OUTER APPLY sys.dm_db_incremental_stats_properties(s.object_id , s.stats_id) AS isp WHERE OBJECT_SCHEMA_NAME(s.object_id) <> 'sys' AND s.auto_created = 0 AND s.object_id = OBJECT_ID('LINEITEM') GO DBCC SHOW_STATISTICS('LINEITEM', 'PK_LINEITEM') DBCC SHOW_STATISTICS('LINEITEM', 'PK_LINEITEM') WITH STATS_STREAM GO
DMV を使用することで統計情報のサンプリング等の情報を取得することができ、DBCC SHOW_STATISTICS を使用することで、統計情報のヒストグラムの詳細を確認することができます。
非パーティションテーブルの統計情報
デフォルトのオプションでインデックスを作成しており、インデックスの再構築を行うと、統計情報のサンプリングは 100% のデータを使用して最新化されることになります。
このような動作のため、インデックスの再構築を実施した直後に、統計情報の最新化を行うということは不要です。
では、次のようなクエリで、統計情報を最新化してみます。
UPDATE STATISTICS LINEITEM (PK_LINEITEM)
UPDATE STATISTICS をオプションを設定せずに実行した場合、サンプリングレートは既定のサンプリングとなります。
そのため、再構築を実行した直後に、UPDATE STATISTICS をサンプリングレートを指定せずに実行すると、サンプリングされたデータを元にして作成された統計情報となるため、統計と実データの乖離が発生する可能性があります。
手動で統計情報を更新する場合は、次のように「WITH FULLSCAN」を指定することで、すべてのデータを使用した (該当のインデックスをフルスキャンした) 統計情報を作成することができます。
(FULLSCAN ではなく、サンプリングレートを指定することもできます)
UPDATE STATISTICS LINEITEM (PK_LINEITEM) WITH FULLSCAN
既定のサンプリングレートは統計情報の自動更新の際にも使用されるため、インデックスの再構築後に統計情報の自動更新が行われると、100% のサンプリングではなく、既定のサンプリングレートによって作成された統計情報に更新が行われます。
SQL Server 2016 SP1 / SQL Server 2017 CU1 以降では、既定のサンプリングレートではなく「固定サンプリングレート」を使用するための「PERSIST_SAMPLE_PERCENT」というオプションが追加されました。
これらのバージョン以降では、ALTER INDEX や UPDATE STATISTICS で次のようなオプションを指定することができます。
UPDATE STATISTICS LINEITEM (PK_LINEITEM) WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON
固定サンプリングレートを適用してから、手動で統計情報を更新してみます。
UPDATE STATISTICS LINEITEM (PK_LINEITEM)
先ほどは、FULLSCAN でない統計情報の更新を実行した場合は、「0.57 %」のサンプリングとなっていましたが、固定サンプリングレートを有効化した場合は、「100 %」のサンプリングでの更新となっています。
最新の SQL Server では、非パーティションテーブルの統計情報の手動更新は固定サンプリングレート等を使用することで、調整をすることができるようになっています。
パーティションテーブルの統計情報
それではパーティションテーブルの統計情報の更新はどのようになるでしょうか?
デフォルトの設定で統計情報を作成すると次のようになります。
この情報はパーティション化した直後や、インデックスの再構築を実行した後の状態となるのですが、パーティション化されているテーブル / インデックスの統計情報はインデックスのメンテナンスの実行では、既定のサンプリングレートによって作成されたものになります。
(パーティション番号を使用したインデックスの再構築でも同様の動作となります。)
UPDATE STATISTICS LINEITEM (PK_LINEITEM) WITH FULLSCAN
明示的に統計情報の更新を FULLSCAN で実行することで、100% のサンプリングレートによって作成された統計情報として作成されます。
パーティション化したテーブル / インデックスの統計情報のメンテナンスについては、インデックスの再構築ではなく、明示的な手動の統計情報更新を実行することで、100% のサンプリングで統計情報を作成することができます。
SQL Server 2016 SP1 CU4 / SQL Server 2017 CU1 以降は、「ON PARTITIONS」というオプションが使用できるようになっています。
UPDATE STATISTICS LINEITEM (PK_LINEITEM) WITH RESAMPLE ON PARTITIONS(2)
これにより、特定のパーティションについての統計情報を再計算して、全体の統計にマージするということで、統計情報のメンテナンスを行うこともできるようになっています。
SQL Server 2014 以降は統計情報に「INCREMENTAL」という増分統計のオプションが指定できるようになっています。
UPDATE STATISTICS LINEITEM (PK_LINEITEM) WITH FULLSCAN, INCREMENTAL = ON
このオプションを指定して統計情報の作成を行うことで、パーティション単位で統計情報管理することができるようになります。
増分統計を使用しても、全体統計へのマージは発生しますので、全体統計の更新も行われているのですが、統計情報についてはパーティション単位でも管理されるようになります。
パーティショニングしたテーブルについては、パーティション単位でインデックスの再構築を実行することができますが、増分統計を使用してもパーティション単位の統計情報は更新されていないようですので、パーティション単位でインデックスを再構築した場合は、パーティション単位で統計情報の再構築を実施する必要性は検討した方が良いかと思います。
テーブル全体のインデックスの再構築を行ってもサンプリングレートについては既定のサンプリングとなりますので、REBUILD 時に統計情報のサンプリングレートは低下します。
固定サンプリングレートを設定していても、パーティションテーブルのインデックスを REBUILD すると、既定のサンプリングレートになっているようでしたので、再構築統計がどのようになているかは、確認した方が良いかと思います。
(固定サンプリングレートが外れているような動作になっていそうでしたので)
統計情報はオプティマイザが実行プランを作成する際に重要な情報となりますので、「今、どのような統計情報が使用されているか?」を気にしておくことは、SQL Server のパフォーマンスを保つために重要な要因となります。
[…] https://blog.engineer-memo.com/2020/02/02/sql-server-%e3%81%ae%e7%b5%b1%e8%a8%88%e6%83%85%e5%a0%b1%e… […]
【後で読みたい!】SQL Server の統計情報の手動メンテナンスについて | Tak's Bar
5 2月 20 at 23:22
[…] SQL Server の統計情報の手動メンテナンスについて at SE の雑記 (engineer-memo.com) […]
SQL Server / Azure SQLの統計情報に関するあれこれ | 煎茶
30 8月 22 at 01:50