#Azure Synapse Analytics SQLプールでも統計情報は大切です
一般的なRDBMS製品同様に、Azure Synapse Analyticsでも統計情報は非常に大切です。
どれぐらい大事な物かといいますと、性能問題が発生した時に「統計情報は最新ですか？」とまず聞かれてしまうぐらい大事なものになります。（これはOracleでもPostgreSQLでもおんなじだと思います。）
さて今回はAzure Synapse Analytics SQLプールの統計情報に関して確認してみました。
#統計情報の作成は自動/更新は手動
Azure Synapse Analytics SQLプールでは自動統計情報の取得機能が実装されております。
また、この自動統計情報の取得機能ですが、デフォルトで有効となっております。
一方で、SQL Serverなどで実装されている__統計情報の自動更新機能はAzure Synapse Analyticsでは実装されていない__ようです。
これら機能は以下のSQLで確認できます。
--統計の自動機能の確認
--1は機能がON、0は機能がOFF
select
name
,is_auto_create_stats_on
,is_auto_update_stats_on
from sys.databases order by name;
結果
また、統計情報の自動取得機能のON、OFFは以下のコマンドで実行します。
（これはONしておくことが推奨です。）
--機能のON
ALTER DATABASE <データベース名>
SET AUTO_CREATE_STATISTICS ON;
--機能のOFF
ALTER DATABASE <データベース名>
SET AUTO_CREATE_STATISTICS OFF;
ちなみに統計情報の自動更新機能を試しに有効化してみましたが、以下のようなエラーとなりました。
##統計情報の自動取得されるタイミング
統計情報の自動取得機能が有効な場合に、統計情報が自動で取得されますが、これがいつ取得されるかと言いますと、以下のようなSQLが実行されると、対象のテーブルの対象の列に統計情報が無かった場合に自動で取得されます。
・SELECT
・INSERT-SELECT
・CTAS
・UPDATE
・DELETE
・EXPLAIN
また、単純に上記のSQLが実行されるだけではなく、そのSQLに__結合や、述語が含まれること__で自動統計情報の取得が開始されます。
実際の動きを見てきます。lineitemと言うテーブルを使って確認します。
###準備１：統計情報の確認用のVIEWを作成
統計情報を確認するためのVIEWを最初に作成します。あっても無くてもいいですが、作成した方が楽です。
IF OBJECT_ID('vstats_columns') IS NOT NULL
DROP view vstats_columns
GO
CREATE VIEW dbo.vstats_columns
AS
SELECT
sm.[name] AS [schema_name]
, tb.[name] AS [table_name]
, st.[name] AS [stats_name]
, st.[filter_definition] AS [stats_filter_definition]
, st.[has_filter] AS [stats_is_filtered]
, STATS_DATE(st.[object_id],st.[stats_id])
AS [stats_last_updated_date]
, co.[name] AS [stats_column_name]
, ty.[name] AS [column_type]
, co.[max_length] AS [column_max_length]
, co.[precision] AS [column_precision]
, co.[scale] AS [column_scale]
, co.[is_nullable] AS [column_is_nullable]
, co.[collation_name] AS [column_collation_name]
, QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS two_part_name
, QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS three_part_name
FROM sys.objects AS ob
JOIN sys.stats AS st ON ob.[object_id] = st.[object_id]
JOIN sys.stats_columns AS sc ON st.[stats_id] = sc.[stats_id]
AND st.[object_id] = sc.[object_id]
JOIN sys.columns AS co ON sc.[column_id] = co.[column_id]
AND sc.[object_id] = co.[object_id]
JOIN sys.types AS ty ON co.[user_type_id] = ty.[user_type_id]
JOIN sys.tables AS tb ON co.[object_id] = tb.[object_id]
JOIN sys.schemas AS sm ON tb.[schema_id] = sm.[schema_id]
WHERE 1=1
GO
###準備２：テーブルの作成とデータの投入
lineitemテーブルを作成し、lineitemテーブルへデータをロードしましたが、この状態ではまだ統計情報は自動取得されません。
--テーブルの作成
CREATE TABLE LINEITEM
(
L_ORDERKEY INTEGER NOT NULL,
L_PARTKEY INTEGER NOT NULL,
L_SUPPKEY INTEGER NOT NULL,
L_LINENUMBER INTEGER NOT NULL,
L_QUANTITY DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
L_DISCOUNT DECIMAL(15,2) NOT NULL,
L_TAX DECIMAL(15,2) NOT NULL,
L_RETURNFLAG CHAR(1) NOT NULL,
L_LINESTATUS CHAR(1) NOT NULL,
L_SHIPDATE DATE NOT NULL,
L_COMMITDATE DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE CHAR(10) NOT NULL,
L_COMMENT VARCHAR(44) NOT NULL
) WITH (
DISTRIBUTION = HASH (L_ORDERKEY),
CLUSTERED COLUMNSTORE INDEX
);
--COPYにてデータをロード
COPY INTO LINEITEM --ロードするテーブル名を指定
FROM 'https://<ストレージアカウント名>.blob.core.windows.net/<コンテナ名>/<フォルダ名>/*' --ロードするファイル指定
WITH (
FILE_TYPE = 'CSV' --ファイルフォーマット指定
,CREDENTIAL = (IDENTITY= 'Storage Account Key', SECRET='<ストレージアカウントキー>') --ストレージアカウントキーで認証
,FIELDTERMINATOR='|' --デリミタ（|）
,ROWTERMINATOR = '0x0A' --改行コード（LF）
);
ここで統計情報を確認しても列統計は自動では取得されていません。
select * from vstats_columns
where table_name = 'lineitem' and stats_last_updated_date is not null;
結果
###述語なしSQLの実行（自動で統計情報の取得はされない）
述語なしのSQLを実行しても自動で統計情報の取得はされません。
--SQLの実行（述語なし）
select top 10 YEAR(L_RECEIPTDATE) from lineitem;
--統計情報の確認
select * from vstats_columns where table_name = 'lineitem' and stats_last_updated_date is not null;
結果
###述語ありSQLの実行（自動で統計情報の取得が行われる）
述語あり（いわゆるWhere句やGroup Byを指定）のSQLを実行すると対象の列の統計情報が取得されます。
--SQLの実行(述語あり）
select YEAR(L_RECEIPTDATE) from lineitem
group by YEAR(L_RECEIPTDATE);
--統計の確認
select * from vstats_columns where table_name = 'lineitem' and stats_last_updated_date is not null;
結果
自動で取得される列統計は**WA_Sys<16進数の列ID>_<16進数のテーブルID>**となります。
また、以下のようなTPCHの1番クエリも実行してみましたが、統計情報は自動で取得されます。
--SQLの実行（述語あり）
/* TPC_H Query 1 - */
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;
--統計の確認
select * from vstats_columns where table_name = 'lineitem' and stats_last_updated_date is not null;
結果
ちなみに、統計情報が自動で取得されるのはクエリ実行前か後か確認してみると、クエリの実行前に取得されているようです。
以下のSQLでクエリの実行履歴を確認出来ますが、そこで推察可能です。
--SQLの実行履歴から統計情報の取得の確認
select * from sys.dm_pdw_exec_requests order by start_time desc;
なので、列統計の無い列に述語付きのSQLを実行した場合、統計情報の自動取得の影響で、ややクエリが遅くなる可能性があります。
また、既に必要な列統計が取得されている場合は、自動で統計情報が取得されることはありません。
#統計情報の更新
残念ながらAzure Synapse Analyticsでは統計情報の自動更新の機能はありません。
なので、統計情報は必要に応じて手動で更新する必要があります。
テーブルの中に日付列やTimestamp列が存在しており、ロードの度に新しい値が入ってくるような場合には、当該列はロードの度に更新する事が望ましいようです。
一方で、都道府県や国、地域のようなあまりデータの分布が変わらないような場合は、頻繁に取得する必要はありません。（一方でこれも新しい都道府県、国、地域のデータロードされた場合は更新する必要があります。）
統計情報の更新は、テーブル単位か、列統計単位での更新が可能なので、更新時間が問題にならなければテーブル単位でデータをロードする度に更新するのが良いように思います。
##統計情報の更新（テーブル単位）
以下の例ではlineitemテーブルの取得されているすべての列統計の更新を行います。
UPDATE STATISTICS dbo.lineitem;
##統計情報の更新（列統計単位）
以下の例ではlineitemテーブルの取得されている単体の列統計の更新を行います。
UPDATE STATISTICS dbo.lineitem(_WA_Sys_0000000D_5708E33C);
#統計情報の手動取得
もちろん統計情報は手動で取得する事も可能です。
単一列の列統計を取得する場合は、統計情報を取得するサンプリングの量をパーセンテージで指定することで取得そのものを高速にすることができます。（デフォルトのサンプリングサイズは20%で、10億件を超えるような大きなテーブルは2%に調整されます。）
一方、サンプリングサイズを指定すると、精度の低い統計情報となる可能性があります。精度が低い統計情報からは良いクエリプランが生成されない可能性もあります。基本はデフォルトのままで問題ありません。
##統計情報の手動取得（サンプルサイズ未指定）
以下の例ではlineitemテーブルのL_RECEIPTDATE列に列統計を取得しています。
サンプルサイズの指定をしていないので、デフォルトのサンプルサイズ20%で取得されます。
※linitemテーブルの件数が10億件以上の場合は2%。
CREATE STATISTICS stats_lineitem_L_RECEIPTDATE ON lineitem(L_RECEIPTDATE);
##統計情報の手動取得（サンプルサイズ50%指定）
以下の例ではlineitemテーブルのL_RECEIPTDATE列に列統計をサンプルサイズ50%で取得しています。
CREATE STATISTICS stats_lineitem_L_RECEIPTDATE ON lineitem(L_RECEIPTDATE) WITH SAMPLE 50 PERCENT;
##統計情報の手動取得（全行対象の取得）
以下の例ではlineitemテーブルのL_RECEIPTDATE列に列統計をすべての行を対象に取得しています。
CREATE STATISTICS stats_lineitem_L_RECEIPTDATE ON lineitem(L_RECEIPTDATE) WITH FULLSCAN;