Azure SQL Data Warehouse でレプリケート テーブルを使用するための設計ガイダンスDesign guidance for using replicated tables in Azure SQL Data Warehouse

この記事では、SQL Data Warehouse スキーマでレプリケート テーブルを設計するための推奨事項を紹介します。This article gives recommendations for designing replicated tables in your SQL Data Warehouse schema. これらの推奨事項を使用すると、データの移動が少なくなり、クエリの複雑さが軽減されることでクエリ パフォーマンスが向上します。Use these recommendations to improve query performance by reducing data movement and query complexity.

前提条件Prerequisites

この記事では、読者が SQL Data Warehouse のデータ分散とデータ移動の概念を理解していることを前提としています。This article assumes you are familiar with data distribution and data movement concepts in SQL Data Warehouse. 詳細については、アーキテクチャに関する記事を参照してください。For more information, see the architecture article.

テーブル設計の一環として、ご利用のデータと、そのデータを照会する方法についてできる限り理解してください。As part of table design, understand as much as possible about your data and how the data is queried. たとえば、次のような質問を考えてみます。For example, consider these questions:

  • テーブルの大きさはどの程度か。How large is the table?
  • どの程度の頻度でテーブルが更新されるか。How often is the table refreshed?
  • データ ウェアハウス内にファクト テーブルとディメンション テーブルがあるか。Do I have fact and dimension tables in a data warehouse?

レプリケート テーブルとはWhat is a replicated table?

レプリケート テーブルには、各コンピューティング ノード上でアクセスできるテーブルの完全なコピーがあります。A replicated table has a full copy of the table accessible on each Compute node. テーブルをレプリケートすると、結合または集計の前に、コンピューティング ノード内のデータを転送する必要がなくなります。Replicating a table removes the need to transfer data among Compute nodes before a join or aggregation. テーブルには複数のコピーが含まれているため、テーブルのサイズが 2 GB 未満に圧縮されている場合にレプリケート テーブルが最も効果的に機能します。Since the table has multiple copies, replicated tables work best when the table size is less than 2 GB compressed.

次の図は、各コンピューティング ノード上でアクセスできるレプリケート テーブルを示したものです。The following diagram shows a replicated table that is accessible on each Compute node. SQL Data Warehouse では、レプリケート テーブルは各コンピューティング ノード上のディストリビューション データベースに完全にコピーされます。In SQL Data Warehouse, the replicated table is fully copied to a distribution database on each Compute node.

レプリケート テーブルReplicated table

レプリケート テーブルは、スター スキーマの小規模なディメンションのテーブルに適しています。Replicated tables work well for small dimension tables in a star schema. ディメンション テーブルは、通常、複数のコピーの格納および保持を可能にするサイズです。Dimension tables are usually of a size that makes it feasible to store and maintain multiple copies. ディメンションは、顧客名、住所、製品の詳細など、変更頻度の低い説明的なデータを格納します。Dimensions store descriptive data that changes slowly, such as customer name and address, and product details. 変更頻度が低いというデータの性質により、レプリケート テーブルの再構築は少なくなります。The slowly changing nature of the data leads to fewer rebuilds of the replicated table.

次の場合は、レプリケート テーブルの使用を検討してください。Consider using a replicated table when:

  • ディスク上のテーブル サイズが、行数には関係なく、2 GB 未満である。The table size on disk is less than 2 GB, regardless of the number of rows. テーブルのサイズを調べるには、次のように DBCC PDW_SHOWSPACEUSED コマンドを使用することができます。DBCC PDW_SHOWSPACEUSED('ReplTableCandidate')To find the size of a table, you can use the DBCC PDW_SHOWSPACEUSED command: DBCC PDW_SHOWSPACEUSED('ReplTableCandidate').
  • データ移動を必要とする結合でテーブルが使用されている。The table is used in joins that would otherwise require data movement. ハッシュ分散テーブルなど同じ列で分散されていないテーブルを結合して、1 つのラウンドロビン テーブルにする場合、クエリを完了するにはデータの移動が必要です。When joining tables that are not distributed on the same column, such as a hash-distributed table to a round-robin table, data movement is required to complete the query. テーブルの 1 つが小さい場合は、レプリケート テーブルを検討してください。If one of the tables is small, consider a replicated table. ほとんどの場合、ラウンド ロビン テーブルの代わりにレプリケート テーブルを使用することをお勧めします。We recommend using replicated tables instead of round-robin tables in most cases. クエリ プランでのデータ移動操作を表示するには、sys.dm_pdw_request_steps を使用してください。To view data movement operations in query plans, use sys.dm_pdw_request_steps. BroadcastMoveOperation は、レプリケート テーブルを使用することで不要にできる代表的なデータ移動操作です。The BroadcastMoveOperation is the typical data movement operation that can be eliminated by using a replicated table.

