SE の雑記

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

統計情報の自動更新が発生している際に同一の統計情報を使用するクエリはどのような動作になるのだろう

without comments

タイトルの内容が気になったので少し動作を確認してみました。

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

image

該当のインデックスについては、 100% のサンプリングでサンプルレートが永続化するようにしていますので、統計情報の自動更新が発生した場合には、全データを使用した統計情報の作成が行われます。

これは統計情報の作成に時間がかかるということにつながります。

統計情報の自動更新が発生している場合に、統計情報の自動更新の発生のトリガーとなったクエリの実行時間が伸びるので、該当の統計情報を使用しているクエリに影響を与えやすくなります。

それでは統計情報の自動更新を発生させるため、次のクエリを実行して「L_SHIPDATE」を更新します。

SET ROWCOUNT 1300000
UPDATE LINEITEM SET L_SHIPDATE = DATEADD(dd,1,L_SHIPDATE)
GO

 

先ほどの統計情報の状況を確認するクエリを実行してみると、該当の統計情報の変更カウンター (modification_counter) がカウントアップ (0 -> 1,300,000 ) されていることが確認できますね。

image

これで、統計情報の自動更新が発生する状態になりました。

最初に冒頭に記載したクエリを実行します。

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 のバージョンによってこのような機能の活用ができるようになりますので、気になる動作があった時の確認もしやすくなるかと。

Written by Masayuki.Ozawa

3月 29th, 2020 at 10:38 pm

Posted in SQL Server

Tagged with

Leave a Reply