Azure Synapse Analytics SQLプールにおけるクエリ実行時のリソース管理

ワークロードグループとワークロード分類子

Azure Synapse Analytics SQLプールではクエリ実行の際に、そのクエリにデータベースのどのくらいのリソースを割り当てるかを管理する必要があります。この管理の事をワークロード管理と言う呼び方をします。またここで言うリソースはCPUとメモリの事を指しており、ディスクに対するIOは制御できません。

ワークロード管理ではリソースだけでなくクエリの重要度も管理する必要があります。
クエリの重要度に関しては以下の記事を参考にしてください。
Azure Synapse Analytics SQLプールの実行クエリの重みづけ（重要度）

ワークロード管理の方法は大きく２つ存在します。
・リソースクラスを使用して管理
・ユーザで定義したワークロード管理

リソースクラスを使用して管理を行う場合には以下の記事を参考にしてください。
Azure Synapse Analytics SQLプールを高速にするリソースクラス

本記事ではユーザで定義したワークロード管理について記載します。
ユーザで定義したワークロード管理で利用するのは「ワークロードグループ」と「ワークロード分類子」です。
この２つの機能を使って、ワークロード管理を実現します。

ワークロードグループ

ワークロードグループはAzure Synapse Analytics SQLプールでクエリを実行する際の様々なリソースの割り当ての方法を制御します。
主に以下のような項目で設定を行います。

MIN_PERCENTAGE_RESOURCE(最小リソース(%))

このパラメータは%の値で指定します。このパラメータで指定した値でデータベースのメモリは、このワークロードグループ専用に確保されます。
CPUリソースはこのパラメータを指定したとしても、専用に確保されるわけではありません。

CAP_PERCENTAGE_RESOURCE (上限リソース(%))

このパラメータは%の値で指定します。このパラメータで指定した値まで、このワークロードグループ内のクエリはメモリを使う事が出来るようになります。
CPUリソースはこのパーセンテージを超えて利用される可能性がありますが、CPUリソースが競合している場合には、ワークロードグループのクエリはこのパラメータで定義した値まで調整されます。

REQUEST_MIN_RESOURCE_GRANT_PERCENT(要求ごとの最小リソース(%))

このパラメータは%の値で指定します。ワークロードグループ内で実行されるクエリ毎にどれだけの最小のリソース（メモリ）を割り当てるのかを指定します。
例えば、MIN_PERCENTAGE_RESOURCE（最小リソース）を30%に設定し、このパラメータの値を2%に設定したワークロードグループを作った場合、このワークロードグループでは15多重での同時実行が可能な状態をデータベース内で担保します。
※ただし、コンカレンシースロットの値はcDWU単位で異なるで、有効な値はcDWU毎に確認が必要です。

REQUEST_MAX_RESOURCE_GRANT_PERCENT(要求ごとの最大リソース(%))

このパラメータは%の値で指定します。ワークロードグループ内で実行されるクエリ毎にどれだけの最大のリソース（メモリ）を割り当てるのかを指定します。デフォルトはREQUEST_MIN_RESOURCE_GRANT_PERCENT(要求ごとの最小リソース(%))と同じ値で、設定値はこのREQUEST_MIN_RESOURCE_GRANT_PERCENT(要求ごとの最小リソース(%))の値よりも大きな値を指定する必要があります。

IMPORTANCE(重要度)

ワークロードグループで実行されるクエリの重要度を指定する事が出来ます。
以下の値を設定可能です。
・HIGH（高）
・ABOVE_NORMAL（標準以上）
・NORMAL（標準）※デフォルト
・BELOW_NORMAL（標準以下）
・LOW（低）
後述しますが、重要度はワークロード分類子でも指定する事が出来ます。両方で重要度が指定されている場合はこちらの値は無視されます。

QUERY_EXECUTION_TIMEOUT_SEC(クエリのタイムアウト時間)

ワークロードグループ内で実行されるクエリのタイムアウト時間を秒単位で指定します。デフォルトは0で、0を指定するとクエリはタイムアウトを起こさなくなります。
タイムアウトまでの時間はあくまでクエリが実行されでからの時間であり、キューに入って待機している状態はタイムアウトまでの時間としてカウントされません。

ワークロード分類子

ワークロードグループを作っただけでは、ワークロードグループは機能しません。ワークロード分類子を作成し、作成したワークロード分類子にワークロードグループを紐づける必要があります。
実行されるクエリはワークロード分類子で指定した条件が満たされた場合、そのワークロード分類子に紐づけられたワークロードグループの設定内容に従って実行されるという仕組みになります。
どのワークロード分類子の条件にも合致しなかった場合には、デフォルトのsmallrcリソースクラスでクエリは実行されます。

ワークロード分類子で指定可能な条件は以下の通りとなります。

MEMBERNAME

データベースユーザー、データベースロール、AADログイン/グループなどを指定します。
※必須で設定が必要な条件です。

WLM_LABEL

ラベル値の値を指定します。
クエリ内に以下のようなOPTION（LABEL=）の値を条件に分類されます。

--ワークロード分類子の作成
CREATE WORKLOAD CLASSIFIER wcLABELTEST WITH  
( WORKLOAD_GROUP = 'xlargerc'
 ,MEMBERNAME     = 'ADFLogin'
 ,WLM_LABEL      = 'label_test' )
;

--ADFLoginユーザかつ以下のようなOPTION(LABEL)のSQLでワークロード分類子の条件に合致
select count(*) from nation
OPTION(LABEL='label_test');