次の場合、レプリケート テーブルでは最適なクエリ パフォーマンスが得られない可能性があります。Replicated tables may not yield the best query performance when:

  • テーブルで、頻繁な挿入、更新、削除操作が行われる。The table has frequent insert, update, and delete operations. これらのデータ操作言語 (DML) 操作では、レプリケート テーブルの再構築が必要となります。These data manipulation language (DML) operations require a rebuild of the replicated table. 頻繁に再構築すると、パフォーマンスが低下する場合があります。Rebuilding frequently can cause slower performance.
  • データ ウェアハウスが頻繁にスケーリングされる。The data warehouse is scaled frequently. データ ウェアハウスをスケーリングすると、コンピューティング ノードの数が変わるため、再構築が発生します。Scaling a data warehouse changes the number of Compute nodes, which incurs a rebuild.
  • テーブルに多数の列があっても、通常、データ操作でアクセスする列はごく少数である。The table has a large number of columns, but data operations typically access only a small number of columns. このシナリオでは、テーブル全体をレプリケートするのではなく、テーブルを分散し、頻繁にアクセスする列にインデックスを作成する方が効果的であると考えられます。In this scenario, instead of replicating the entire table, it might be more effective to distribute the table, and then create an index on the frequently accessed columns. クエリでデータ移動が必要な場合、SQL Data Warehouse は要求された列のデータを移動するだけです。When a query requires data movement, SQL Data Warehouse only moves data for the requested columns.

単純なクエリ述語でレプリケート テーブルを使用するUse replicated tables with simple query predicates

テーブルを分散するかレプリケートするかを選択する前に、そのテーブルに対して実行を計画するクエリの種類について考えてみます。Before you choose to distribute or replicate a table, think about the types of queries you plan to run against the table. できる限り、次のようにしてください。Whenever possible,

  • 等値や非等値など、単純なクエリ述語を使用したクエリには、レプリケート テーブルを使用します。Use replicated tables for queries with simple query predicates, such as equality or inequality.
  • LIKE や NOT LIKE など、複雑なクエリ述語を使用したクエリには、分散テーブルを使用します。Use distributed tables for queries with complex query predicates, such as LIKE or NOT LIKE.

CPU を集中的に使用するクエリでは、作業がすべてのコンピューティング ノードに分散されたときに、最高のパフォーマンスが得られます。CPU-intensive queries perform best when the work is distributed across all of the Compute nodes. たとえば、テーブルの各行の計算を実行するクエリのパフォーマンスは、レプリケート テーブルよりも分散テーブルの方が高くなります。For example, queries that run computations on each row of a table perform better on distributed tables than replicated tables. レプリケート テーブル全体が各コンピューティング ノードに格納されるため、CPU を集中的に使用する、レプリケート テーブルに対するクエリは、各コンピューティング ノード上のテーブル全体に対して実行されます。Since a replicated table is stored in full on each Compute node, a CPU-intensive query against a replicated table runs against the entire table on every Compute node. 余分な計算により、クエリのパフォーマンスが低下する場合があります。The extra computation can slow query performance.

