Linee guida per la progettazione di tabelle distribuite in Azure SQL Data WarehouseGuidance for designing distributed tables in Azure SQL Data Warehouse

Suggerimenti per la progettazione di tabelle con distribuzione hash e round robin in Azure SQL Data Warehouse.Recommendations for designing hash-distributed and round-robin distributed tables in Azure SQL Data Warehouse.

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 Azure SQL Data Warehouse - Architettura MPP (Massively Parallel Processing).For more information, see Azure SQL Data Warehouse - Massively Parallel Processing (MPP) architecture.

Che cos'è una tabella distribuita?What is a distributed table?

Una tabella distribuita viene visualizzata come una singola tabella, ma le righe al suo interno, in realtà, sono archiviate in 60 distribuzioni.A distributed table appears as a single table, but the rows are actually stored across 60 distributions. Le righe, inoltre, vengono distribuite con un algoritmo hash o round robin.The rows are distributed with a hash or round-robin algorithm.

Le tabella con distribuzione hash migliorano le prestazioni delle query nelle tabelle dei fatti di grandi dimensioni e rappresentano l'argomento principale di questo articolo.Hash-distributed tables improve query performance on large fact tables, and are the focus of this article. Le tabelle round robin consentono invece di aumentare la velocità di caricamento.Round-robin tables are useful for improving loading speed. Queste scelte di progettazione, quindi, possono contribuire in maniera significativa al miglioramento delle prestazioni delle query e di caricamento.These design choices have a significant impact on improving query and loading performance.

Un'altra opzione di archiviazione delle tabelle prevede la replica di una tabella di piccole dimensioni in tutti i nodi di calcolo.Another table storage option is to replicate a small table across all the Compute nodes. Per altre informazioni, vedere Linee guida di progettazione per l'uso di tabelle replicate in Azure SQL Data Warehouse.For more information, see Design guidance for replicated tables. Per scegliere rapidamente tra queste tre opzioni, vedere Tabelle distribuite nell'articolo di panoramica sulle tabelle.To quickly choose among the three options, see Distributed tables in the tables overview.

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?

Tabelle con distribuzione hashHash distributed

Una tabella con distribuzione hash distribuisce le righe della tabella nei vari nodi di calcolo usando una funzione hash deterministica per assegnare ogni riga a una distribuzione.A hash-distributed table distributes table rows across the Compute nodes by using a deterministic hash function to assign each row to one distribution.

Tabella distribuitaDistributed table

Poiché valori identici eseguono sempre l'hash nella stessa distribuzione, nel data warehouse sono integrate informazioni sulla posizione delle righe.Since identical values always hash to the same distribution, the data warehouse has built-in knowledge of the row locations. SQL Data Warehouse usa queste informazioni per ridurre al minimo lo spostamento dei dati durante le query e, di conseguenza, migliorare le prestazioni complessive delle query.SQL Data Warehouse uses this knowledge to minimize data movement during queries, which improves query performance.

Le tabelle con distribuzione hash sono particolarmente indicate per le tabelle dei fatti di grandi dimensioni in uno schema star.Hash-distributed tables work well for large fact tables in a star schema. Possono contenere un numero molto elevato di righe e conseguire comunque prestazioni elevate.They can have very large numbers of rows and still achieve high performance. È necessario, ovviamente, considerare anche alcuni aspetti di progettazione per ottenere le prestazioni che il sistema distribuito è in grado di offrire.There are, of course, some design considerations that help you to get the performance the distributed system is designed to provide. Uno di questi riguarda la scelta di una colonna di distribuzione appropriata, illustrata in questo articolo.Choosing a good distribution column is one such consideration that is described in this article.

Valutare l'opportunità di usare una tabella con distribuzione hash se:Consider using a hash-distributed table when:

  • La dimensione della tabella su disco è superiore a 2 GB.The table size on disk is more than 2 GB.
  • La tabella prevede frequenti operazioni di inserimento, aggiornamento ed eliminazione.The table has frequent insert, update, and delete operations.

Distribuzione round robinRound-robin distributed

Una tabella con distribuzione round robin distribuisce le righe della tabella in modo uniforme tra tutte le distribuzioni.A round-robin distributed table distributes table rows evenly across all distributions. L'assegnazione delle righe alle distribuzioni è casuale.The assignment of rows to distributions is random. A differenza delle tabelle con distribuzione hash, inoltre, non è garantito che valori identici vengano assegnati alla stessa distribuzione.Unlike hash-distributed tables, rows with equal values are not guaranteed to be assigned to the same distribution.

