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.

Nota

La funzionalità delle tabelle replicate è attualmente disponibile in anteprima pubblica.The replicated table feature is currently in public preview. Alcuni comportamenti sono soggetti a modifiche.Some behaviors are subject to change.

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. Ad esempio, un join in tabelle con distribuzione hash richiede lo spostamento dei dati quando le colonne di join non si trovano nella stessa colonna di distribuzione.For example, a join on hash-distributed tables requires data movement when the joining columns are not the same distribution column. Se una delle tabelle con distribuzione hash ha dimensioni ridotte, provare una tabella replicata.If one of the hash-distributed tables is small, consider a replicated table. Un join in una tabella round robin richiede lo spostamento dei dati.A join on a round-robin table requires data movement. È 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.

Provare a convertire una tabella distribuita esistente in una tabella replicata nei casi seguenti:Consider converting an existing distributed table to a replicated table when:

  • I piani di query usano operazioni di spostamento dei dati che trasmettono i dati a tutti i nodi di calcolo.Query plans use data movement operations that broadcast the data to all the Compute nodes. L'operazione BroadcastMoveOperation è costosa e rallenta le prestazioni delle query.The BroadcastMoveOperation is expensive and slows query performance. 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.

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 hash 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 hash 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 presenti nelle colonne richieste.When a query requires data movement, SQL Data Warehouse only moves data in 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 hash o una distribuzione round robin.In this example, supplier can be hash-distributed or 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. Richiede quindi una ricompilazione delle tabelle in ogni nodo di calcolo.Then it requires a rebuild of the tables on each Compute node. Una ricompilazione di una tabella replicata include la copia della tabella in ogni nodo di calcolo e quindi la ricompilazione degli indici.A rebuild of a replicated table includes copying the table to each Compute node and then rebuilding the indexes.

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 di servizio diversoThe data warehouse is scaled to a different service 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. Nell'istruzione di selezione iniziale dalla tabella sono inclusi i passaggi per ricompilare la tabella replicata.Within the initial select statement from the table are steps to rebuild the replicated table. Poiché la ricompilazione viene eseguita all'interno della query, l'impatto dell'istruzione di selezione iniziale può essere significativo, a seconda delle dimensioni della tabella.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. Se sono interessate più tabelle replicate che richiedono una ricompilazione, ogni copia viene ricompilata in serie come passaggi all'interno dell'istruzione.If multiple replicated tables are involved that need a rebuild, each copy is rebuilt serially as steps within the statement. Per mantenere la coerenza dei dati durante la ricompilazione della tabella replicata, viene applicato un blocco esclusivo alla tabella.To maintain data consistency during the rebuild of the replicated table an exclusive lock is taken on the table. Il blocco impedisce qualsiasi accesso alla tabella durante la ricompilazione.The lock prevents all access to the table for the duration of the rebuild.

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, è consigliabile forzare un aggiornamento delle tabelle replicate dopo un caricamento in batch.To ensure consistent query execution times, we recommend forcing a refresh of the replicated tables after a batch load. In caso contrario, la prima query deve attendere l'aggiornamento delle tabelle, che include la ricompilazione degli indici.Otherwise, the first query must wait for the tables to refresh, which includes rebuilding the indexes. A seconda delle dimensioni e del numero di tabelle replicate interessate, l'impatto sulle prestazioni può essere significativo.Depending on the size and number of replicated tables affected, the performance impact can be significant.

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 forzare una ricompilazione, eseguire l'istruzione seguente in ogni tabella nell'output precedente.To force 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.