たとえば、次のクエリには、複雑な述語が使用されています。For example, this query has a complex predicate. レプリケート テーブルではなく分散テーブルを指定した場合に、処理速度が速くなります。It runs faster when supplier is a distributed table instead of a replicated table. この例では、supplier をラウンドロビン分散できます。In this example, supplier can be round-robin distributed.


SELECT EnglishProductName 
FROM DimProduct 
WHERE EnglishDescription LIKE '%frame%comfortable%'

既存のラウンド ロビン テーブルをレプリケート テーブルに変換するConvert existing round-robin tables to replicated tables

既にラウンド ロビン テーブルがある場合、この記事に記載されている条件を満たしているのであれば、レプリケート テーブルに変換することをお勧めします。If you already have round-robin tables, we recommend converting them to replicated tables if they meet with criteria outlined in this article. レプリケート テーブルは、データ移動の必要がなくなるため、ラウンド ロビン テーブルよりもパフォーマンスが高くなります。Replicated tables improve performance over round-robin tables because they eliminate the need for data movement. ラウンド ロビン テーブルでは、常に、結合のためにデータ移動が必要になります。A round-robin table always requires data movement for joins.

この例では CTAS を使用して、DimSalesTerritory テーブルをレプリケート テーブルに変更します。This example uses CTAS to change the DimSalesTerritory table to a replicated table. この例は、DimSalesTerritory がハッシュ分散かラウンド ロビンかに関係なく動作します。This example works regardless of whether DimSalesTerritory is hash-distributed or round-robin.

CREATE TABLE [dbo].[DimSalesTerritory_REPLICATE]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = REPLICATE  
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]
OPTION  (LABEL  = 'CTAS : DimSalesTerritory_REPLICATE') 

--Create statistics on new table
CREATE STATISTICS [SalesTerritoryKey] ON [DimSalesTerritory_REPLICATE] ([SalesTerritoryKey]);
CREATE STATISTICS [SalesTerritoryAlternateKey] ON [DimSalesTerritory_REPLICATE] ([SalesTerritoryAlternateKey]);
CREATE STATISTICS [SalesTerritoryRegion] ON [DimSalesTerritory_REPLICATE] ([SalesTerritoryRegion]);
CREATE STATISTICS [SalesTerritoryCountry] ON [DimSalesTerritory_REPLICATE] ([SalesTerritoryCountry]);
CREATE STATISTICS [SalesTerritoryGroup] ON [DimSalesTerritory_REPLICATE] ([SalesTerritoryGroup]);

-- Switch table names
RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[DimSalesTerritory_REPLICATE] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

ラウンド ロビンとレプリケートのクエリ パフォーマンスの例Query performance example for round-robin versus replicated

レプリケート テーブルでは、結合のためのデータ移動は必要ありません。これは、テーブル全体が既に各コンピューティング ノード上に存在するためです。A replicated table does not require any data movement for joins because the entire table is already present on each Compute node. ディメンション テーブルがラウンド ロビン分散の場合、結合によって、ディメンション テーブル全体が各コンピューティング ノードにコピーされます。If the dimension tables are round-robin distributed, a join copies the dimension table in full to each Compute node. データを移動するために、クエリ プランには BroadcastMoveOperation と呼ばれる操作が含まれています。To move the data, the query plan contains an operation called BroadcastMoveOperation. この種類のデータ移動操作では、クエリのパフォーマンスが低下します。レプリケート テーブルを使用すると、この操作は使用されなくなります。This type of data movement operation slows query performance and is eliminated by using replicated tables. クエリ プランのステップを表示するには、sys.dm_pdw_request_steps システム カタログ ビューを使用します。To view query plan steps, use the sys.dm_pdw_request_steps system catalog view.

たとえば、AdventureWorks スキーマに対する次のクエリでは、FactInternetSales テーブルがハッシュ分散です。For example, in following query against the AdventureWorks schema, the FactInternetSales table is hash-distributed. DimDate テーブルと DimSalesTerritory テーブルは、小さいディメンション テーブルです。The DimDate and DimSalesTerritory tables are smaller dimension tables. このクエリでは、会計年度 2004 年の北米における売上合計が返されます。This query returns the total sales in North America for fiscal year 2004:

