Linee guida di progettazione per l'uso di tabelle replicate nel pool Synapse SQL

Articolo
01/10/2024

Questo articolo offre alcuni consigli per la progettazione di tabelle replicate nel pool Synapse SQL. Usare questi consigli per migliorare le prestazioni delle query riducendo lo spostamento dei dati e la complessità delle query stesse.

Prerequisiti

Questo articolo presuppone una certa familiarità con i concetti di distribuzione e spostamento dei dati nel pool SQL. Per altre informazioni, vedere l'articolo relativo all'architettura.

Come parte della progettazione di tabelle, è necessario comprendere quanto più possibile i propri dati e il modo in cui vengono eseguite query sui dati. Ad esempio, considerare queste domande:

Quali sono le dimensioni della tabella?
Quanto spesso viene aggiornata la tabella?
Sono presenti tabelle dei fatti e delle dimensioni in un pool SQL?

Che cos'è una tabella replicata?

Una tabella replicata include una copia completa della tabella accessibile in ogni nodo di calcolo. La replica di una tabella elimina la necessità di trasferire dati tra i nodi di calcolo prima di un join o un'aggregazione. Poiché la tabella ha più copie, le tabelle replicate funzionano meglio quando le dimensioni delle tabelle sono inferiori a 2 GB, già compresse. 2 GB non è un limite rigido. Se i dati sono statici e non cambiano, è possibile replicare tabelle di dimensioni maggiori.

Il diagramma seguente mostra una tabella replicata accessibile in ogni nodo di calcolo. Nel pool SQL la tabella replicata viene copiata completamente in un database di distribuzione in ogni nodo di calcolo.

Le tabelle replicate sono adatte alle tabelle delle dimensioni in uno schema star. Le tabelle delle dimensioni vengono in genere unite a tabelle dei fatti, distribuite in modo diverso rispetto alla tabella delle dimensioni. Le dimensioni hanno in genere dimensioni tali da rendere possibile l'archiviazione e la conservazione di più copie. Nelle dimensioni sono archiviati dati descrittivi che cambiano raramente, come il nome e l'indirizzo di un cliente e i dettagli del prodotto. La rarità delle modifiche dei dati comporta un numero minore di interventi di manutenzione della tabella replicata.

Provare a usare una tabella replicata nei casi seguenti:

La dimensione della tabella su disco è inferiore a 2 GB, indipendentemente dal numero di righe. Per individuare la dimensione di una tabella, usare il comando DBCC PDW_SHOWSPACEUSED: DBCC PDW_SHOWSPACEUSED('ReplTableCandidate').
La tabella viene usata in join che richiederebbero altrimenti lo spostamento dei dati. 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. Se una delle tabelle ha dimensioni ridotte, provare una tabella replicata. È consigliabile usare tabelle replicate invece di tabelle round robin nella maggior parte dei casi. Per visualizzare le operazioni di spostamento dei dati nei piani di query, usare sys.dm_pdw_request_steps. L'operazione tipica di spostamento di dati che può essere eliminata usando una tabella replicata è BroadcastMoveOperation.

Le tabelle replicate possono essere causa di prestazioni delle query non ottimali nei casi seguenti:

La tabella prevede frequenti operazioni di inserimento, aggiornamento ed eliminazione. Le operazioni di Data Manipulation Language (DML) richiedono una ricompilazione della tabella replicata. La ricompilazione causa spesso un rallentamento delle prestazioni.
Il pool SQL viene ridimensionato di frequente. Il ridimensionamento di un pool SQL cambia il numero di nodi di calcolo. Questo comporta la ricompilazione della tabella replicata.
La tabella include un numero elevato di colonne, ma le operazioni sui dati accedono in genere solo a una quantità ridotta di colonne. 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. Quando una query richiede lo spostamento dei dati, il pool SQL sposta solo i dati per le colonne richieste.

Suggerimento

Per altre indicazioni sull'indicizzazione e sulle tabelle replicate, vedere il foglio informativo per il pool SQL dedicato (in precedenza SQL Data Warehouse) in Azure Synapse Analytics.