WLM_CONTEXT

セッションコンテキストの値を指定します。
クエリ実行前にsys. sp_set_session_contextを使用してセッションコンテキストを設定し、セッションコンテキストの値を条件に分類されます。

--ワークロード分類子の作成
CREATE WORKLOAD CLASSIFIER wcCONTEXTTEST WITH  
( WORKLOAD_GROUP = 'xlargerc'
 ,MEMBERNAME     = 'ADFLogin'
 ,WLM_CONTEXT    = 'context_test' )
;

--セッションコンテキストを設定&確認
EXEC sys.sp_set_session_context @key = 'wlm_context', @value = 'context_test';
SELECT SESSION_CONTEXT(N'wlm_context');

--ADFLoginユーザで上記の通りセッションコンテキストの値でワークロード分類子に合致
select count(*) from nation;

START_TIME と END_TIME

UTCタイムゾーンでHH:MM形式で指定します。指定した時間を条件にワークロード分類子の合致を判断します。

IMPORTANCE(重要度)

この値はワークロード分類子へ合致する条件を指定しているわけではなく、ワークロード分類子に合致したクエリの重要度を設定できます。
以下の値を設定可能です。
・HIGH（高）
・ABOVE_NORMAL（標準以上）
・NORMAL（標準）※デフォルト
・BELOW_NORMAL（標準以下）
・LOW（低）

ワークロード分類子の優先順位

ワークロード分類子をたくさん作成している場合、クエリを実行する際に、実行するクエリがいくつかのワークロード分類子に合致してしまう可能性があります。

CREATE WORKLOAD CLASSIFIER wcTEST1 WITH  
( WORKLOAD_GROUP = 'xlargerc'
 ,MEMBERNAME     = 'ADFLogin'
 ,WLM_LABEL      = 'label_test' )
;

CREATE WORKLOAD CLASSIFIER wcTEST2 WITH  
( WORKLOAD_GROUP = 'largerc'
 ,MEMBERNAME     = 'ADFLogin'
 ,WLM_CONTEXT    = 'context_test' )
;

--セッションコンテキストの設定
EXEC sys.sp_set_session_context @key = 'wlm_context', @value = 'context_test';

--クエリ実行（LABEL付）
select count(*) from nation
OPTION(LABEL='label_test');

上記の場合、最後に実行したクエリはセッションコンテキストの設定もあり、OPTION(LABEL)もクエリ内に指定しているので、
wcTEST1、wcTEST2両方の条件に合致してしまいます。

このように複数のワークロード分類子に合致した場合、選択されるワークロード分類子を決定するために、ワークロード分類子の各条件には「重みづけ」が設定されています。

分類子の条件パラメータ	重みづけ
User	64
ROLE	32
WLM_LABEL	16
WLM_CONTEXT	8
START_TIME/END_TIME	4

上記の例だとwcTEST1の重みづけは80（User:64+WLM_LABEL:16）、wcTEST2の重みづけは72（User:64+WLM_CONTEXT:8）となるため、wcTEST1が選択されるという仕組みになっています。

設定例

DW100cのAzure Synapse Analytics SQLプールを想定し、
バッチ実行用のワークロードグループwgELTとアドホックなクエリ用のワークロードグループwgADFを作成します。

--wgELTの作成
CREATE WORKLOAD GROUP wgELT
WITH
(
 MIN_PERCENTAGE_RESOURCE = 50
,CAP_PERCENTAGE_RESOURCE = 100
,REQUEST_MIN_RESOURCE_GRANT_PERCENT = 50
,REQUEST_MAX_RESOURCE_GRANT_PERCENT = 100
);

--wgADFの作成
CREATE WORKLOAD GROUP wgADF
WITH
(
 MIN_PERCENTAGE_RESOURCE = 0
,CAP_PERCENTAGE_RESOURCE = 50
,REQUEST_MIN_RESOURCE_GRANT_PERCENT = 25
,QUERY_EXECUTION_TIMEOUT_SEC = 600
);

--作成したワークロードグループの確認
SELECT * FROM sys.workload_management_workload_groups ORDER BY 1;

ワークロード分類子を作成し、上記で作成したワークロードグループをマッピングします。

--wcELTの作成とwcELTとwgELTをマッピング
CREATE WORKLOAD CLASSIFIER wcELT WITH  
( WORKLOAD_GROUP = 'wgELT'
 ,MEMBERNAME     = 'ELTLogin'
 ,IMPORTANCE     = HIGH
)
;

--wcADFの作成とwcADFとwgADFをマッピング
CREATE WORKLOAD CLASSIFIER wcADF WITH  
( WORKLOAD_GROUP = 'wgADF'
 ,MEMBERNAME     = 'ADFLogin'
)
;

--作成したワークロード分類子の確認
SELECT *
FROM sys.workload_management_workload_classifiers
ORDER BY 1;

また以下のコマンドでどのワークロードグループがつかわれているのか確認可能です。

--ワークロードグループの実行統計
SELECT * FROM sys.dm_workload_management_workload_groups_stats ORDER BY 1;

--クエリの重要度の確認
SELECT s.login_name, r.status, r.importance, r.submit_time, r.start_time
FROM sys.dm_pdw_exec_sessions s
JOIN sys.dm_pdw_exec_requests r ON s.session_id = r.session_id
WHERE r.resource_class is not null
ORDER BY r.start_time;