タイトルの内容が気になったので少し動作を確認してみました。
TPC-H の Q1 のクエリが元になるデータが大きく、適度な時間なクエリだったので今回は次のクエリを使用しています。
USE tpch_1GB; SET STATISTICS TIME ON; SELECT L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY) AS SUM_QTY, SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE, AVG(L_QUANTITY) AS AVG_QTY, AVG(L_EXTENDEDPRICE) AS AVG_PRICE, AVG(L_DISCOUNT) AS AVG_DISC, COUNT(*) AS COUNT_ORDER FROM LINEITEM WHERE L_SHIPDATE <= dateadd(dd, -90, cast('1998-12-01' as datetime)) GROUP BY L_RETURNFLAG, L_LINESTATUS ORDER BY L_RETURNFLAG,L_LINESTATUS GO
TPC-H の 1GB のスケールのデータを使用して、「SET STATISITCS TIME ON」で実行時に処理時間の統計を出力するようにしておきます。
このクエリでは、条件として「L_SHIPDATE」が使用されていますので、この統計情報が更新された際には、更新処理に時間がかかるようにするため、次のクエリを実行しておきます。
CREATE INDEX NCIX_L_SHIPDATE ON LINEITEM (L_SHIPDATE) GO UPDATE STATISTICS LINEITEM(NCIX_L_SHIPDATE) WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON GO
実行している内容は、L_SHIPDATE にインデックスを作成し、そのインデックス統計に「FULLSCAN」「PERSIST_SAMPLE_PERCENT = ON」を指定して、統計情報の更新を行います。
(列統計でも対応できるはずですが、今回はクエリの実行効率を向上させたかったのでインデックスを付与しています)
このクエリで作成された統計情報の状態を次のクエリで確認してみます。
select object_name(s.object_id) as object_name, s.name, s.has_persisted_sample, s.stats_generation_method_desc, sp.last_updated, sp.rows, sp.rows_sampled, sp.modification_counter, sp.persisted_sample_percent from sys.stats as s outer apply sys.dm_db_stats_properties(object_id, stats_id) as sp where s.object_id = OBJECT_ID('LINEITEM') GO
該当のインデックスについては、 100% のサンプリングでサンプルレートが永続化するようにしていますので、統計情報の自動更新が発生した場合には、全データを使用した統計情報の作成が行われます。
これは統計情報の作成に時間がかかるということにつながります。
統計情報の自動更新が発生している場合に、統計情報の自動更新の発生のトリガーとなったクエリの実行時間が伸びるので、該当の統計情報を使用しているクエリに影響を与えやすくなります。
それでは統計情報の自動更新を発生させるため、次のクエリを実行して「L_SHIPDATE」を更新します。
SET ROWCOUNT 1300000 UPDATE LINEITEM SET L_SHIPDATE = DATEADD(dd,1,L_SHIPDATE) GO
先ほどの統計情報の状況を確認するクエリを実行してみると、該当の統計情報の変更カウンター (modification_counter) がカウントアップ (0 -> 1,300,000 ) されていることが確認できますね。
これで、統計情報の自動更新が発生する状態になりました。
最初に冒頭に記載したクエリを実行します。
USE tpch_1GB; SET STATISTICS TIME ON; SELECT L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY) AS SUM_QTY, SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE, AVG(L_QUANTITY) AS AVG_QTY, AVG(L_EXTENDEDPRICE) AS AVG_PRICE, AVG(L_DISCOUNT) AS AVG_DISC, COUNT(*) AS COUNT_ORDER FROM LINEITEM WHERE L_SHIPDATE <= dateadd(dd, -90, cast('1998-12-01' as datetime)) GROUP BY L_RETURNFLAG, L_LINESTATUS ORDER BY L_RETURNFLAG,L_LINESTATUS GO
このクエリを実行している最中に該当の統計情報を使用するクエリを再コンパイルされる形で実行してみます。
USE tpch_1GB; SET STATISTICS TIME ON; SELECT L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY) AS SUM_QTY, SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE, AVG(L_QUANTITY) AS AVG_QTY, AVG(L_EXTENDEDPRICE) AS AVG_PRICE, AVG(L_DISCOUNT) AS AVG_DISC, COUNT(*) AS COUNT_ORDER FROM LINEITEM WHERE L_SHIPDATE <= dateadd(dd, -90, cast('1998-12-01' as datetime)) GROUP BY L_RETURNFLAG, L_LINESTATUS ORDER BY L_RETURNFLAG,L_LINESTATUS OPTION(RECOMPILE) GO
それでは、クエリで使用された統計情報を確認してみます。
最初のクエリで使用された統計情報は次のような情報となっています。
(何回かリトライをしているので、時間については上記の画像の時間とは異なっています。)
<OptimizerStatsUsage> <StatisticsInfo Database="[tpch_1GB]" Schema="[dbo]" Table="[LINEITEM]" Statistics="[NCIX_L_SHIPDATE]" ModificationCount="0" SamplingPercent="100" LastUpdate="2020-03-29T21:29:05.33" /> <StatisticsInfo Database="[tpch_1GB]" Schema="[dbo]" Table="[LINEITEM]" Statistics="[PK_LINEITEM]" ModificationCount="0" SamplingPercent="0.94066" LastUpdate="2020-03-29T21:09:42.45" /> <StatisticsInfo Database="[tpch_1GB]" Schema="[dbo]" Table="[LINEITEM]" Statistics="[_WA_Sys_0000000A_3C69FB99]" ModificationCount="0" SamplingPercent="0.937327" LastUpdate="2020-03-29T21:09:43.6" /> <StatisticsInfo Database="[tpch_1GB]" Schema="[dbo]" Table="[LINEITEM]" Statistics="[_WA_Sys_00000004_3C69FB99]" ModificationCount="0" SamplingPercent="2.27951" LastUpdate="2020-03-29T21:22:47.22" /> <StatisticsInfo Database="[tpch_1GB]" Schema="[dbo]" Table="[LINEITEM]" Statistics="[_WA_Sys_00000009_3C69FB99]" ModificationCount="0" SamplingPercent="0.937327" LastUpdate="2020-03-29T21:09:42.77" /> </OptimizerStatsUsage>
L_SHIPDATE の統計情報は「LastUpdate=”2020-03-29T21:29:05.33″」となっていますね。
それでは、後から実行したクエリはどうでしょうか。
<OptimizerStatsUsage> <StatisticsInfo Database="[tpch_1GB]" Schema="[dbo]" Table="[LINEITEM]" Statistics="[NCIX_L_SHIPDATE]" ModificationCount="1300000" SamplingPercent="100" LastUpdate="2020-03-29T21:26:26.95" /> <StatisticsInfo Database="[tpch_1GB]" Schema="[dbo]" Table="[LINEITEM]" Statistics="[PK_LINEITEM]" ModificationCount="0" SamplingPercent="0.94066" LastUpdate="2020-03-29T21:09:42.45" /> <StatisticsInfo Database="[tpch_1GB]" Schema="[dbo]" Table="[LINEITEM]" Statistics="[_WA_Sys_0000000A_3C69FB99]" ModificationCount="0" SamplingPercent="0.937327" LastUpdate="2020-03-29T21:09:43.6" /> <StatisticsInfo Database="[tpch_1GB]" Schema="[dbo]" Table="[LINEITEM]" Statistics="[_WA_Sys_00000004_3C69FB99]" ModificationCount="0" SamplingPercent="2.27951" LastUpdate="2020-03-29T21:22:47.22" /> <StatisticsInfo Database="[tpch_1GB]" Schema="[dbo]" Table="[LINEITEM]" Statistics="[_WA_Sys_00000009_3C69FB99]" ModificationCount="0" SamplingPercent="0.937327" LastUpdate="2020-03-29T21:09:42.77" /> </OptimizerStatsUsage>
「LastUpdate=”2020-03-29T21:26:26.95″」となっていますね。
最初に実行されたクエリで L_SHIPDATE の統計情報は更新をしている最中に実行されているはずのクエリですが、該当の統計情報を更新されている最中については、更新前の統計情報を使用して実行されているようですね。
SQL Server 2019 では「WAIT_ON_SYNC_STATISTICS_REFRESH」という待ち事象が増えていますので、この待ち事象を確認することで、統計情報の自動更新が同期的に実行された際の待機状態を確認することができます。
この待機事象が多い場合には DB オプションの「AUTO_UPDATE_STATISTICS_ASYNC」(統計情報の非同期更新) を有効化することで、統計情報の更新のトリガーとなったクエリ実行の最適化の効率と引き換えに統計情報の自動更新によるクエリ実行時間への影響を抑えることができるようになります。
- 統計情報のサンプリングの固定 : SQL Server 2016 SP1 CU4 以降
- 実行プランに使用された統計情報を出力 : 互換性レベル 120 (SQL Server 2014 以降)
- WAIT_ON_SYNC_STATISTICS_REFRESH による統計情報の自動更新同期実行の取得 : SQL Server 2019 以降
SQL Server のバージョンによってこのような機能の活用ができるようになりますので、気になる動作があった時の確認もしやすくなるかと。