SE の雑記

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

SQLOS のワーカースレッドについて軽くまとめてみました

leave a comment

SQLOS で管理しているワーカースレッド (Worker Thread) について軽くまとめてみたいと思います。

■Max Worker Thread について


SQL Server の最大ワーカースレッド数を管理する、[max worker threads] オプションですが、デフォルトでは [0] が設定されており、SQL Server のスケジューラー数に応じて自動で設定されるようになっています。

自動で設定された最大ワーカースレッド数については [sys.dm_os_sys_info] から確認することが可能です。

SELECT
    max_workers_count
FROM
    sys.dm_os_sys_info

今回は 8 コアの環境 (HT 有り) を使用しており、スケジューラーが 8 個、起動している環境となっています。
そのため、最大ワーカースレッド数は以下のように設定されます。
image

 

■ワーカースレッドの割り当て状況


次にワーカースレッドが実際にどれくらい割り当てられているかを確認してみたいと思います。

最大で 576 のワーカースレッドが使用できることを確認しましたが、このワーカースレッドは SQL Server のサービスの起動時に最大値まで自動的に割り当てられているということはありません。

x64 の SQL Server を使用している場合、スレッドスタックのサイズが 2MB となっています。
# x86 : 512KB / IA64 4MB

サービスの起動時に 576 × 2MB = 1,152MB の領域をスレッドスタックとして確保するということはせず、使用するときになったらワーカースレッド用のメモリを確保するという動作になるため、起動時には抑えられた形でワーカースレッドが各スケジューラーに割り当てられています。

それでは、実際のワーカースレッドの割り当て状況を確認してみたいと思います。
# SQL Server が内部で利用していると思われるスケジューラーは省いています。

SELECT
    parent_node_id
    , cpu_id
    , scheduler_id
    , current_workers_count
FROM
    sys.dm_os_schedulers
WHERE
    scheduler_id < 256

image

[current_workers_count] が現在、各スケジューラーに対して割り当てられているワーカースレッド数になるのですが、最大 576 と比較して、抑えられた値が設定されているのが確認できます。

それでは、この環境に対して、550 スレッドで接続をして処理をした後の状態を確認してみたいと思います。
image

下の情報がスケジューラーに割り当てられているワーカースレッドの合計になります。

594 のワーカースレッドが全スケジューラーで使用されているのが確認できますね。
各スケジューラーには最大ワーカースレッド数に設定されている値から、均等にスレッドが割り当てられることになります。
そのため、576 最大ワーカースレッド / 8 スケジューラー = 72 になりますので、この数が各スケジューラーに割り当てられる最大のスレッド数になります。

■Current Worker と Acitive Worker


先ほどは CUrrent Worker の値から、各スケジューラーに割り当てられている、ワーカースレッドの数を確認しました。
ワーカースレッドの状態はいくつかあるのですが、今回は Active Worker についてみていきたいと思います。

Current Worker はスケジューラーに割り当てられているワーカースレッドの数になるのですが、これは割り当てられている状態を示しており、実際に使われているワーカースレッドということではありません。

以下のクエリを実行してみます。

SELECT
    (SELECT SUM(current_workers_count) FROM sys.dm_os_schedulers) AS [CurrentWorker]
    , (SELECT SUM(active_workers_count) FROM sys.dm_os_schedulers) AS [Active Worker]
    , (SELECT COUNT(*) FROM sys.dm_os_threads)  AS [OS Thread (Include Non SQL Server)]
    , (SELECT COUNT(*) FROM sys.dm_os_threads
        WHERE started_by_sqlservr = 1 AND scheduler_address IS NOT NULL) AS [OS Thread (Exclude Non SQL Server)]

image

CUrrent Worker は 603 となっているのですが、Active Worker は 30 となっています。
これは、先ほどのテストをしたときに瞬間的にワーカースレッドが必要になり、最大値まで割り当てをしたが、現状は 30 しかワーカースレッドが使用されていない状態となっています。

スレッド系の DMV として、[sys.dm_os_threads] があるのですが、スケジューラーに割り当てられているワーカースレッドの合計と、この DMV から SQL Server のスレッドのみを抽出した合計は一致するようですね。

このあたりの情報を使用することで、最大ワーカースレッドの値が現状の使用状況とマッチしているかを確認することが可能です。

 

■スレッドスタックに割り当てられているメモリ領域を確認


スレッドスタックに割り当てられているメモリですが、これは max server memory といった、SQL Server が使用するメモリを制御するための設定では管理がされていない領域となっています。

これに関しては、以下の記事がわかりやすいです。
Denali のメモリ管理(後編)

スレッドスタックのサイズは x64 の場合、2MB というのがわかっていますので、DMV を使用しても取得することが可能なのですが、今回は Sysinternals の VMMap を使用して確認をしてみたいと思います。