SELECT [TotalSalesAmount] = SUM(SalesAmount)
FROM dbo.FactInternetSales s
INNER JOIN dbo.DimDate d
  ON d.DateKey = s.OrderDateKey
INNER JOIN dbo.DimSalesTerritory t
  ON t.SalesTerritoryKey = s.SalesTerritoryKey
WHERE d.FiscalYear = 2004
  AND t.SalesTerritoryGroup = 'North America'

DimDateDimSalesTerritory をラウンドロビン テーブルとして作成し直しました。We re-created DimDate and DimSalesTerritory as round-robin tables. 結果として、このクエリでは次のクエリ プランが示されました。これには、複数のブロードキャスト移動操作が含まれています。As a result, the query showed the following query plan, which has multiple broadcast move operations:

ラウンドロビン クエリ プラン

DimDateDimSalesTerritory をレプリケート テーブルとして作成し直し、もう一度このクエリを実行しました。We re-created DimDate and DimSalesTerritory as replicated tables, and ran the query again. その結果のクエリ プランは非常に短くなり、ブロードキャスト移動は含まれていません。The resulting query plan is much shorter and does not have any broadcast moves.

レプリケートされたクエリ プラン

レプリケート テーブルを変更する場合のパフォーマンスに関する考慮事項Performance considerations for modifying replicated tables

SQL Data Warehouse は、テーブルのマスター バージョンを保持することによってレプリケート テーブルを実装します。SQL Data Warehouse implements a replicated table by maintaining a master version of the table. そのマスター バージョンは、各コンピューティング ノード上の 1 つのディストリビューション データベースにコピーされます。It copies the master version to one distribution database on each Compute node. 変更があったときには、SQL Data Warehouse がまずマスター テーブルを更新します。When there is a change, SQL Data Warehouse first updates the master table. そして、Compute ノードのすべてでテーブルを再構築します。Then it rebuilds the tables on each Compute node. レプリケート テーブルの再構築では、すべての Compute ノードにテーブルがコピーされ、インデックスが再構築されます。A rebuild of a replicated table includes copying the table to each Compute node and then building the indexes. たとえば、DW400 上のレプリケート テーブルには、データのコピーが 5 部あります。For example, a replicated table on a DW400 has 5 copies of the data. 各 Compute ノードにマスター コピー 1 部と完全コピー 1 部A master copy and a full copy on each Compute node. すべてのデータは分散データベースに格納されます。All data is stored in distribution databases. SQL Data Warehouse はこのモデルを使用して、データ変更ステートメントを高速に処理し、柔軟なスケーリング操作を行えるようにしています。SQL Data Warehouse uses this model to support faster data modification statements and flexible scaling operations.

再構築が必要になるのは、次の操作の後です。Rebuilds are required after:

  • データが読み込まれるまたは変更されるData is loaded or modified
  • データ ウェアハウスが別のレベルにスケーリングされるThe data warehouse is scaled to a different level
  • テーブル定義が更新されるTable definition is updated

次の操作の後は、再構築は不要です。Rebuilds are not required after:

  • 一時停止操作Pause operation
  • 再開操作Resume operation

再構築は、データが変更された直後には行われるわけではありません。The rebuild does not happen immediately after data is modified. 再構築は、初めてクエリがテーブルから選択するときにトリガーされます。Instead, the rebuild is triggered the first time a query selects from the table. 各 Compute ノードにデータが非同期にコピーされる間、再構築をトリガーしたクエリはマスター バージョンのテーブルからただちにデータを読み取ります。The query that triggered the rebuild reads immediately from the master version of the table while the data is asynchronously copied to each Compute node. データのコピーが完了するまで、以降のクエリはマスター バージョンのテーブルを使い続けます。Until the data copy is complete, subsequent queries will continue to use the master version of the table. 別の再構築を強制するレプリケート テーブルに対するアクティビティが発生すると、データ コピーは無効になり、次の select ステートメントによって再びデータ コピーがトリガーされます。If any activity happens against the replicated table that forces another rebuild, the data copy is invalidated and the next select statement will trigger data to be copied again.

