【SQL Server】並列処理時のパフォーマンスについて

前回は、並列処理の設定確認や変更方法を記しましたが、今回は並列実行されていることの確認や、その効果検証についてです。
memorandom-nishi.hatenablog.jp

並列処理時の確認

動的管理ビューのdm_exec_query_statsにSQL Server2016以降から並列度の次数(dop)やスレッド数が追加されています。

total_dop	並列処理の次数の合計
last_dop	最後に実行したときの並列処理の次数
min_dop	累積のうち最小の並列処理の次数
max_dop	累積のうち最大の並列処理の次数
total_used_threads	並列処理時のスレッド数の合計
last_used_threads	最後に実行したときの並列処理時のスレッド数
min_used_threads	累積のうち並列処理時の最小のスレッド数
max_used_threads	累積のうち並列処理時の最大のスレッド数

#dopとthreadの違いはMSのblogに記載があります。
https://blogs.msdn.microsoft.com/jpsql/2013/10/15/dop/

直近の実行での並列度や平均並列度は上記動的管理ビューを見ることでわかります。
例えば以下のようなSQLで確認することができます。

#以前作ったCPU、IO負荷の高いクエリの使いまわしですが。

SELECT TOP 100
        rank() over(order by total_dop/execution_count desc,sql_handle,statement_start_offset) AS [row_no]
,       db_name(st.dbid) AS [database_name]
,       creation_time
,       last_execution_time
,       (total_worker_time+0.0)/1000 AS [total_worker_time(ms)]
,       (total_worker_time+0.0)/(execution_count*1000) AS [AvgCPUTime(ms)]
,       (total_elapsed_time+0.0)/1000 AS [total_elapsed_time(ms)]
,       (total_elapsed_time+0.0)/(execution_count*1000) AS [AvgElapsedTime(ms)]
,       total_logical_reads AS [LogicalReads(page)]
,       total_logical_writes AS [logicalWrites(page)]
,       total_logical_reads+total_logical_writes AS [AggIO(page)]
,       (total_logical_reads+total_logical_writes)/(execution_count + 0.0) AS [AvgIO(page)]
,       execution_count
,       total_rows
,       max_dop
,       total_dop
,       total_dop/execution_count AS [AvgDOP]
,       st.text AS [batch_query_text]
,   CASE 
        WHEN sql_handle IS NULL
        THEN ' '
        ELSE ( SUBSTRING(st.text,(qs.statement_start_offset+2)/2,(CASE 
                                                                      WHEN qs.statement_end_offset = -1        
                                                                      THEN LEN(CONVERT(NVARCHAR(MAX),st.text))*2      
                                                                      ELSE qs.statement_end_offset    
                                                                  END 
        - qs.statement_start_offset) /2  ) )
    END AS [statement_query_text] 
,       plan_generation_num
,       qp.query_plan
FROM sys.dm_exec_query_stats   AS [qs]
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS [st]
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS [qp]
WHERE total_worker_time > 0 
ORDER BY total_dop/execution_count  DESC,sql_handle,statement_start_offset
OPTION (RECOMPILE)

グラフィカル表示の実行プランを確認する場合、次のアイコンがあれば並列実行される可能性があります。

アイコン	並列処理の物理操作	説明
	Distribute Streams	1つのインプットに対しパラレル処理
	Repartition Streams	複数のパラレル処理結果に対しパラレル処理
	Gather Streams	複数パラレル処理を1つに統合

実際の実行プランを取得するとアイコンにカーソルを合わせると実行回数として並列度が確認できます。
f:id:utiowa:20170506001532p:plain

それぞれのスレッドでの処理行数やCPU時間や実行時間も見ることができます。
f:id:utiowa:20170506001601p:plain

今回は並列処理のGather Streamsなので、イメージ的には次のような動きかと。
f:id:utiowa:20170506001625p:plain

ただし、「実際の実行プラン」でなければこの情報は見ることができないため、拡張イベント等の仕込みが無い場合は後から見ることができません。その場合は上記で説明した動的管理ビューからの平均・最大並列数で確認するのが現実的でしょうか。

並列処理した場合のパフォーマンスコスト

並列処理した場合のクエリを比較して負荷の違いを見てみます。
試しにMaxdopを1～4にした場合の各5回の実行からの平均統計で比較すると次のような結果となります。

お試しクエリ

-- キャッシュをクリア
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE


SET STATISTICS IO ON
SET STATISTICS TIME ON


SELECT TOP 10000
	A.[col1]
    ,	A.[col2]
    ,	A.[col3]
    ,	B.*
  FROM			[Param_testDB].[dbo].[Parallel_test] AS A
  INNER JOIN		[Param_testDB].[dbo].[Parallel_test] AS B
  ON A.col2 = B.col2
  ORDER BY NEWID(),4
  OPTION (MAXDOP 1)
--  OPTION (MAXDOP 2)
--  OPTION (MAXDOP 3)
--  OPTION (MAXDOP 4)

GO

SET STATISTICS TIME OFF
SET STATISTICS IO OFF