SE の雑記

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

SQL Server のスレッドおよびタスクのアーキテクチャ

without comments

スレッドおよびタスクのアーキテクチャ ガイド というドキュメントがあります。

このドキュメントでは SQL Server のスレッドやタスクについての解説が行われています。
この投稿では、DMV を使用しながらこのドキュメントに記載されている内容を見ていきたいと思います。

今回は次のようなスクリプトを使って確認してみます。

[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
"@
$cmd = $con.CreateCommand()
$cmd.CommandText = $sql
$cmd.CommandTimeout = 0
Write-Host "Start Execute NonQuery."
while($true){
    $cmd.ExecuteNonQuery() | Out-Null
}
$con.Close()
$con.Dispose()

 
接続~クエリの実行を行う単純なスクリプトですがこれを動作しながら DMV を使用して SQL Server のタスク / スレッドについて確認を行っていきます。

SQL Server への接続とセッションの確立

最初に SQL Server の接続を行っています。
これらについては次の DMV で情報が確認できます。

アプリケーションから、SQL Server に接続を行うと、

  • 接続
  • セッション

が割り当てられることになります。
これらが SQL Server に対してアクセスを行う際に割り当てられる最初の情報となります。
image

次のクエリを実行することで DMV から情報を確認することができます。

DECLARE @session_id int
select @session_id = session_id from sys.dm_exec_sessions where program_name = 'SQLTaskThread'
select 'session' as type, * from sys.dm_exec_sessions where program_name = 'SQLTaskThread'
select 'connection' as type, * from sys.dm_exec_connections where session_id = @session_id

 
image
セッションとタスクが割り当てられていることが確認できますね。
スクリプトではクエリは実行せずに Open した直後に停止をしています。
このタイミングではクエリを実行していませんので、SQL Server の処理の実行単位である「タスク」については実行されていませんので、処理を進めてみたいと思います。

実行要求とタスクの割り当て

SQL Server でクエリの実行を行う際には要求に対してタスクが生成されて処理が行われます。。
これらの情報については次の DMV で確認ができます。

image
SQL Server で実行中の要求を見るときには「sys.dm_exec_requests」から確認することは定番ではないでしょうか。

「処理が実行されている = タスク」が処理されているということになります。
この「タスク」の実行状況を確認することが「sys.dm_os_tasks」となります。
sys.dm_exec_requests は、セッションと 1 対 1 の情報となっています。
クエリを実行する際には様々なテーブルから情報の取得を行い、その情報を加工することで結果が取得されます。

要求としては「この SQL を実行してください」という 1 つの要求ですが、その要求を実行するためには、単一または複数の処理 (タスク) が実行されることになります。
この処理の状況を確認するものが「sys.dm_os_tasks」となります。
それでは DMV を使用して確認してみましょう。

DECLARE @session_id int
select @session_id = session_id from sys.dm_exec_sessions where program_name = 'SQLTaskThread'
select 'session' as type, * from sys.dm_exec_sessions where program_name = 'SQLTaskThread'
select 'connection' as type, * from sys.dm_exec_connections where session_id = @session_id
DECLARE @task_address varbinary(8)
select 'request' as type, * from sys.dm_exec_requests where session_id= @session_id
select @task_address = task_address from sys.dm_os_tasks where session_id = @session_id
select 'task' as type, * from sys.dm_os_tasks where session_id = @session_id

image
先ほどのセッションに対してタスクが実行されていることが確認できますね。

ワーカーの割り当て

タスクというのはクエリの実行する際の処理単位であり、タスクを実行するためには「ワーカー」が必要となります。

SQL Server を取り扱ったことがある人であれば、「ワーカースレッド」という言葉の方がなじみが深いかもしれません。
SQL Server では、処理を実行する際にはワーカースレッドの割り当てが必要となります。

つまり「実行されているタスクにはワーカーが割り当てられている」ということになります。
この情報については次の DMV で確認できます。

image
接続を Open した直後の状態では、リクエスト / タスクが生成されていませんので、ワーカーも割り当てられていなかったのですが、実際にクエリが実行されると、実行されている処理に対してワーカーが割り当てられます。

DECLARE @session_id int
select @session_id = session_id from sys.dm_exec_sessions where program_name = 'SQLTaskThread'
select 'session' as type, * from sys.dm_exec_sessions where program_name = 'SQLTaskThread'
select 'connection' as type, * from sys.dm_exec_connections where session_id = @session_id
DECLARE @task_address varbinary(8)
select 'request' as type, * from sys.dm_exec_requests where session_id= @session_id
select @task_address = task_address from sys.dm_os_tasks where session_id = @session_id
select 'task' as type, * from sys.dm_os_tasks where session_id = @session_id
DECLARE @worker_address varbinary(8)
select 'worker' as type, * from sys.dm_os_workers where task_address in(
	select task_address from sys.dm_os_tasks where session_id = @session_id
)

 
実行されているタスクに対してワーカーが割り当てられていることが確認できます。
image
このワーカーが「ワーカースレッド」ということになります。

ワーカースレッドには「scheduler_address」も認識ができるようになっており、これが SQL Server が内部で持っている SQL OS のスケジューラーとなります。
ここまでの対応が理解できていると次のようなクエリの意味が分かってきます。

SELECT
	os.cpu_id,
	T.scheduler_address,
	T.worker_count,
	os.current_workers_count
FROM
(
	SELECT
		scheduler_address,
		COUNT(*) AS worker_count
	FROM
		sys.dm_os_workers AS ow
	GROUP BY
		ow.scheduler_address
) AS T
INNER JOIN sys.dm_os_schedulers AS os
	ON os.scheduler_address = T.scheduler_address
		AND os.status = 'VISIBLE ONLINE'

 
ワーカーをベースにして、各スケジューラーが認識している現在のワーカー数を出力しているものになるのですが、数値が一致していることが確認できますね。
image
 

OS スレッド

ここまでの内容は「SQL Server のプロセス内」の処理の認識であると言えます。

クエリが実際に実行されるためには、先ほど確認したワーカーが OS のスレッドとして実行される必要があります。
このような「SQL Server が認識しているワーカーと OS が認識しているスレッドの情報」についても DMV で確認ができるようになっています。

image
sys.dm_os_threads は OS のスレッドと SQL Server のワーカーをマッピングするための情報となります。

DECLARE @session_id int
select @session_id = session_id from sys.dm_exec_sessions where program_name = 'SQLTaskThread'
select 'session' as type, * from sys.dm_exec_sessions where program_name = 'SQLTaskThread'
select 'connection' as type, * from sys.dm_exec_connections where session_id = @session_id
DECLARE @task_address varbinary(8)
select 'request' as type, * from sys.dm_exec_requests where session_id= @session_id
select @task_address = task_address from sys.dm_os_tasks where session_id = @session_id
select 'task' as type, * from sys.dm_os_tasks where session_id = @session_id
DECLARE @worker_address varbinary(8)
select 'worker' as type, * from sys.dm_os_workers where task_address in(
	select task_address from sys.dm_os_tasks where session_id = @session_id
)
select 'thread' as type, * from sys.dm_os_threads where worker_address in(
	select worker_address from sys.dm_os_workers where task_address in(
		select task_address from sys.dm_os_tasks where session_id = @session_id
	)
)

このようなクエリで OS のスレッドとしてはどのように認識されているかを確認することができます。
image
こちらが実際に取得した情報となります。

「os_thread_id」として「15372」が使用されていることが確認できますね。

この情報は SQL Server のプロセス内の実際のスレッド ID と合致する情報となっています。
ここまでの情報の関連性が理解できていると、

  • アプリケーションから実行されたクエリは Session_Id = 94 で実行されている
  • 情報を取得したタイミングでは 1 つのワーカーが使用されており、そのワーカーは OS 上のスレッドとしては 15372 として実行されている

というような情報の関連性もわかるようになります。
 

MAXDOP とワーカーの関係

冒頭で紹介してドキュメントには次のような記載があります。

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

 
SQL Server で使用される CPU を制御するための設定として「max degree of parallelism」という設定がありますが、この設定はここまで見てきた情報の中の「1 つのタスクについての CPU 使用数の上限を制御」するというものになります。
先ほどまでのクエリでは「OPTION (MAXDOP 1)」を指定していたのですが、これを「OPTION (MAXDOP 2)」にしてみたいと思います。
先ほどまでの MAXDOP の状態では、情報を取得したタイミグでは、1 つのタスクしか実行されていませんでしたが、MAXDOP を変更することで 10 個のタスクが実行されているタイミングが確認できました。

image
次のようなクエリを実行して情報を取得してみます。

DECLARE @session_id int
select @session_id = session_id from sys.dm_exec_sessions where program_name = 'SQLTaskThread'
select 'task' as type, exec_context_id,task_state as cnt
from sys.dm_os_tasks where session_id = @session_id
order by exec_context_id asc

 
MAXDOP = 2 のため、同一の context_id で動作しているタスクが 2 存在しているものがあり、実行中のタスクとしては、同時に 2 以上のタスクが RUNNING になっていることが確認できます。
image
このことから、各タスク (イメージとしては実行プランのアイコンの各処理) は MAXDOP の設定値を上限として動作していますが、同時に実行されるタスクは複数あるため、結果として 2 コア以上が使用されるということが確認できるのではないでしょうか。
SQL Server はソースコードが公開されていないため、詳細な動作について利用者が確認できる範囲は OSS ベースの RDBMS と比較して少ないのは事実だと思います。

(海外だと windbg で接続して確認されている方もいますが)
しかし、DMV の情報を組み合わせてみると、ドキュメントに書かれている内容について、本投稿のような確認までは落とし込むことができます。
ドキュメントを読むだけでなく、「ドキュメントで書かれている内容がどの DMV の情報から確認することができるか」を考えると一歩進んだ SQL Server の学習をすることができます。
公式ドキュメントのアーキテクチャの内容を手を動かしながら調べてみるというのは面白いのではないでしょうか。

Written by Masayuki.Ozawa

2月 29th, 2020 at 10:01 pm

Leave a Reply