Usare tabelle replicate con predicati di query semplici

Prima di scegliere di distribuire o replicare una tabella, considerare i tipi di query che si prevede di eseguire sulla tabella. Se possibile:

Usare tabelle replicate per query con predicati di query semplici, come le query di uguaglianza o disuguaglianza.
Usare tabelle distribuite per query con predicati di query complessi, come LIKE o 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. Ad esempio, le query che eseguono calcoli in ogni riga di una tabella hanno prestazioni migliori nelle tabelle distribuite che non nelle tabelle replicate. 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. Il calcolo aggiuntivo può rallentare le prestazioni delle query.

Questa query, ad esempio, ha un predicato complesso. Viene eseguita più rapidamente se i dati sono in una tabella distribuita anziché in una tabella replicata. In questo esempio i dati possono avere una distribuzione round robin.

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

Convertire le tabelle round robin esistenti in tabelle replicate

Se sono già presenti tabelle round robin che soddisfano i criteri specificati in questo articolo, è consigliabile convertirle in tabelle replicate. Le tabelle replicate migliorano le prestazioni rispetto alle tabelle round robin, perché eliminano la necessità di spostare i dati. Una tabella round robin richiede lo spostamento dei dati per i join.

In questo esempio viene usato CTAS per modificare la DimSalesTerritory tabella in una tabella replicata. Questo esempio funziona indipendentemente dal fatto che DimSalesTerritory sia distribuito tramite hash o round robin.

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

-- 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 replicate

Una tabella replicata non richiede lo spostamento dei dati per i join, perché l'intera tabella è già presente in ogni nodo di calcolo. Se viene eseguita una distribuzione round robin delle tabelle delle dimensioni, un join copia interamente la tabella delle dimensioni in ogni nodo di calcolo. Per spostare i dati, il piano di query contiene un'operazione chiamata BroadcastMoveOperation. Questo tipo di operazione di spostamento dei dati rallenta le prestazioni delle query e viene eliminato usando tabelle replicate. Per visualizzare i passaggi del piano di query, usare la vista del catalogo di sistema sys.dm_pdw_request_steps.

Ad esempio, nella query AdventureWorks seguente sullo schema la FactInternetSales tabella è distribuita tramite hash. Le tabelle DimDate e DimSalesTerritory sono tabelle delle dimensioni più piccole. Questa query restituisce le vendite totali in America del Nord per l'anno fiscale 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. Di conseguenza, la query ha mostrato il piano di query seguente, che prevede più operazioni di spostamento di trasmissione:

Round-robin query plan

DimDate e DimSalesTerritory sono state ricreate come tabelle replicate e su di esse è stata eseguita una query. Il piano di query risultante è molto più breve e non include spostamenti di trasmissione.

Replicated query plan

Considerazioni sulle prestazioni per la modifica di tabelle replicate

Il pool SQL implementa una tabella replicata mantenendo una versione master della tabella. Copia la versione master nel primo database di distribuzione in ogni nodo di calcolo. Quando viene eseguita una modifica, prima viene aggiornata la versione master e quindi le tabelle in ogni nodo di calcolo vengono ricompilate. La ricompilazione di una tabella replicata include la copia della tabella in ogni nodo di calcolo e quindi la compilazione degli indici. Ad esempio, una tabella replicata in un DW2000c ha cinque copie dei dati. Una copia master e una copia completa in ogni nodo di calcolo. Tutti i dati vengono archiviati nei database di distribuzione. Il pool SQL usa questo modello per supportare istruzioni di modifica dei dati più veloci e operazioni di ridimensionamento flessibili.

Le ricompilazione asincrone vengono attivate dalla prima query sulla tabella replicata dopo:

Vengono caricati o modificati dati
L'istanza di Synapse SQL viene ridimensionata a un livello diverso
Viene aggiornata la definizione di tabella

Le ricompilazioni non sono necessarie dopo le operazioni seguenti:

Operazione di sospensione
Operazione di ripresa

