SE の雑記

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

SQL Data Warehouse の Replicated Table を使ってみる

leave a comment

SQL Data Warehouse で Replicated Table が Public Preview でサポートされたようです で投稿をしましたが、Replicated Table を実際に使ってみました。

Replicated Table は各ディストリビューションに分散されているデータのコピーを、コンピュートノードに配置をすることにより、複数テーブルを使用する際のオーバーヘッドを抑えるための機能となります。

今回はサンプルとして、次のようなクエリを実行して、テーブルを作成します。

SET NOCOUNT ON
GO
DROP TABLE  [T_REPLICATE]
GO

CREATE TABLE [dbo].[T_REPLICATE]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = REPLICATE  
  )  
AS 
SELECT 1 AS C1, 'TEST' AS C2 
FROM sys.objects o1
CROSS JOIN sys.objects o2 
GO

INSERT INTO T_REPLICATE 
SELECT 1 AS C1, 'TEST' AS C2 
FROM sys.objects o1
CROSS JOIN sys.objects o2 
GO 50

 

テーブルの作成が完了したら、各ディストリビューションのデータの格納状況を確認してみます。

-- オブジェクトのデータ格納状態の取得
SELECT
	o.name,
	t.name,
	o.object_id,
	ps.object_id,
	m.physical_name,
	i.name,
	ni.name,
	im.physical_name,
	ps.index_id,
	i.type_desc,
	tp.distribution_policy_desc,
	ps.partition_number,
	ps.used_page_count,
	ps.row_count,
	ps.pdw_node_id,
	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
WHERE
	o.name = 'T_REPLICATE'
	OR
	o.name IS NULL
ORDER BY
	o.name, ps.index_id, ps.partition_number,ps.distribution_id
GO

取得した結果が以下になるのですが、データを追加した直後に関しては、ROUND_ROBIN 相当で各ディストリビューションにデータが分散されて配置されているのが確認できますね。

image

次にこのクエリを実行してみます。

SELECT t.[name], p.[object_id], p.[state]
  FROM sys.pdw_replicated_table_cache_state p 
  JOIN sys.tables t ON t.object_id = p.object_id

sys.pdw_replicated_table_cache_state から Replicated Table のキャッシュ状況を確認しているのですが、単純にデータを投入した直後については、キャッシュの状態が「NotReady」となっていることが確認できます。

image

レプリケートされたテーブルに関しては、データが更新され、初回のアクセスが行われた際にすべてのコンピュートノードにデータのコピーが作成されます。

そのため、ベースのデータを投入した直後のデータについては「NotReady」となっています。

それでは、レプリケートされたテーブルに SELECT を実施した後に、再度情報を確認してみます。

image

各コンピュートノードにデータのコピーが配置されている場合には「Ready」な状態となっています。

ただしデータの更新が行われ、コピーされているデータに変更がある場合は、次回のアクセス時に再度コピーが行われ、データが再配置されるというような動作が行われます。

これらの動作については、文頭で実行しているデータの情報を取得するクエリから確認することができます。

データがコピーされている状態であれば、名称が NULL となっているテーブルがコンピュートノードに配置されていることが確認できます。

image

このテーブルのオブジェクト ID は再度 NotReady → Ready になった状態で変更されているため、テーブルの再作成が行われていることが確認できます。

それでは、NotReady になっている状態で、実行計画を取得してみます。

EXPLAIN
SELECT COUNT(*) FROM [T_REPLICATE]

NotReady な状態になっているテーブルに対してアクセスをした場合、全コンピュートノードを対象にして、BLOADCAST_MOVE により、データの移動が行われていることが実行計画からも確認できます。

image

レプリケートされたテーブルが更新されていない場合の 2 回目のアクセスについては、既に移動されているデータにより検索が行えますので、データの移動が発生していないことが確認できますね。

image

頻繁に更新は行われないデータであれば、レプリケートされたテーブルは効率的にアクセスを行うための方法として、使用することができますが、データの更新が頻繁に行われる場合は、データ移動が頻繁に発生することになってしまうため、ハッシュ分散で格納した方がよいというような傾向が出る可能性があります。

2 GB で制限がかかるのかと思って、それ以上のデータを格納してみたのですが、Public Preview 時点では 2 GB を超えるレプリケートされたテーブルを作ることはできそうでした。

初回アクセス時のブロードキャストムーブに、かなり時間がかかっていたので、GB 単位のテーブルをレプリケートされたテーブルにすると、コピーされていない状態のアクセスコストがかなり大きそうではありますが。

Written by masayuki.ozawa

7月 23rd, 2017 at 7:31 pm

Posted in SQL Database

Tagged with

Leave a Reply

*