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.

注意

レプリケート テーブル機能は、現在、パブリック プレビューの段階です。The replicated table feature is currently in public preview. 一部の動作は変更される可能性があります。Some behaviors are subject to change.

前提条件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. たとえば、ハッシュ分散テーブルの結合では、結合する列が同じディストリビューション列ではない場合にデータ移動が必要になります。For example, a join on hash-distributed tables requires data movement when the joining columns are not the same distribution column. ハッシュ分散テーブルの 1 つが小さい場合は、レプリケート テーブルを検討してください。If one of the hash-distributed tables is small, consider a replicated table. ラウンド ロビン テーブルの結合では、データ移動が必要です。A join on a round-robin table requires data movement. ほとんどの場合、ラウンド ロビン テーブルの代わりにレプリケート テーブルを使用することをお勧めします。We recommend using replicated tables instead of round-robin tables in most cases.

次の場合は、既存の分散テーブルをレプリケート テーブルに変換することを検討してください。Consider converting an existing distributed table to a replicated table when:

  • データをすべてのコンピューティング ノードにブロードキャストするデータ移動操作がクエリ プランで使用されている。Query plans use data movement operations that broadcast the data to all the Compute nodes. BroadcastMoveOperation はコストが高く、クエリのパフォーマンスが低下します。The BroadcastMoveOperation is expensive and slows query performance. クエリ プランでのデータ移動操作を表示するには、sys.dm_pdw_request_steps を使用してください。To view data movement operations in query plans, use sys.dm_pdw_request_steps.

次の場合、レプリケート テーブルでは最適なクエリ パフォーマンスが得られない可能性があります。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 hash 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 in 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. この例では、ハッシュ分散またはラウンド ロビン分散を使用できます。In this example, supplier can be hash-distributed or 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. その後、各コンピューティング ノード上のテーブルの再構築を要求します。Then it requires a rebuild of the tables on each Compute node. レプリケート テーブルの再構築には、各コンピューティング ノードへのテーブルのコピーとインデックスの再構築が含まれます。A rebuild of a replicated table includes copying the table to each Compute node and then rebuilding the indexes.

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

  • データが読み込まれるまたは変更されるData is loaded or modified
  • データ ウェアハウスが別の サービス レベルにスケーリングされるThe data warehouse is scaled to a different service 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. テーブルに対する最初の select ステートメント内に、レプリケート テーブルを再構築するステップがあります。Within the initial select statement from the table are steps to rebuild the replicated table. 再構築はクエリ内で行われるため、最初の select ステートメントへの影響は、テーブルのサイズによっては非常に大きくなる可能性があります。Because the rebuild is done within the query, the impact to the initial select statement could be significant depending on the size of the table. 再構築を必要とするレプリケート テーブルが複数含まれている場合は、各コピーがステートメント内のステップとして順次再構築されます。If multiple replicated tables are involved that need a rebuild, each copy is rebuilt serially as steps within the statement. レプリケート テーブルの再構築中にデータの整合性を維持するために、テーブルに対する排他的ロックが取得されます。To maintain data consistency during the rebuild of the replicated table an exclusive lock is taken on the table. このロックにより、再構築中はテーブルへのすべてのアクセスが禁止されます。The lock prevents all access to the table for the duration of the rebuild.

インデックスは控えめに使用する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, we recommend forcing a refresh of the replicated tables after a batch load. そうしないと、最初のクエリはテーブルが更新されるまで待たなければなりませんが、更新にはインデックスの再構築も含まれます。Otherwise, the first query must wait for the tables to refresh, which includes rebuilding the indexes. 対象となるレプリケート テーブルのサイズと数によっては、パフォーマンスへの影響が非常に大きくなります。Depending on the size and number of replicated tables affected, the performance impact can be significant.

次のクエリでは、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 force 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.