VMMap で [sqlservr.exe] の情報を取得してみます。
image

type が Stack となっている領域がスレッドスタックで確保れている領域になります。
image

上記の画像は Stack の内容を表示したものになるのですが、下のペインに表示されている詳細情報で、Size が 2,048 K となっているのが確認できます。
これは、スレッドスタックに 2MB のメモリが割り当てられていることを示しています。
# x86 の環境で情報を取得した場合、Size が 512 K になっていることが確認できます。

中間のメモリサイズの合計を表示しているペインでは、Stack のメモリとして 1,116,192 K がコミットされた状態として確保されているのが確認できますね。

先ほど、603 のワーカースレッドが割り当てられていることを SQL Server 上から確認をしています。
603 × 2MB= 1,206 MB になりますので、SQL Server で取得した情報と近似値となっていることがここから確認できますね。

VMMap を使用できない場合、Current Worker の数 × スタックサイズで計算することで、スレッドスタックのサイズを把握することができると思います。

 

■スレッドアドレス / スレッド IDとの対比


VMMap ではスレッドスタックの各スレッドのスレッド ID を取得することが可能です。
image

また、Sysinternals の Process Explorer を使用して、プロセスのスレッド情報を取得することも可能です。

image

これらのスレッドの情報ですが、SQL Server の DMV を使用して取得した情報と対比することが可能です。

取得には以下のようなクエリを使用します。
# 情報の見方を変えるためのクエリはコメントアウトしています。

SELECT
    dot.thread_address
    , dot.stack_base_address
    , dot.stack_end_address
    , dot.os_thread_id
    , dota.task_address
    , dota.host_address
    , dota.worker_address
    , dos.parent_node_id
    , dot.processor_group
    , dos.scheduler_id
    , dota.scheduler_id
    , der.scheduler_id
    , dos.cpu_id
    , dos.status
    , der.status
    , der.command
    , der.wait_type
    , der.last_wait_type
    , der.wait_resource
    , dota.task_state
    , dos.is_online
    , dos.is_idle
    , dota.session_id
    , der.session_id
    , dos.current_tasks_count
    , dos.runnable_tasks_count
    , dos.current_workers_count
    , dos.active_workers_count
    , dos.work_queue_count
    , dot.creation_time
    , der.start_time
    , dot.affinity
    , dot.priority
    , des.program_name
–    , *
FROM
    sys.dm_os_schedulers AS dos
    LEFT JOIN
    sys.dm_os_threads AS dot
    ON
    dos.scheduler_address = dot.scheduler_address
    AND
    dot.started_by_sqlservr = 1
    AND
    dot.scheduler_address IS NOT NULL
    LEFT JOIN
    sys.dm_os_tasks AS dota
    ON
    dot.worker_address = dota.worker_address
    LEFT JOIN
    sys.dm_exec_requests AS der
    ON
    dota.session_id = der.session_id
    LEFT JOIN
    sys.dm_exec_sessions AS des
    ON
    der.session_id = des.session_id
–WHERE
    –dota.task_address IS NOT NULL
    –dota.task_address IS NULL
ORDER BY
    os_thread_id ASC,
    dos.parent_node_id ASC

このクエリを実行すると以下のような情報を取得できます。
image

各情報でスレッド ID の重複が確認できると思います。

SQL Server の DMV から取得したスレッド ID を手掛かりに、さらに詳細な情報を追うということもできそうですね。

 

■ワーカースレッドの破棄


瞬間的に大量のワーカースレッドが割り当てられ、その後はワーカースレッドの利用が落ち着いた場合、スレッドスタックの領域は確保されたままになるかというとそういうことはありません。

未使用のワーカースレッドに関しては一定の時間、未使用の状態が継続すると破棄されるようになっています。

先ほどの VMMap の状態で 20 分程度、放っておいた後に F5 で更新したものがこちらになります。
image

スレッドスタックの領域が 147,192 K となり、メモリが解放されていることが確認できますね。
DMV でも情報を取得してみます。
image

DMV からも CUrrent Worker が減少していることが確認できます。

瞬間的に大量のワーカースレッドを使用することになり、その後ピークタイムが過ぎて、必要なるワーカースレッド数が減少した場合、不要なワーカースレッドは破棄され、メモリが確保されたままになるということはなさそうですね。

SQL Server 2012 だと、この間隔がどのぐらいなのかが調べきれていないのですが、SQL Server 2000 では 15 分で破棄されるという記載があります。
Inside the SQL Server 2000 User Mode Scheduler

SQL Server 上で取得した情報をいろいろなツールで取得した情報と対比でき、調べだすといろいろと気になることが増えてきて、スケジューラー周りの動作を調べるのも面白いですね~。

Written by masayuki.ozawa

10月 27th, 2011 at 6:20 am

Posted in SQL Server

Tagged with

Leave a Reply

*