インデックスは控えめに使用するUse indexes conservatively

標準的なインデックス作成方法は、レプリケート テーブルにも当てはまります。Standard indexing practices apply to replicated tables. SQL Data Warehouse は、再構築の一環として、各レプリケート テーブル インデックスを再構築します。SQL Data Warehouse rebuilds each replicated table index as part of the rebuild. インデックスを使用するのは、インデックスの再構築にかかるコストよりもパフォーマンスの向上が重要な場合のみにしてください。Only use indexes when the performance gain outweighs the cost of rebuilding the indexes.

データの読み込みをバッチ処理するBatch data loads

レプリケート テーブルにデータを読み込む場合、複数の読み込みをバッチ処理することで、再構築を最小限に抑えるようにします。When loading data into replicated tables, try to minimize rebuilds by batching loads together. select ステートメントを実行する前に、バッチ処理された読み込みすべてを実行します。Perform all the batched loads before running select statements.

たとえば、次の読み込みパターンでは、4 つのソースからデータを読み込み、4 つの再構築を呼び出します。For example, this load pattern loads data from four sources and invokes four rebuilds.

  • ソース 1 から読み込む。Load from source 1.
  • Select ステートメントで再構築 1 をトリガーする。Select statement triggers rebuild 1.
  • ソース 2 から読み込む。Load from source 2.
  • Select ステートメントで再構築 2 をトリガーする。Select statement triggers rebuild 2.
  • ソース 3 から読み込む。Load from source 3.
  • Select ステートメントで再構築 3 をトリガーする。Select statement triggers rebuild 3.
  • ソース 4 から読み込む。Load from source 4.
  • Select ステートメントで再構築 4 をトリガーする。Select statement triggers rebuild 4.

たとえば、次の読み込みパターンでは、4 つのソースからデータを読み込みますが、呼び出す再構築は 1 つだけです。For example, this load pattern loads data from four sources, but only invokes one rebuild.

  • ソース 1 から読み込む。Load from source 1.
  • ソース 2 から読み込む。Load from source 2.
  • ソース 3 から読み込む。Load from source 3.
  • ソース 4 から読み込む。Load from source 4.
  • Select ステートメントで再構築をトリガーする。Select statement triggers rebuild.

バッチ読み込み後にレプリケート テーブルを再構築するRebuild a replicated table after a batch load

クエリの実行時間に一貫性を持たせるため、バッチ読み込み後に強制的にレプリケート テーブルを構築することを検討してください。To ensure consistent query execution times, consider forcing the build of the replicated tables after a batch load. 構築しない場合、最初のクエリは引き続きデータ移動を使用してクエリを完了します。Otherwise, the first query will still use data movement to complete the query.

次のクエリでは、sys.pdw_replicated_table_cache_state DMV を使用して、変更されたものの再構築されていないレプリケート テーブルの一覧を表示します。This query uses the sys.pdw_replicated_table_cache_state DMV to list the replicated tables that have been modified, but not rebuilt.

SELECT [ReplicatedTable] = t.[name]
  FROM sys.tables t  
  JOIN sys.pdw_replicated_table_cache_state c  
    ON c.object_id = t.object_id 
  JOIN sys.pdw_table_distribution_properties p 
    ON p.object_id = t.object_id 
  WHERE c.[state] = 'NotReady'
    AND p.[distribution_policy_desc] = 'REPLICATE'

再構築をトリガーするには、前の出力に含まれる各テーブルに対して次のステートメントを実行します。To trigger a rebuild, run the following statement on each table in the preceding output.

SELECT TOP 1 * FROM [ReplicatedTable]

次の手順Next steps

レプリケート テーブルを作成するには、次のいずれかのステートメントを使用します。To create a replicated table, use one of these statements:

分散テーブルの概要については、分散テーブルに関するページを参照してください。For an overview of distributed tables, see distributed tables.