Linee guida di progettazione per l'uso di tabelle replicate in Azure SQL Data WarehouseDesign guidance for using replicated tables in Azure SQL Data Warehouse

Questo articolo offre alcuni consigli per la progettazione di tabelle replicate nello schema Azure SQL Data Warehouse.This article gives recommendations for designing replicated tables in your SQL Data Warehouse schema. Usare questi consigli per migliorare le prestazioni delle query riducendo lo spostamento dei dati e la complessità delle query stesse.Use these recommendations to improve query performance by reducing data movement and query complexity.

prerequisitiPrerequisites

Questo articolo presuppone una certa familiarità con i concetti di distribuzione e spostamento dei dati in SQL Data Warehouse.This article assumes you are familiar with data distribution and data movement concepts in SQL Data Warehouse. Per altre informazioni, vedere l'articolo relativo all'architettura.For more information, see the architecture article.

Come parte della progettazione di tabelle, è necessario comprendere quanto più possibile i propri dati e il modo in cui vengono eseguite query sui dati.As part of table design, understand as much as possible about your data and how the data is queried. Ad esempio, considerare queste domande:For example, consider these questions:

  • Quali sono le dimensioni della tabella?How large is the table?
  • Quanto spesso viene aggiornata la tabella?How often is the table refreshed?
  • Sono presenti tabelle dei fatti e delle dimensioni in un data warehouse?Do I have fact and dimension tables in a data warehouse?

Che cos'è una tabella replicata?What is a replicated table?

Una tabella replicata include una copia completa della tabella accessibile in ogni nodo di calcolo.A replicated table has a full copy of the table accessible on each Compute node. La replica di una tabella elimina la necessità di trasferire dati tra i nodi di calcolo prima di un join o un'aggregazione.Replicating a table removes the need to transfer data among Compute nodes before a join or aggregation. Poiché la tabella ha più copie, le tabelle replicate funzionano meglio quando le dimensioni delle tabelle sono inferiori a 2 GB, già compresse.Since the table has multiple copies, replicated tables work best when the table size is less than 2 GB compressed.

Il diagramma seguente mostra una tabella replicata accessibile in ogni nodo di calcolo.The following diagram shows a replicated table that is accessible on each Compute node. In SQL Data Warehouse la tabella replicata viene interamente copiata in un database di distribuzione in ogni nodo di calcolo.In SQL Data Warehouse, the replicated table is fully copied to a distribution database on each Compute node.

Tabella replicataReplicated table

Le tabelle replicate sono più adatte per piccole tabelle delle dimensioni in uno schema star.Replicated tables work well for small dimension tables in a star schema. Le tabelle delle dimensioni hanno in genere dimensioni tali da rendere possibile l'archiviazione e la conservazione di più copie.Dimension tables are usually of a size that makes it feasible to store and maintain multiple copies. Nelle dimensioni sono archiviati dati descrittivi che cambiano raramente, come il nome e l'indirizzo di un cliente e i dettagli del prodotto.Dimensions store descriptive data that changes slowly, such as customer name and address, and product details. La rarità delle modifiche dei dati comporta un numero minore di ricompilazioni della tabella replicata.The slowly changing nature of the data leads to fewer rebuilds of the replicated table.

Provare a usare una tabella replicata nei casi seguenti:Consider using a replicated table when:

  • La dimensione della tabella su disco è inferiore a 2 GB, indipendentemente dal numero di righe.The table size on disk is less than 2 GB, regardless of the number of rows. Per individuare la dimensione di una tabella, usare il comando 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').
  • La tabella viene usata in join che richiederebbero altrimenti lo spostamento dei dati.The table is used in joins that would otherwise require data movement. Quando si crea un join di tabelle non distribuite nella stessa colonna, ad esempio una tabella con distribuzione hash in una tabella round robin, è necessario lo spostamento dei dati per completare la query.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. Se una delle tabelle ha dimensioni ridotte, provare una tabella replicata.If one of the tables is small, consider a replicated table. È consigliabile usare tabelle replicate invece di tabelle round robin nella maggior parte dei casi.We recommend using replicated tables instead of round-robin tables in most cases. Per visualizzare le operazioni di spostamento dei dati nei piani di query, usare sys.dm_pdw_request_steps.To view data movement operations in query plans, use sys.dm_pdw_request_steps. L'operazione tipica di spostamento di dati che può essere eliminata usando una tabella replicata è BroadcastMoveOperation.The BroadcastMoveOperation is the typical data movement operation that can be eliminated by using a replicated table.