Di conseguenza, a volte il sistema deve richiamare un'operazione di spostamento dei dati per organizzare meglio i dati e poter risolvere una query.As a result, the system sometimes needs to invoke a data movement operation to better organize your data before it can resolve a query. Questo passaggio aggiuntivo può rallentare le query.This extra step can slow down your queries. L'aggiunta di una tabella con distribuzione round robin, ad esempio, richiede una ridistribuzione dei dati, con una conseguente riduzione delle prestazioni.For example, joining a round-robin table usually requires reshuffling the rows, which is a performance hit.

Valutare l'opportunità di usare la distribuzione round robin per una tabella negli scenari seguenti:Consider using the round-robin distribution for your table in the following scenarios:

  • Quando si inizia come punto di partenza semplice (impostazione predefinita)When getting started as a simple starting point since it is the default
  • Se non è presente una chiave di join ovvia.If there is no obvious joining key
  • Se non è presente una colonna candidata ottimale per la distribuzione hash della tabella.If there is not good candidate column for hash distributing the table
  • Se la tabella non condivide una chiave di join comune con altre tabelle.If the table does not share a common join key with other tables
  • Se il join è meno significativo di altri join nella query.If the join is less significant than other joins in the query
  • Quando si tratta di una tabella di staging temporaneo.When the table is a temporary staging table

L'esercitazione relativa al caricamento dei dati relativi ai taxi di New York in Azure SQL Data Warehouse illustra un esempio di caricamento dei dati in una tabella di staging con distribuzione round robin.The tutorial Load New York taxicab data to Azure SQL Data Warehouse gives an example of loading data into a round-robin staging table.

Scelta di una colonna di distribuzioneChoosing a distribution column

Nelle tabelle con distribuzione hash è presente una colonna di distribuzione che rappresenta la chiave hash.A hash-distributed table has a distribution column that is the hash key. Il codice seguente, ad esempio, crea una tabella con distribuzione hash con ProductKey come colonna di distribuzione.For example, the following code creates a hash-distributed table with ProductKey as the distribution column.