La ricompilazione non viene eseguita immediatamente dopo la modifica dei dati. Al contrario, la ricompilazione viene attivata la prima volta che una query esegue una selezione dalla tabella. 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. Fino al completamento della copia dei dati, le query successive continueranno a usare la versione master della tabella. 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.

Usare gli indici con moderazione

Alle tabelle replicate si applicano le procedure di indicizzazione standard. Il pool SQL ricompila ogni indice di tabella replicata nell'ambito della ricompilazione. Usare gli indici solo quando le prestazioni sono più importanti del costo della ricompilazione degli indici.

Caricamento dati in batch

Quando si caricano dati in tabelle replicate, provare a ridurre al minimo le ricompilazioni eseguendo i caricamenti in batch. Eseguire tutti i caricamenti in batch prima di eseguire istruzioni di selezione.

Ad esempio, questo modello di carico carica dati da quattro origini e richiama quattro ricompilazioni.

Caricamento dall'origine 1.
L'istruzione di selezione attiva la ricompilazione 1.
Caricamento dall'origine 2.
L'istruzione di selezione attiva la ricompilazione 2.
Caricamento dall'origine 3.
L'istruzione di selezione attiva la ricompilazione 3.
Caricamento dall'origine 4.
L'istruzione di selezione attiva la ricompilazione 4.

Ad esempio, questo modello di carico carica dati da quattro origini, ma richiama una sola ricompilazione.

Caricamento dall'origine 1.
Caricamento dall'origine 2.
Caricamento dall'origine 3.
Caricamento dall'origine 4.
L'istruzione di selezione attiva la ricompilazione.

Ricompilare una tabella replicata dopo un caricamento in batch

Per garantire tempi di esecuzione di query coerenti, prendere in considerazione di forzare la compilazione delle tabelle replicate dopo un caricamento in batch. In caso contrario, la prima query continuerà a usare lo spostamento dei dati per completare la query.

L'operazione Costruisci tabella cache replicata' può eseguire contemporaneamente fino a due operazioni. Ad esempio, se si tenta di ricompilare la cache per cinque tabelle, il sistema utilizzerà un staticrc20 (che non può essere modificato) per compilare simultaneamente due tabelle al momento. Pertanto, è consigliabile evitare di usare tabelle replicate di grandi dimensioni superiori a 2 GB, in quanto ciò potrebbe rallentare la ricompilazione della cache tra i nodi e aumentare il tempo complessivo.

Questa query usa la DMV sys.pdw_replicated_table_cache_state per elencare le tabelle replicate che sono state modificate, ma non ricompilate.

SELECT SchemaName = SCHEMA_NAME(t.schema_id)
 , [ReplicatedTable] = t.[name]
 , [RebuildStatement] = 'SELECT TOP 1 * FROM ' + '[' + SCHEMA_NAME(t.schema_id) + '].[' + 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.

SELECT TOP 1 * FROM [ReplicatedTable]

Nota

Se si prevede di ricompilare le statistiche della tabella replicata non memorizzata nella cache, assicurarsi di aggiornare le statistiche prima di attivare la cache. L'aggiornamento delle statistiche invaliderà la cache, quindi la sequenza è importante.

Esempio: iniziare con UPDATE STATISTICS, quindi attivare la ricompilazione della cache. Negli esempi seguenti, l'esempio corretto aggiorna le statistiche e quindi attiva la ricompilazione della cache.

-- Incorrect sequence. Ensure that the rebuild operation is the last statement within the batch.
BEGIN
SELECT TOP 1 * FROM [ReplicatedTable]

UPDATE STATISTICS [ReplicatedTable]
END

-- Correct sequence. Ensure that the rebuild operation is the last statement within the batch.
BEGIN
UPDATE STATISTICS [ReplicatedTable]

SELECT TOP 1 * FROM [ReplicatedTable]
END

Per monitorare il processo di ricompilazione, è possibile usare sys.dm_pdw_exec_requests, dove command inizierà con 'BuildReplicatedTableCache'. Ad esempio:

-- Monitor Build Replicated Cache
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE command like 'BuildReplicatedTableCache%'