Le tabelle replicate possono essere causa di prestazioni delle query non ottimali nei casi seguenti:Replicated tables may not yield the best query performance when:

  • La tabella prevede frequenti operazioni di inserimento, aggiornamento ed eliminazione.The table has frequent insert, update, and delete operations. Queste operazioni di Data Manipulation Language (DML) richiedono una ricompilazione della tabella replicata.These data manipulation language (DML) operations require a rebuild of the replicated table. La ricompilazione causa spesso un rallentamento delle prestazioni.Rebuilding frequently can cause slower performance.
  • Il data warehouse viene ridimensionato spesso.The data warehouse is scaled frequently. Il ridimensionamento di un data warehouse comporta la modifica del numero dei nodi di calcolo, che richiede una ricompilazione.Scaling a data warehouse changes the number of Compute nodes, which incurs a rebuild.
  • La tabella include un numero elevato di colonne, ma le operazioni sui dati accedono in genere solo a una quantità ridotta di colonne.The table has a large number of columns, but data operations typically access only a small number of columns. In questo scenario, invece di replicare l'intera tabella, può essere più efficace eseguire una distribuzione della tabella e quindi creare un indice per le colonne cui si accede di frequente.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. Quando una query richiede lo spostamento dei dati, SQL Data Warehouse sposta solo i dati per le colonne richieste.When a query requires data movement, SQL Data Warehouse only moves data for the requested columns.

Usare tabelle replicate con predicati di query sempliciUse replicated tables with simple query predicates

Prima di scegliere di distribuire o replicare una tabella, considerare i tipi di query che si prevede di eseguire sulla tabella.Before you choose to distribute or replicate a table, think about the types of queries you plan to run against the table. Se possibile:Whenever possible,

  • Usare tabelle replicate per query con predicati di query semplici, come le query di uguaglianza o disuguaglianza.Use replicated tables for queries with simple query predicates, such as equality or inequality.
  • Usare tabelle distribuite per query con predicati di query complessi, come LIKE o NOT LIKE.Use distributed tables for queries with complex query predicates, such as LIKE or NOT LIKE.

Le query che richiedono un uso intensivo della CPU hanno prestazioni migliori quando il lavoro viene distribuito tra tutti i nodi di calcolo.CPU-intensive queries perform best when the work is distributed across all of the Compute nodes. Ad esempio, le query che eseguono calcoli in ogni riga di una tabella hanno prestazioni migliori nelle tabelle distribuite che non nelle tabelle replicate.For example, queries that run computations on each row of a table perform better on distributed tables than replicated tables. Poiché una tabella replicata viene completamente archiviata in ogni nodo di calcolo, una query che richiede un uso intensivo di CPU su una tabella replicata viene eseguita sull'intera tabella in ogni nodo di calcolo.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. Il calcolo aggiuntivo può rallentare le prestazioni delle query.The extra computation can slow query performance.

Questa query, ad esempio, ha un predicato complesso.For example, this query has a complex predicate. Viene eseguita più rapidamente quando il fornitore è una tabella distribuita invece di una tabella replicata.It runs faster when supplier is a distributed table instead of a replicated table. In questo esempio il fornitore può avere una distribuzione round robin.In this example, supplier can be round-robin distributed.


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

Convertire le tabelle round robin esistenti in tabelle replicateConvert existing round-robin tables to replicated tables

Se sono già presenti tabelle round robin, è consigliabile convertirle in tabelle replicate se soddisfano i criteri specificati in questo articolo.If you already have round-robin tables, we recommend converting them to replicated tables if they meet with criteria outlined in this article. Le tabelle replicate migliorano le prestazioni rispetto alle tabelle round robin, perché eliminano la necessità di spostare i dati.Replicated tables improve performance over round-robin tables because they eliminate the need for data movement. Una tabella round robin richiede lo spostamento dei dati per i join.A round-robin table always requires data movement for joins.

