SE の雑記

SQL Data Warehouse (SQL DW) は、60 のデータベースに分散してデータを格納した分散テーブルを使用して、透過的にデータを検索することで、効率よくデータを分析することができるアーキテクチャとなっています。

データの分散を適切に実施することがが性能を出す際の重要なポイントとなります。
「分散の方式を誤り、一つのデータベースにデータが極端に集中した場合、どのような性能的なデメリットが出るか」を数値的に取得したことがなかったため、確認をしてみました。

今回は、TPC-H の「LINEITEM」を使用しているのですが、データの分散方法は極端になるように調整をしています。
テーブル単位のデータの格納状況は次のようなクエリで確認をすることができます。

-- オブジェクトのデータ格納状態の取得
SELECT
	o.name,
	t.name,
	ps.used_page_count,
	ps.row_count,
	ps.distribution_id
FROM
	sys.dm_pdw_nodes_db_partition_stats ps
	INNER JOIN
		sys.pdw_nodes_tables t
	ON
		ps.object_id = t.object_id
		AND
		ps.pdw_node_id = t.pdw_node_id
		AND
		ps.distribution_id = t.distribution_id
	LEFT JOIN
		sys.pdw_table_mappings m
	ON
		m.physical_name = t.name
	LEFT JOIN
		sys.objects o
	ON
		o.object_id = m.object_id
	LEFT JOIN
		sys.pdw_nodes_indexes ni
	ON
		ps.object_id = ni.object_id
		AND
		ps.index_id = ni.index_id
		AND
		ps.pdw_node_id = ni.pdw_node_id
		AND
		ps.distribution_id = ni.distribution_id
	LEFT JOIN
		sys.pdw_index_mappings im
	ON
		im.physical_name = ni.name
	LEFT JOIN
		sys.indexes i
	ON
		i.object_id = im.object_id
		AND
		i.index_id = im.index_id
	LEFT JOIN
		sys.pdw_table_distribution_properties tp
	ON
		tp.object_id = o.object_id
ORDER BY
	o.name, ps.index_id, ps.partition_number,ps.distribution_id
GO

 
同一サイズのデータを使用しており、一つのテーブルは ROUND_ROBIN によるデータ分散を実施しているため、次のように、各データベースに均等にデータが格納されている状態です。

もう一つのテーブルは、60 分割の特定のデータベースにのみデータが集中して (分散が適切ではなく格納されれるデータベースに偏りがある) 格納されるようにしています。

この状態で次のようなクエリを次実行し、処理時間の違いを確認してみます。

DECLARE @startTime datetime2(1) = (SELECT GETDATE())
SELECT L_SHIPDATE, COUNT(*),SUM(L_QUANTITY) FROM LINEITEM_Skew_Temp GROUP BY L_SHIPDATE ORDER BY 1
SELECT DATEDIFF(ms, @startTime, GETDATE()) AS ElapsedTime_ms
GO
DECLARE @startTime datetime2(1) = (SELECT GETDATE())
SELECT L_SHIPDATE, COUNT(*),SUM(L_QUANTITY) FROM LINEITEM_Skew GROUP BY L_SHIPDATE ORDER BY 1
SELECT DATEDIFF(ms, @startTime, GETDATE()) AS ElapsedTime_ms
GO

上がラウンドロビンで均等にデータを分散、下が不適切なハッシュ項目で特定のデータベースにのみデータが配置された状態の処理結果です。

同一のデータ件数を処理しているのですが、「17 秒」と「89 秒」というように処理時間に大きな差が出ていることが確認できますね。
この処理時間は、Gen2 で DW100c という最小の構成で実施した際の処理時間となります。
SQLDW Gen2 の場合、DW1000c 以上が複数のコンピューティングノードが設定された環境となります。

DW1000c の 2 台のコンピューティングノードが存在する状態で処理を実行してみるとどうなるでしょうか。
DW1000c にすることで、コンピューティングノードの台数の増加以外に、メモリサイズも増加しますので、どちらのパターンでも処理時間が向上していますね。

データに極端に偏りがあると、コンピューティングノードを増やしたことによる恩恵が受けにくいケースを考える必要があります。
下の画像は、DW1000c の状態のクエリプランとなります。


データに偏りがあるケースでは「ShuffleMoveOperation」に 6 秒かかっており、実行時間の大半がこの処理となっています。
SheffleMoveOperation は、データベースに分散されたデータの移動についての処理となりますので DMS (データ移動サービス) の情報から、さらに状況を確認することができます。

SELECT * FROM sys.dm_pdw_dms_workers
WHERE request_id IN ('QID1290', 'QID1294')
ORDER BY request_id, step_index, pdw_node_id, distribution_id

 
データが均等に分散されている状態では、複数のコンピューティングノードが均等にデータの取得を行えているため、データベースの負荷とコンピューティングの負荷が、スケールアウトしやすい形で分散されていることが確認できます。

それでは、データに偏りがある場合はどうなるでしょうか？
次の画像がデータに偏りを発生させた場合の、DB のデータ読み取りの状態となります。

データが均等に分散されていた場合は、「pdw_node_id」が「8」「15」の 2 台のコンピューターから、60 分割された各データベース (distribution_id) にデータの読み取りが行われており、負荷が均等に分散していました。
データに偏りがある場合、データのアクセスは「pdw_node_id」が「8」のコンピューターからのみ実施されており、データの読み取りについては「distribution_id」が「2」 のデータベースのみアクセスが発生しているという状態となっています。
このような状態ですと、複数のデータベースとコンピューティングリソースを使用できるという SQL DW の性能特徴を生かすことができないデータアクセスとなっているといえるのではないでしょうか。
製品の特性を活かしたデータ格納を行うことが SQL DW の重要なポイントとなるということが処理時間から確認ができたのかなと。