SQL Server で、並列クエリの待ち事象を確認する際には「CXPACKET」「EXECSYNC」「EXCHANGE」を確認するのが一般的でしたが、SQL Server 2017 CU3 では、上記に加えて「CXCONSUMER」という待ち事象が増えています。
これにより、いままで CXPACKET として集計されていた一部の挙動が CXCONSUMER の方に集計されるようになります。
この動作自体は、PASS Summit 2017 で発表が行われ、Making parallelism waits actionable で解説されていたのですが、CU3 がリリースされたことで実際に検証が可能となりました。
今回の機能追加に伴い、sys.dm_os_wait_stats (Transact-SQL) のドキュメントも更新されています。
CXCONSUMER
行を送信する、producer スレッド consumer スレッドが待機したときに、並列クエリ プランで発生します。 これは、並列クエリの実行の通常の一部です。
適用されます: SQL Server 2017 CU3 と SQL データベースCXPACKET
クエリ プロセッサ交換反復子を同期するときに、および生成および行を使用する場合は、並列クエリ プランで発生します。 待機時間が長すぎて、クエリのチューニング (インデックスの追加など) を実行しても短くできない場合は、並列処理のコストしきい値を調整したり並列処理の次数を下げたりすることを検討してください。
注:でSQL Server 2017CU3 および SQL データベース、クエリ プロセッサ交換反復子を同期するように、コンシューマーのスレッドの行を作成できるようにのみ CXPACKET を参照します。 コンシューマーのスレッドは、CXCONSUMER 待機の種類で個別に追跡されます。
検証する際は、次のドキュメントと合わせて確認すると理解しやすいかと。
- CXPACKET 待ちは悪いことか?
- DOPは並列クエリで使用されるスレッド数ではない
- Parallel Query Execution in SQL Server
- Understanding and Controlling Parallel Query Processing in SQL Server
手元の SCVMM 用の DB で並列クエリが実行されているものがあったので、これを使いながら軽く検証を。
セッション A では、次のクエリを実行して、共有ロックの取得を保持した状態にし、Wait が発生している状態で、セッション B で違うクエリを実行します。
BEGIN TRAN SELECT * FROM dbo.view_PMM_DriverFullInfo WITH (HOLDLOCK) WHERE name = 'CNAB3STK.INF' WAITFOR DELAY '00:00:20' ROLLBACK TRAN
セッション B では、排他ロックを取得するクエリを実行して、ロック競合が発生した状態にします。
SELECT * FROM dbo.view_PMM_DriverFullInfo WITH (XLOCK) SELECT * FROM sys.dm_exec_session_wait_stats WHERE session_id = @@SPID
セッション A のトランザクションが完了するまで、ロック競合の状態になります。
競合が解消された後に実行される、セッション単位の待ち事象の情報としては次のような情報が取得できます。
上記のクエリでは、ビューから情報を取得しているのですが、ビューの検索時には並列クエリとして実行されています。
SQL Server 2017 に CU3 を適用する前の状態では、「CXPACKET」と「LCK_M_X」が高い値となっていますね。
クエリを実行した状態では、並列クエリとして実行されているもので、ロック競合が発生し、Producer スレッドが、Consumer スレッドに対して、データを渡すことが出いない状態になっています。
今までのバージョンでは、待ち事象からは、これ以上詳細に確認することができませんでした。
そのため、CXPACKET が発生している要因が、クエリの並列実行に起因しているのか、それとも他の事象に起因しているのかの判断が難しいというような課題がありました。
それでは、CU3 を適用した状態で同様のことを実施してみます。
「CXPACKET」の待ち事象が減り、「LCM_M_X」と「CXCONSUMER」が高い値を示していますね。
「waiting_tasks_count」「wait_time_ms」が「LCK_M_X」の 2 倍となっていますので、2 個のコンシューマースレッドが生成されている状態で発生しているという読み方ができそうです。
(Non CU3 は 4 コア、CU3 は 2 コアの環境で実行しているので、waitng_tasks_count には、コア数に依存した数値の違いが出ています)
この情報から、「LCK_M_X」が起因して、並列クエリで Consumer スレッドにデータを渡すことができず「CXCONSUMER」待機が発生しているという推測が可能となります。
この状態では、CPU 数 / MAXDOP 数の調整をしても効果は薄く、利用するスレッド数からのアプローチではなく、クエリの最適化のアプローチが必要になるという判断ができるかと。
並列度の最適化により、クエリの実行性能が改善するかの判断をする際に、強力な確認方法となりそうですので、有効に活用したいですね。
SQL Database でも、すでに「CXCONSUMER」の待ち事象は実装されているようなので、並列クエリの実行が可能なパフォーマンスレベルを使用している DB については情報を確認することができるかと。