Questo esempio usa CTAS per modificare la tabella DimSalesTerritory in una tabella replicata.This example uses CTAS to change the DimSalesTerritory table to a replicated table. Questo esempio funziona indipendentemente dal fatto che per DimSalesTerritory sia stata eseguita una distribuzione hash o round robin.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];

Esempio di prestazioni delle query delle tabelle round robin rispetto alle tabelle replicateQuery performance example for round-robin versus replicated

Una tabella replicata non richiede lo spostamento dei dati per i join, perché l'intera tabella è già presente in ogni nodo di calcolo.A replicated table does not require any data movement for joins because the entire table is already present on each Compute node. Se viene eseguita una distribuzione round robin delle tabelle delle dimensioni, un join copia interamente la tabella delle dimensioni in ogni nodo di calcolo.If the dimension tables are round-robin distributed, a join copies the dimension table in full to each Compute node. Per spostare i dati, il piano di query contiene un'operazione chiamata BroadcastMoveOperation.To move the data, the query plan contains an operation called BroadcastMoveOperation. Questo tipo di operazione di spostamento dei dati rallenta le prestazioni delle query e viene eliminato usando tabelle replicate.This type of data movement operation slows query performance and is eliminated by using replicated tables. Per visualizzare i passaggi del piano di query, usare la vista del catalogo di sistema sys.dm_pdw_request_steps.To view query plan steps, use the sys.dm_pdw_request_steps system catalog view.

Nella query seguente sullo schema AdventureWorks, ad esempio, la tabella FactInternetSales è con distribuzione hash.For example, in following query against the AdventureWorks schema, the FactInternetSales table is hash-distributed. Le tabelle DimDate e DimSalesTerritory sono tabelle delle dimensioni più piccole.The DimDate and DimSalesTerritory tables are smaller dimension tables. Questa query restituisce le vendite totali in America del Nord per l'anno fiscale 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'

DimDate e DimSalesTerritory sono state ricreate come tabelle round robin.We re-created DimDate and DimSalesTerritory as round-robin tables. Di conseguenza, la query ha mostrato il piano di query seguente, che prevede più operazioni di spostamento di trasmissione:As a result, the query showed the following query plan, which has multiple broadcast move operations:

Piano di query round robin

DimDate e DimSalesTerritory sono state ricreate come tabelle replicate e su di esse è stata eseguita una query.We re-created DimDate and DimSalesTerritory as replicated tables, and ran the query again. Il piano di query risultante è molto più breve e non include spostamenti di trasmissione.The resulting query plan is much shorter and does not have any broadcast moves.

Piano di query di tabelle replicate

Considerazioni sulle prestazioni per la modifica di tabelle replicatePerformance considerations for modifying replicated tables

SQL Data Warehouse implementa una tabella replicata mantenendo una versione master della tabella.SQL Data Warehouse implements a replicated table by maintaining a master version of the table. Il servizio copia la versione master in un database di distribuzione in ogni nodo di calcolo.It copies the master version to one distribution database on each Compute node. Quando viene apportata una modifica, SQL Data Warehouse aggiorna prima la tabella master.When there is a change, SQL Data Warehouse first updates the master table. Esegue quindi la ricompilazione delle tabelle in ogni nodo di calcolo.Then it rebuilds the tables on each Compute node. La ricompilazione di una tabella replicata include la copia della tabella in ogni nodo di calcolo e quindi la compilazione degli indici.A rebuild of a replicated table includes copying the table to each Compute node and then building the indexes. Ad esempio una tabella replicata su DW400 ha 5 copie di dati.For example, a replicated table on a DW400 has 5 copies of the data. Una copia master e una copia completa in ogni nodo di calcolo.A master copy and a full copy on each Compute node. Tutti i dati vengono archiviati nei database di distribuzione.All data is stored in distribution databases. SQL Data Warehouse usa questo modello per supportare istruzioni di modifica dei dati più veloci e operazioni di scalabilità flessibili.SQL Data Warehouse uses this model to support faster data modification statements and flexible scaling operations.

Le compilazioni sono necessarie dopo le operazioni seguenti:Rebuilds are required after:

  • Vengono caricati o modificati datiData is loaded or modified
  • Il data warehouse viene ridimensionato in base a un livello diversoThe data warehouse is scaled to a different level
  • Viene aggiornata la definizione di tabellaTable definition is updated

