SE の雑記

先日投稿した SQL Server のスレッドおよびタスクのアーキテクチャ では、ベースとした、スレッドおよびタスクのアーキテクチャ ガイド の次の記載を引用しました。

最新の SQL Server / SQL Database であれば、これを確認するのはそれほど難しくありませんので書いておこうかと。

使用するのは以前の投稿に引き続きこちらのスクリプトです。

[System.Data.SqlClient.SqlConnection]::ClearAllPools()
$con = New-Object System.Data.SqlClient.SqlConnection("Server=localhost;Integrated Security=SSPI;database=tpch;Application Name=SQLTaskThread")
$con.Open()
Write-Host "Connection Open Compilete"
Read-Host
$sql = @"
/* TPC_H Query 2 - Minimum Cost Supplier */
SELECT TOP 100 S_ACCTBAL, S_NAME, N_NAME, P_PARTKEY, P_MFGR, S_ADDRESS, S_PHONE, S_COMMENT
FROM PART, SUPPLIER, PARTSUPP, NATION, REGION
WHERE P_PARTKEY = PS_PARTKEY AND S_SUPPKEY = PS_SUPPKEY AND P_SIZE = 15 AND
P_TYPE LIKE '%%BRASS' AND S_NATIONKEY = N_NATIONKEY AND N_REGIONKEY = R_REGIONKEY AND
R_NAME = 'EUROPE' AND
PS_SUPPLYCOST = (SELECT MIN(PS_SUPPLYCOST) FROM PARTSUPP, SUPPLIER, NATION, REGION
 WHERE P_PARTKEY = PS_PARTKEY AND S_SUPPKEY = PS_SUPPKEY
 AND S_NATIONKEY = N_NATIONKEY AND N_REGIONKEY = R_REGIONKEY AND R_NAME = 'EUROPE')
ORDER BY S_ACCTBAL DESC, N_NAME, S_NAME, P_PARTKEY
OPTION (MAXDOP 4)
"@
$cmd = $con.CreateCommand()
$cmd.CommandText = $sql
$cmd.CommandTimeout = 0
Write-Host "Start Execute NonQuery."
while($true){
    $cmd.ExecuteNonQuery() | Out-Null
}
$con.Close()
$con.Dispose()

 
このスクリプトを実行している最中に「実行中のクエリの情報」を確認してみます。

並列処理の最大限度 (MAXDOP) の制限は、要求ごとではなく、タスクごとに設定されます。 つまり、並列クエリの実行の間に、1 つの要求で複数のタスクを生成し、各タスクで MAXDOP の制限まで複数のワーカーを使用できます。 MAXDOP の詳細については、「max degree of parallelism サーバー構成オプションの構成」を参照してください。

DECLARE @session_id int
select @session_id = session_id from sys.dm_exec_sessions where program_name = 'SQLTaskThread'
select
	qp.session_id,
	qp.task_address,
	qp.physical_operator_name,
	qp.node_id,
	qp.thread_id,
	ot.task_state,
	qsx.query_plan
from
	sys.dm_exec_query_profiles as qp
	left join sys.dm_os_tasks as ot
		on ot.task_address = qp.task_address
	outer apply sys.dm_exec_query_statistics_xml(qp.session_id) as qsx
where
	qp.session_id = @session_id
	-- and ot.task_state = 'RUNNING'
order by
	node_id, thread_id

 
この取得方法は、SQL Server 2016 で追加された 軽量クエリ実行統計プロファイリング インフラストラクチャ という手法を用いたものです。

SQL Server 2019 / SQL Database であれば、デフォルトで使用することができます。
 
この仕組みが実装される前は、「実行中のクエリの情報取得」を負荷を低く実行するのは難しく、キャッシュの情報から追う必要があったのですが、軽量クエリ実行統計プロファイリング インフラストラクチャ (軽量なクエリプロファイリング) は「実行中のクエリの情報」を軽量に取得することができるものとなります。
スクリプトを実行している最中にクエリを実行して情報の取得を行うと、次のような結果を取得することができます。

それでは、この情報を読み解いていきましょう。
SQL Server のタスクですが、基本的な考え方としては、「実行プランのアイコン」を指すものと考えられるのではないでしょうか。
こちらがスクリプトから実行しているクエリの実行プランの一部ですが、各アイコンが 1 つのタスクであると考えることができます。

(関連する処理の一連の流れで、一つのタスクとなることもありますので、厳密には一つのアイコンが一つのタスクではなく、複数のアイコンが同一のタスクとなることもありますがイメージとしては、一つの処理 (アイコン) が一つのタスクのような形で考えてみるとわかりやすいかと)

これがわかると「並列処理の最大限度 (MAXDOP) の制限は、要求ごとではなく、タスクごとに設定されます。」の情報の確認方法もわかってきます。

赤枠が node_id = 2 のソート処理における使用されているスレッドの情報を示したものです。

このソート処理では「0～4」の 5 スレッドが使用されていることが確認できます。

それでは、実行プランの情報から詳細を確認してみましょう。

この画像が node_id = 2 の情報を示したものとなるのですが、thread_id = 1 ～ 4 の 4 スレッドで処理が行われたことが確認できますね。
このことから MAXDOP = 4 により、「一つのタスクが最大 4 スレッドで処理が行われた」ということが確認できます。
クエリの複雑性が増し、複数のテーブルからの情報の取得が必要となると、処理によっては複数テーブルの取得を並列で実行する必要が出てきます。
この時、各テーブルの情報取得はそれぞれが個別のタスクとして認識されることになりますので、各処理で MAXDOP を上限とした処理が行われることになります。
二つの処理 (タスク) が実行されており、MAXDOP が 4 なのであれば、 8 スレッドが並列して実行されている可能性があるということですね。
MAXDOP を設定することで、タスクの並列度を調整し、特定のクエリによって、過度に CPU を使用することを抑えることができます。
この時に、「MAXDOP はタスクについての並列度を制限している」ということを意識できていると、「MAXDOP を設定していも、特定のクエリによって CPU が過度に使用されているのはなぜか？」についての調査の一助となることがありますので「タスク」という単位での並列度の制御を行っているということは覚えておくと良いのではないでしょうか。