書こう、書こうと思っていながらかけていなかったのですが、What is MaxDOP controlling? という記事が公開されたので、良い機会と思い、CXPACKET が発生している実行中のクエリのリソース情報の取得についてまとめたいと思います。
「実行中のクエリの情報を取得」する場合、sys.dm_exec_requests から情報の取得をするのが定番です。
今回は特定の Session ID の情報を取得するので、次のようなクエリを実行して情報の取得を行ってみます。
SELECT er.session_id, er.status, er.command, er.wait_type, er.last_wait_type, er.wait_resource, er.cpu_time, er.total_elapsed_time, er.reads, er.logical_reads, er.writes, er.task_address FROM sys.dm_exec_requests AS er WHERE session_id = 98
最初に並列クエリとして実行されないパターンでクエリを実行し、実行中に上記のクエリで情報の取得を行います。
今回実行しているクエリは、並列クエリではない実行プランです。
exec_requests から情報を取得すると、実行中のクエリから、各種リソースの使用状況が取得できていることが確認できます。
それでは、このクエリを並列クエリとして実行してみます。
実行プランから、並列プランとして実行されていることが確認できます。
それでは、exec_requests の情報はどうでしょうか。
wait_type が CXPACKET となっているため、このリクエストは並列クエリとして実行されていることが確認できます。
情報を取得したタイミングでは、経過時間 (total_elapsed_time) が 7,525 ms であり、CPU 使用時間 (cpu_time) が 29,087 ms ということが確認できます。
経過時間より CPU 使用時間の方が長い場合は、経過時間に対して複数の CPU コアを使用して処理を実行していた傾向があるということが判断できます。
このクエリは大量の I/O が発生するクエリです。
cpu_time / total_elapsed_time は上昇していますが、reads / logical_reads / writes といったリソースの使用状況については、上昇していません。
並列クエリでない場合はクエリの進行状況に応じて、これらのリソースの使用状況もカウントアップされるのですが、並列クエリの場合は、「CXPACKET が完了したタイミングでカウントアップされる」というような形となります。
冒頭で紹介した MAXDOP の記事では、sys.dm_exec_requests については次のように解説が行われています。
A request is a logical representation of a query or batch an application sends to the Database Engine. Requests can be monitored through the sys.dm_exec_requests DMV, can be executed in parallel (multiple CPUs) or in a serial fashion (single CPU), have a state that reflects the state of underlying tasks, and accumulate waits when resources needed are not available like a page latch or row lock.
sys.dm_exec_requests はデータベースに対してのリクエストを表現したものとなり、要求に対しての情報を取得しています。
記事の中では「タスク」についても解説が行われています。
A task is a single unit of work that needs to be carried out for the request to be completed. A serial request will only have one active task, whereas a parallel request will have multiple tasks executing concurrently. Tasks can be monitored through the sys.dm_os_tasks and sys.dm_os_waiting_tasks DMVs, also have a state (running, runnable, or suspended) that reflects up to the owner request state.
リクエストを完了させるために、実行されている作業単位が「タスク」であり、リクエストはタスクによって処理が行われます。
sys.dm_exec_requests では、タスクのアドレスが「task_address」として取得できます。
それでは、タスクの情報を次のクエリで取得してみましょう。
SELECT ot.session_id, ot.parent_task_address, ot.task_address, ot.task_state, ot.scheduler_id, ot.exec_context_id, ot.worker_address FROM sys.dm_os_tasks AS ot WHERE ot.session_id = 98 ORDER BY ot.parent_task_address, ot.scheduler_id, ot.exec_context_id
exec_requests で取得できた「task_address」ですが、sys.dm_os_tasks でも同一の task_address の存在が確認できます。
「parent_task_address」が NULL となっているため、このタスクアドレスで実行されているタスクは、並列クエリを実行している際の「コーディネーター タスク」となり、並列クエリの調整を行っているタスクの情報ということが確認できます。
並列クエリとして、実行されている場合は、このコーディネーター タスクが把握できているリソースの使用状況が sys.dm_exec_requests から確認することができており、実行中の子のタスクのリソース使用状況の情報の一部については、dm_exec_requests では、並列クエリが完了したタイミングでないと確認をすることができません。
クエリが複数のステートメントで構成されているのであれば、情報が取得できるタイミングがあるかもしれませんが、単一ステートメントで完結している場合では取得できるタイミングを見極めるのは難しいかと。
それでは、並列クエリのリソースの使用状況はどのように確認すればよいかというと、スレッドまで情報を落とし込んで取得を行います。
先ほど取得を行った sys.dm_os_tasks の情報に関連する情報を追加します。
SELECT ot.session_id, ot.parent_task_address, ot.task_address, ot.pending_io_count, oth.os_thread_id, ot.task_state, ow.last_wait_type, ot.scheduler_id, ot.exec_context_id, oth.usermode_time, oth.kernel_time, ow.pending_io_count, ow.pending_io_byte_count, ot.pending_io_byte_count, ow.pending_io_byte_average, ot.pending_io_byte_average FROM sys.dm_os_tasks AS ot LEFT JOIN sys.dm_os_workers AS ow ON ow.worker_address = ot.worker_address LEFT JOIN sys.dm_os_threads AS oth ON oth.thread_address = ow.thread_address WHERE ot.session_id = 98 ORDER BY ot.parent_task_address, ot.scheduler_id, ot.exec_context_id
DMV の情報を組み合わせることで、SQL Server のタスクが、OS 上の SQL Server のプロセス内の度のスレッド ID で処理が行われているかを確認することができます。
ここまでブレークダウンをすると、sys.dm_exec_requests では「CXPACKET が発生している」としか分からなかったリクエストについて、実際にはどのようなタスクが実行されており、各タスクで発生している待ち事象や、リソースの使用状況についても確認を行うことができます。
sys.dm_exec_requests で CXPACKET が発生しているクエリについて、さらに詳細にタスクの実行状況を確認する必要がある場合は、タスクをベースにして情報の取得を行うことで、さらに詳細な状況を確認することができるということは、クエリの情報を分析する際の有効な手段として覚えておくと良いのではないでしょうか。