Le ricompilazioni non sono necessarie dopo le operazioni seguenti:Rebuilds are not required after:

  • Operazione di sospensionePause operation
  • Operazione di ripresaResume operation

La ricompilazione non viene eseguita immediatamente dopo la modifica dei dati.The rebuild does not happen immediately after data is modified. Al contrario, la ricompilazione viene attivata la prima volta che una query esegue una selezione dalla tabella.Instead, the rebuild is triggered the first time a query selects from the table. La query che ha attivato la ricompilazione esegue la lettura immediatamente dalla versione master della tabella, mentre i dati vengono copiati in modo asincrono in ogni nodo di calcolo.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. Fino al completamento della copia dei dati, le query successive continueranno a usare la versione master della tabella.Until the data copy is complete, subsequent queries will continue to use the master version of the table. Se viene eseguita un'attività in base alla tabella replicata che forza un'altra ricompilazione, la copia dei dati viene invalidata e l'istruzione SELECT successiva attiverà di nuovo la copia dei dati.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.

Usare gli indici con moderazioneUse indexes conservatively

Alle tabelle replicate si applicano le procedure di indicizzazione standard.Standard indexing practices apply to replicated tables. SQL Data Warehouse ricompila ogni indice di tabella replicata come parte della ricompilazione.SQL Data Warehouse rebuilds each replicated table index as part of the rebuild. Usare gli indici solo quando le prestazioni sono più importanti del costo della ricompilazione degli indici.Only use indexes when the performance gain outweighs the cost of rebuilding the indexes.

Caricare in batch i datiBatch data loads

Quando si caricano dati in tabelle replicate, provare a ridurre al minimo le ricompilazioni eseguendo i caricamenti in batch.When loading data into replicated tables, try to minimize rebuilds by batching loads together. Eseguire tutti i caricamenti in batch prima di eseguire istruzioni di selezione.Perform all the batched loads before running select statements.

Ad esempio, questo modello di carico carica dati da quattro origini e richiama quattro ricompilazioni.For example, this load pattern loads data from four sources and invokes four rebuilds.

  • Caricamento dall'origine 1.Load from source 1.
  • L'istruzione di selezione attiva la ricompilazione 1.Select statement triggers rebuild 1.
  • Caricamento dall'origine 2.Load from source 2.
  • L'istruzione di selezione attiva la ricompilazione 2.Select statement triggers rebuild 2.
  • Caricamento dall'origine 3.Load from source 3.
  • L'istruzione di selezione attiva la ricompilazione 3.Select statement triggers rebuild 3.
  • Caricamento dall'origine 4.Load from source 4.
  • L'istruzione di selezione attiva la ricompilazione 4.Select statement triggers rebuild 4.

Ad esempio, questo modello di carico carica dati da quattro origini, ma richiama una sola ricompilazione.For example, this load pattern loads data from four sources, but only invokes one rebuild.

  • Caricamento dall'origine 1.Load from source 1.
  • Caricamento dall'origine 2.Load from source 2.
  • Caricamento dall'origine 3.Load from source 3.
  • Caricamento dall'origine 4.Load from source 4.
  • L'istruzione di selezione attiva la ricompilazione.Select statement triggers rebuild.

Ricompilare una tabella replicata dopo un caricamento in batchRebuild a replicated table after a batch load

Per garantire tempi di esecuzione di query coerenti, prendere in considerazione di forzare la compilazione delle tabelle replicate dopo un caricamento in batch.To ensure consistent query execution times, consider forcing the build of the replicated tables after a batch load. In caso contrario, la prima query continuerà a usare lo spostamento dei dati per completare la query.Otherwise, the first query will still use data movement to complete the query.

Questa query usa la DMV sys.pdw_replicated_table_cache_state per elencare le tabelle replicate che sono state modificate, ma non ricompilate.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'

Per attivare una ricompilazione, eseguire l'istruzione seguente in ogni tabella nell'output precedente.To trigger a rebuild, run the following statement on each table in the preceding output.

SELECT TOP 1 * FROM [ReplicatedTable]

Passaggi successiviNext steps

Per creare una tabella replicata, usare una di queste istruzioni:To create a replicated table, use one of these statements:

Per una panoramica delle tabelle distribuite, vedere Tabelle distribuite.For an overview of distributed tables, see distributed tables.