CREATE TABLE [dbo].[FactInternetSales]
(   [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,  DISTRIBUTION = HASH([ProductKey])
)
;

La scelta della colonna di distribuzione è una decisione di progettazione importante, poiché i valori presenti in questa colonna determinano il modo in cui vengono distribuite le righe.Choosing a distribution column is an important design decision since the values in this column determine how the rows are distributed. La scelta ottimale dipende da vari fattori e, in genere, comporta alcuni compromessi.The best choice depends on several factors, and usually involves tradeoffs. Se, tuttavia, non si riesce a scegliere subito la tabella ottimale, è possibile usare CREATE TABLE AS SELECT (CTAS) per ricreare la tabella con una colonna di distribuzione diversa.However, if you don't choose the best column the first time, you can use CREATE TABLE AS SELECT (CTAS) to re-create the table with a different distribution column.

Scegliere una colonna di distribuzione che non richiede aggiornamentiChoose a distribution column that does not require updates

Non è possibile aggiornare una colonna di distribuzione a meno che non si elimini la riga e si inserisca una nuova riga con i valori aggiornati.You cannot update a distribution column unless you delete the row and insert a new row with the updated values. È consigliabile quindi selezionare una colonna con valori statici.Therefore, select a column with static values.

Scegliere una colonna di distribuzione in modo che i dati vengano distribuiti in modo uniformeChoose a distribution column with data that distributes evenly

Per ottenere prestazioni ottimali, tutte le distribuzioni devono avere approssimativamente lo stesso numero di righe.For best performance, all of the distributions should have approximately the same number of rows. Se una o più distribuzioni hanno un numero sproporzionato di righe, alcune distribuzioni completano la propria porzione di query parallela prima delle altre.When one or more distributions have a disproportionate number of rows, some distributions finish their portion of a parallel query before others. La query, quindi, viene completata solo nel momento in cui tutte le distribuzioni hanno terminato l'elaborazione e la velocità di ogni query corrisponde di fatto alla distribuzione più lenta.Since the query can't complete until all distributions have finished processing, each query is only as fast as the slowest distribution.

  • L'asimmetria dei dati significa che i dati non vengono distribuiti in modo uniforme tra le distribuzioniData skew means the data is not distributed evenly across the distributions
  • L'asimmetria di elaborazione significa che alcune distribuzioni richiedono più tempo di altre per eseguire query parallele.Processing skew means that some distributions take longer than others when running parallel queries. Questa situazione può verificarsi in caso di asimmetria dei dati.This can happen when the data is skewed.

Per bilanciare l'elaborazione parallela, selezionare una colonna di distribuzione che:To balance the parallel processing, select a distribution column that:

  • Contenga molti valori univoci.Has many unique values. La colonna può includere alcuni valori duplicati,The column can have some duplicate values. ma tutte le righe con lo stesso valore vengono assegnate alla stessa distribuzione.However, all rows with the same value are assigned to the same distribution. Poiché sono presenti 60 distribuzioni, la colonna deve avere almeno 60 valori univoci.Since there are 60 distributions, the column should have at least 60 unique values. In genere, tuttavia, il numero di valori univoci è molto più elevato.Usually the number of unique values is much greater.
  • Non contenga valori null o ne contenga un numero limitato.Does not have NULLs, or has only a few NULLs. Come esempio estremo, se tutti i valori della colonna sono NULL, tutte le righe vengono assegnate alla stessa distribuzione.For an extreme example, if all values in the column are NULL, all the rows are assigned to the same distribution. L'elaborazione della query, quindi, è assegnata a un'unica distribuzione e non può usufruire dei vantaggi dell'elaborazione in parallelo.As a result, query processing is skewed to one distribution, and does not benefit from parallel processing.
  • Non è una colonna data.Is not a date column. Tutti i dati relativi alla stessa data vengono inseriti nella stessa distribuzione.All data for the same date lands in the same distribution. In questo modo, se più utenti filtrano in base alla stessa data, l'intero lavoro di elaborazione viene eseguito solo da una delle 60 distribuzioni.If several users are all filtering on the same date, then only 1 of the 60 distributions do all the processing work.

Scegliere una colonna di distribuzione che riduca al minimo lo spostamento dei datiChoose a distribution column that minimizes data movement

Per ottenere il risultato corretto, è possibile che le query spostino i dati da un nodo di calcolo a un altro.To get the correct query result queries might move data from one Compute node to another. Lo spostamento dei dati si verifica in genere quando le query hanno join e aggregazioni in tabelle distribuite.Data movement commonly happens when queries have joins and aggregations on distributed tables. Scegliere una colonna di distribuzione che contribuisca a ridurre lo spostamento dei dati è una delle strategie più importanti per ottimizzare le prestazioni di SQL Data Warehouse.Choosing a distribution column that helps minimize data movement is one of the most important strategies for optimizing performance of your SQL Data Warehouse.

Per ridurre al minimo lo spostamento dei dati, selezionare una colonna di distribuzione che:To minimize data movement, select a distribution column that:

  • Viene usata in clausole JOIN, GROUP BY, DISTINCT, OVER e HAVING.Is used in JOIN, GROUP BY, DISTINCT, OVER, and HAVING clauses. Se due tabelle dei fatti di grandi dimensioni hanno join frequenti, le prestazioni delle query migliorano se si distribuiscono entrambe le tabelle in una delle colonne di join.When two large fact tables have frequent joins, query performance improves when you distribute both tables on one of the join columns. Se una tabella non viene usata in operazioni di join, valutare l'opportunità di distribuire la tabella in una colonna che si trova spesso nella clausola GROUP BY.When a table is not used in joins, consider distributing the table on a column that is frequently in the GROUP BY clause.
  • Non viene usata in clausole WHERE.Is not used in WHERE clauses. Questo potrebbe limitare la query in modo che non venga eseguita in tutte le distribuzioni.This could narrow the query to not run on all the distributions.
  • Non è una colonna dati.Is not a date column. Le clausole WHERE filtrano spesso per data.WHERE clauses often filter by date. Quando si verifica questa situazione, l'intera elaborazione può essere eseguita solo su alcune distribuzioni.When this happens, all the processing could run on only a few distributions.

Cosa fare quando nessuna delle colonne è una colonna di distribuzione appropriataWhat to do when none of the columns are a good distribution column

Se nessuna delle colonne ha valori distinti sufficienti per una colonna di distribuzione, è possibile creare una nuova colonna come composizione di uno o più valori.If none of your columns have enough distinct values for a distribution column, you can create a new column as a composite of one or more values. Per evitare spostamenti di dati durante l'esecuzione di query, usare la colonna di distribuzione composita come colonna di join nelle query.To avoid data movement during query execution, use the composite distribution column as a join column in queries.

Dopo aver progettato una tabella con distribuzione round robin, è necessario caricare i dati nella tabella.Once you design a hash-distributed table, the next step is to load data into the table. Per informazioni sul caricamento, vedere l'articolo di panoramica sul caricamento.For loading guidance, see Loading overview.

Come stabilire se una colonna di distribuzione è appropriataHow to tell if your distribution column is a good choice

Dopo aver caricato i dati in una tabella con distribuzione hash, verificare che le righe siano state distribuite in modo uniforme tra le 60 distribuzioni.After data is loaded into a hash-distributed table, check to see how evenly the rows are distributed across the 60 distributions. Una variazione fino al 10% del numero di righe assegnate a ogni distribuzione non influisce in modo significativo sulle prestazioni.The rows per distribution can vary up to 10% without a noticeable impact on performance.

Determinare se la tabella presenta un'asimmetria dei datiDetermine if the table has data skew

Per controllare se è presente un'asimmetria dei dati, è possibile usare DBCC PDW_SHOWSPACEUSED.A quick way to check for data skew is to use DBCC PDW_SHOWSPACEUSED. Il codice SQL seguente restituisce il numero di righe di tabella archiviate in ognuna delle 60 distribuzioni.The following SQL code returns the number of table rows that are stored in each of the 60 distributions. Per ottenere prestazioni bilanciate, le righe nella tabella distribuita devono essere suddivise in modo uniforme tra tutte le distribuzioni.For balanced performance, the rows in your distributed table should be spread evenly across all the distributions.

-- Find data skew for a distributed table
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

Per identificare quali tabelle presentano un'asimmetria dei dati superiore al 10%:To identify which tables have more than 10% data skew:

  1. Creare la visualizzazione dbo.vTableSizes illustrata nell'articolo di panoramica delle tabelle.Create the view dbo.vTableSizes that is shown in the Tables overview article.
  2. Eseguire questa query:Run the following query:
select *
from dbo.vTableSizes
where two_part_name in
    (
    select two_part_name
    from dbo.vTableSizes
    where row_count > 0
    group by two_part_name
    having min(row_count * 1.000)/max(row_count * 1.000) > .10
    )
order by two_part_name, row_count
;

Verificare lo spostamento dei dati nei piani di queryCheck query plans for data movement

Se la colonna di distribuzione è appropriata, i join e le aggregazioni presentano uno spostamento minimo dei dati.A good distribution column enables joins and aggregations to have minimal data movement. Questo aspetto influisce sul modo in cui devono essere scritti i join.This affects the way joins should be written. Per ottenere uno spostamento minimo dei dati per un join in due tabelle con distribuzione hash, una delle colonne di join deve essere una colonna di distribuzione.To get minimal data movement for a join on two hash-distributed tables, one of the join columns needs to be the distribution column. Se due tabelle con distribuzione hash creano un join in una colonna di distribuzione dello stesso tipo di dati, il join non richiede lo spostamento dei dati.When two hash-distributed tables join on a distribution column of the same data type, the join does not require data movement. I join possono usare colonne aggiuntive senza richiedere uno spostamento dei dati.Joins can use additional columns without incurring data movement.

Per evitare lo spostamento dei dati durante un join:To avoid data movement during a join:

  • È necessario eseguire la distribuzione hash delle tabelle coinvolte nel join in una delle colonne che fanno parte del join.The tables involved in the join must be hash distributed on one of the columns participating in the join.
  • I tipi di dati delle colonne di join nelle due tabelle devono corrispondere.The data types of the join columns must match between both tables.
  • Le colonne devono essere unite con un operatore Uguale.The columns must be joined with an equals operator.
  • Il tipo di join può non essere CROSS JOIN.The join type may not be a CROSS JOIN.

Per vedere se nelle query si verifica uno spostamento dei dati, è possibile controllare il piano di query.To see if queries are experiencing data movement, you can look at the query plan.

Risolvere un problema relativo a una colonna di distribuzioneResolve a distribution column problem

Non è necessario risolvere tutti i casi di asimmetria dei dati.It is not necessary to resolve all cases of data skew. La distribuzione è essenzialmente l'individuazione del giusto equilibrio fra la minimizzazione dell'asimmetria dei dati e la minimizzazione dello spostamento dei dati.Distributing data is a matter of finding the right balance between minimizing data skew and data movement. Non è sempre possibile ridurre al minimo entrambi i valori.It is not always possible to minimize both data skew and data movement. In alcuni casi, il vantaggio di uno spostamento dei dati minimo può essere quello di compensare l'impatto negativo dell'asimmetria dei dati.Sometimes the benefit of having the minimal data movement might outweigh the impact of having data skew.

Per decidere se sia necessario risolvere la differenza dati di una tabella, è necessario conoscere nel modo più completo possibile i volumi di dati e le query del carico di lavoro.To decide if you should resolve data skew in a table, you should understand as much as possible about the data volumes and queries in your workload. È possibile seguire la procedura descritta nell'articolo Monitoraggio delle query per monitorare l'impatto dell'asimmetria sulle prestazioni delle query.You can use the steps in the Query monitoring article to monitor the impact of skew on query performance. In particolare, è possibile scoprire quanto tempo richiede il completamento di query di grandi dimensioni in singole distribuzioni.Specifically, look for how long it takes large queries to complete on individual distributions.

Non essendo possibile modificare la colonna di distribuzione in una tabella esistente, il modo più comune per risolvere l'asimmetria dei dati consiste nel ricreare la tabella con una colonna di distribuzione diversa.Since you cannot change the distribution column on an existing table, the typical way to resolve data skew is to re-create the table with a different distribution column.

Ricreare la tabella con una nuova colonna di distribuzioneRe-create the table with a new distribution column

Questo esempio usa CREATE TABLE AS SELECT per ricreare una tabella con una colonna di distribuzione hash diversa.This example uses CREATE TABLE AS SELECT to re-create a table with a different hash distribution column.

CREATE TABLE [dbo].[FactInternetSales_CustomerKey]
WITH (  CLUSTERED COLUMNSTORE INDEX
     ,  DISTRIBUTION =  HASH([CustomerKey])
     ,  PARTITION       ( [OrderDateKey] RANGE RIGHT FOR VALUES (   20000101, 20010101, 20020101, 20030101
                                                                ,   20040101, 20050101, 20060101, 20070101
                                                                ,   20080101, 20090101, 20100101, 20110101
                                                                ,   20120101, 20130101, 20140101, 20150101
                                                                ,   20160101, 20170101, 20180101, 20190101
                                                                ,   20200101, 20210101, 20220101, 20230101
                                                                ,   20240101, 20250101, 20260101, 20270101
                                                                ,   20280101, 20290101
                                                                )
                        )
    )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
OPTION  (LABEL  = 'CTAS : FactInternetSales_CustomerKey')
;

--Create statistics on new table
CREATE STATISTICS [ProductKey] ON [FactInternetSales_CustomerKey] ([ProductKey]);
CREATE STATISTICS [OrderDateKey] ON [FactInternetSales_CustomerKey] ([OrderDateKey]);
CREATE STATISTICS [CustomerKey] ON [FactInternetSales_CustomerKey] ([CustomerKey]);
CREATE STATISTICS [PromotionKey] ON [FactInternetSales_CustomerKey] ([PromotionKey]);
CREATE STATISTICS [SalesOrderNumber] ON [FactInternetSales_CustomerKey] ([SalesOrderNumber]);
CREATE STATISTICS [OrderQuantity] ON [FactInternetSales_CustomerKey] ([OrderQuantity]);
CREATE STATISTICS [UnitPrice] ON [FactInternetSales_CustomerKey] ([UnitPrice]);
CREATE STATISTICS [SalesAmount] ON [FactInternetSales_CustomerKey] ([SalesAmount]);

--Rename the tables
RENAME OBJECT [dbo].[FactInternetSales] TO [FactInternetSales_ProductKey];
RENAME OBJECT [dbo].[FactInternetSales_CustomerKey] TO [FactInternetSales];

Passaggi successiviNext steps

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