Indici delle tabelle nel pool SQL dedicato di Azure Synapse Analytics

Raccomandazioni ed esempi per l'indicizzazione di tabelle nel pool SQL dedicato in Azure Synapse Analytics.

Tipi di indice

Il pool SQL dedicato offre diverse opzioni di indicizzazione, tra cui indici columnstore con cluster, indici clusterizzati e indici non clusterizzati, e un'opzione senza indice nota anche come heap.

Per creare una tabella con un indice, vedere la documentazione su CREATE TABLE (pool SQL dedicato).

Indici columnstore cluster

Per impostazione predefinita, il pool SQL dedicato crea un indice columnstore raggruppato quando non viene specificata alcuna opzione di indice per una tabella. Le tabelle columnstore clusterizzate offrono sia il livello massimo di compressione dei dati che le migliori prestazioni globali delle query. Le tabelle columnstore cluster garantiscono in genere prestazioni migliori rispetto alle tabelle heap o con indice cluster e rappresentano la scelta migliore in caso di tabelle di grandi dimensioni. Per questi motivi, è opportuno iniziare con l'indice columnstore in cluster quando non si è certi del modo in cui indicizzare una tabella.

Per creare una tabella columnstore cluster, specificare CLUSTERED COLUMNSTORE INDEX nella clausola WITH o lasciare disattivata la clausola WITH:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( CLUSTERED COLUMNSTORE INDEX );

Esistono alcuni scenari in cui columnstore cluster potrebbe non essere un'opzione valida:

• Le tabelle di tipo columnstore non supportano i tipi di dati varchar(max), nvarchar(max) e varbinary(max). È consigliabile usare tabelle heap o di indici cluster.
• Le tabelle columnstore potrebbero risultare meno efficienti per i dati temporanei. Si considerino heap e tabelle temporanee.
• Tabelle di piccole dimensioni con meno di 60 milioni di righe. È consigliabile usare tabelle heap.

Tabelle heap

Quando si effettua temporaneamente l'atterraggio dei dati nel pool SQL dedicato, è possibile che l'uso di una tabella heap renda più veloce il processo complessivo. Questo perché il caricamento negli heap è più veloce rispetto alle tabelle degli indici e in alcuni casi è possibile eseguire la lettura successiva dalla cache. Se si caricano i dati solo per inserirli temporaneamente prima di eseguire altre trasformazioni, il caricamento della tabella in una tabella heap è molto più rapido del caricamento dei dati in una tabella columnstore cluster. Anche il caricamento dei dati in una tabella temporanea risulta più veloce del caricamento di una tabella in un archivio permanente. Dopo il caricamento dei dati, è possibile creare indici nella tabella per ottenere prestazioni di query più veloci.

Le tabelle columnstore del cluster iniziano a ottenere una compressione ottimale una volta che sono presenti più di 60 milioni di righe. Per le tabelle di ricerca di piccole dimensioni, meno di 60 milioni di righe, è consigliabile usare l'indice HEAP o cluster per ottenere prestazioni di query più veloci.

Per creare una tabella heap, specificare HEAP nella clausola WITH:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( HEAP );

Indici clusterizzati e non clusterizzati

Gli indici cluster possono offrire prestazioni migliori rispetto alle tabelle columnstore cluster quando è necessario recuperare rapidamente una singola riga. Per le query in cui è necessario eseguire la ricerca in una sola riga o comunque in un numero di righe molto ridotto a velocità elevata, è consigliabile usare indici cluster o indici non cluster secondari. Gli indici cluster hanno lo svantaggio di risultare utili solo per le query con un filtro molto selettivo nella colonna dell'indice cluster. Per migliorare i filtri nelle altre colonne è possibile aggiungere un indice non cluster a tali colonne. Tuttavia, ogni indice che viene aggiunto a una tabella richiede spazio e tempo di elaborazione aggiuntivi.

Per creare una tabella con indice cluster, è necessario specificare CLUSTERED INDEX nella clausola WITH.

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( CLUSTERED INDEX (id) );

Per aggiungere un indice non cluster in una tabella, utilizzare la sintassi seguente:

CREATE INDEX zipCodeIndex ON myTable (zipCode);

Ottimizzazione degli indici columnstore raggruppati

Le tabelle columnstore cluster organizzano i dati in segmenti. Una qualità elevata dei segmenti è fondamentale per ottenere prestazioni ottimali delle query in una tabella columnstore. La qualità dei segmenti si può misurare in base al numero di righe in un gruppo di righe compresso. La qualità del segmento è più ottimale quando ci sono almeno 100 K righe per gruppo di righe compresso e migliora le prestazioni man mano che il numero di righe per gruppo di righe si avvicina a 1.048.576 righe, che è il massimo numero di righe che un gruppo di righe può contenere.

La vista seguente può essere creata e usata nel sistema per calcolare il numero medio di righe per ogni gruppo di righe e per identificare eventuali indici columnstore cluster non ottimali. L'ultima colonna di questa vista viene generata come istruzione SQL che può essere usata per la ricompilazione degli indici.

CREATE VIEW dbo.vColumnstoreDensity
AS
SELECT
        GETDATE()                                                               AS [execution_date]
,       DB_Name()                                                               AS [database_name]
,       s.name                                                                  AS [schema_name]
,       t.name                                                                  AS [table_name]
,       MAX(p.partition_number)                                                 AS [table_partition_count]
,       SUM(rg.[total_rows])                                                    AS [row_count_total]
,       SUM(rg.[total_rows])/COUNT(DISTINCT rg.[distribution_id])               AS [row_count_per_distribution_MAX]
,       CEILING((SUM(rg.[total_rows])*1.0/COUNT(DISTINCT rg.[distribution_id]))/1048576) AS [rowgroup_per_distribution_MAX]
,       SUM(CASE WHEN rg.[State] = 0 THEN 1                   ELSE 0    END)    AS [INVISIBLE_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE 0    END)    AS [INVISIBLE_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 1 THEN 1                   ELSE 0    END)    AS [OPEN_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE 0    END)    AS [OPEN_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 2 THEN 1                   ELSE 0    END)    AS [CLOSED_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE 0    END)    AS [CLOSED_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 3 THEN 1                   ELSE 0    END)    AS [COMPRESSED_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE 0    END)    AS [COMPRESSED_rowgroup_rows]
,       SUM(CASE WHEN rg.[State] = 3 THEN rg.[deleted_rows]   ELSE 0    END)    AS [COMPRESSED_rowgroup_rows_DELETED]
,       MIN(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_AVG]
,       'ALTER INDEX ALL ON ' + s.name + '.' + t.NAME + ' REBUILD;'             AS [Rebuild_Index_SQL]
FROM    sys.[dm_pdw_nodes_db_column_store_row_group_physical_stats] rg
JOIN    sys.[pdw_nodes_tables] nt                   ON  rg.[object_id]          = nt.[object_id]
                                                    AND rg.[pdw_node_id]        = nt.[pdw_node_id]
                                                    AND rg.[distribution_id]    = nt.[distribution_id]
JOIN    sys.[pdw_permanent_table_mappings] mp                 ON  nt.[name]               = mp.[physical_name]
JOIN    sys.[tables] t                              ON  mp.[object_id]  = t.[object_id]
JOIN    sys.[schemas] s                             ON t.[schema_id]    = s.[schema_id]
JOIN    sys.[partitions] p                          ON P.object_id      = t.object_id
GROUP BY
        s.[name]
,       t.[name];

Dopo aver creato la vista, eseguire questa query per identificare le tabelle con gruppi di righe con meno di 100 K righe. È possibile aumentare la soglia di 100 K se si sta cercando una qualità del segmento più ottimale.

SELECT    *
FROM    [dbo].[vColumnstoreDensity]
WHERE    COMPRESSED_rowgroup_rows_AVG < 100000
        OR INVISIBLE_rowgroup_rows_AVG < 100000;

Dopo avere eseguito la query, è possibile iniziare a esaminare i dati e analizzare i risultati. Questa tabella illustra gli elementi da cercare nell'analisi dei gruppi di righe.

Impatto della manutenzione degli indici

La colonna Rebuild_Index_SQL nella vista vColumnstoreDensity contiene un'istruzione ALTER INDEX REBUILD che può essere usata per ricompilare gli indici. Durante la ricompilazione degli indici, assicurarsi di allocare memoria sufficiente per la sessione di ricompilazione dell'indice. A tale scopo, incrementare la classe di risorse di un utente con autorizzazioni per la ricompilazione dell'indice in questa tabella al livello minimo consigliato. Per un esempio, vedere Ricompilazione degli indici per migliorare la qualità dei segmenti più avanti in questo articolo.

Per una tabella con un indice columnstore clusterizzato ordinato, ALTER INDEX REBUILD riordina i dati usando tempdb. Monitorare tempdb durante le operazioni di ricompilazione. Se è necessario più spazio per tempdb, è possibile aumentare le dimensioni del pool di database. Tornare alle dimensioni precedenti al termine della ricompilazione dell'indice.

Per una tabella con un indice columnstore raggruppato e ordinato, ALTER INDEX REORGANIZE non riordina i dati. Per riordinare i dati, usare ALTER INDEX REBUILD.

Per altre informazioni sugli indici columnstore cluster ordinati, vedere Ottimizzazione delle prestazioni con indici columnstore cluster ordinati.

Possibili cause di una qualità scadente dell'indice columnstore

Se sono state identificate tabelle di qualità scadente, è consigliabile identificarne la causa radice. Di seguito sono riportate altre cause comuni della qualità scadente dei segmenti:

1. Utilizzo elevato di memoria durante la compilazione dell'indice
2. Volume elevato di operazioni DML
3. Operazioni di carico ridotto o a goccia.
4. Troppe partizioni

I fattori seguenti possono far sì che un indice columnstore abbia un numero di righe notevolmente inferiore alla cifra ottimale di 1 milione per ogni gruppo di righe. E possono anche far sì che le righe vengano inserite nel gruppo di righe differenziale anziché nel gruppo di righe compresso.

Utilizzo elevato di memoria durante la compilazione dell'indice

Il numero di righe per ogni gruppo di righe compresso è direttamente correlato alla larghezza della riga e alla quantità di memoria disponibile per l'elaborazione del gruppo di righe. Quando le righe vengono scritte nelle tabelle columnstore in condizioni di utilizzo elevato di memoria, la qualità dei segmenti columnstore potrebbe risentirne. La procedura consigliata consiste quindi nel fare in modo che la sessione che sta scrivendo nelle tabelle di indice columnstore abbia accesso alla maggiore quantità di memoria possibile. Poiché esiste un compromesso tra memoria e concorrenza, le linee guida sull'allocazione di memoria corretta dipendono dai dati in ogni riga della tabella, dalle unità data warehouse allocate al sistema e dal numero di slot di concorrenza che è possibile assegnare alla sessione che scrive i dati nella tabella.

Volume elevato di operazioni DML

Un volume elevato di operazioni DML che aggiornano ed eliminano righe può introdurre inefficienze nel columnstore. Ciò vale soprattutto quando viene modificata la maggior parte delle righe di un gruppo di righe.

• L'eliminazione di una riga da un gruppo di righe compresso si limita a contrassegnare in modo logico la riga come eliminata. La riga rimane nel gruppo di righe compresso finché la partizione o la tabella non viene ricompilata.
• Quando si inserisce una riga, questa viene aggiunta a una tabella rowstore interna detta gruppo di righe delta. La riga inserita non viene convertita in columnstore finché il gruppo di righe delta non è pieno e viene contrassegnato come chiuso. I gruppi di righe vengono chiusi quando raggiungono la capacità massima di 1.048.576 righe.
• L'aggiornamento di una riga nel formato columnstore viene elaborato come un'eliminazione logica e poi come operazione di inserimento. La riga inserita può essere archiviata nel delta store.

Le operazioni di aggiornamento e inserimento in batch che superano la soglia in blocco di 102.400 righe per distribuzione allineata a partizione passano direttamente al formato columnstore. Tuttavia, presupponendo una distribuzione uniforme, perché ciò si verifichi si dovranno modificare più di 6.144.000 righe in una singola operazione. Se il numero di righe per una determinata distribuzione allineata alle partizioni è inferiore a 102.400, le righe vengono inviate al delta store e lì rimangono fino a quando non si sono inserite o modificate un numero sufficiente di righe per chiudere il gruppo di righe o l'indice non viene ricompilato.

Operazioni di caricamento di piccole dimensioni o con un flusso irregolare

I carichi di piccole dimensioni nel pool SQL dedicato sono anche noti come carichi graduali. In genere rappresentano un flusso quasi costante di dati inseriti nel sistema. Tuttavia, poiché questo flusso è quasi continuo, il volume delle righe non è particolarmente grande. Molto spesso i dati sono notevolmente inferiori alla soglia necessaria per un caricamento diretto nel formato columnstore.

In queste situazioni, è spesso preferibile inserire i dati prima nell'archiviazione BLOB di Azure e lasciarli accumulare prima del caricamento. Questa tecnica è spesso chiamata micro-batch.

Troppe partizioni

Un altro fattore da considerare è l'impatto del partizionamento sulle tabelle columnstore cluster. Prima di eseguire il partizionamento, il pool SQL dedicato divide già i dati in 60 database. Il partizionamento, quindi, suddivide ulteriormente i dati. Se si partizionano i dati, tenere presente che per poter sfruttare i vantaggi di un indice columnstore cluster ogni partizione deve contenere almeno 1 milione di righe. Se una tabella è divisa in 100 partizioni, per poter sfruttare i vantaggi di un indice columnstore in cluster, deve contenere almeno 6 miliardi di righe, ovvero 60 distribuzioni 100 partizioni 1 milione di righe. Se la tua tabella con 100 partizioni non contiene 6 miliardi di righe, riduci il numero di partizioni oppure considera l'uso di una tabella heap.

Dopo aver caricato alcuni dati nelle tabelle, seguire questa procedura per identificare e ricompilare le tabelle con indici columnstore cluster non ottimali.

ricompilare gli indici per migliorare la qualità dei segmenti

Passaggio 1: Identificare o creare l'utente per la classe di risorse appropriata

Un metodo rapido per migliorare immediatamente la qualità dei segmenti consiste nella ricompilazione dell'indice. Il codice SQL restituito dalla vista precedente contiene un'istruzione ALTER INDEX REBUILD che può essere usata per ricompilare gli indici. Durante la ricompilazione degli indici, assicurarsi di allocare memoria sufficiente per la sessione di ricompilazione dell'indice. A tale scopo, incrementare la classe di risorse di un utente con autorizzazioni per la ricompilazione dell'indice in questa tabella al livello minimo consigliato.

Di seguito è riportato un esempio di come allocare altra memoria per un utente, aumentando la relativa classe di risorse. Per informazioni sull'uso delle classi di risorse, vedere Classi di risorse per la gestione del carico di lavoro.

EXEC sp_addrolemember 'xlargerc', 'LoadUser';

Passaggio 2: Ricompilare gli indici columnstore raggruppati con una classe di risorse utente superiore

Accedi come l'utente del passaggio 1 (LoadUser), che ora utilizza una classe di risorse superiore, ed esegui i comandi ALTER INDEX. Assicurarsi che l'utente abbia l'autorizzazione ALTER per le tabelle in cui viene ricompilato l'indice. Questi esempi illustrano come ricompilare l'intero indice columnstore o una singola partizione. Nelle tabelle di grandi dimensioni è più pratico ricompilare un'unica partizione alla volta.

In alternativa, invece di ricompilare l'indice è possibile copiare la tabella in una nuova tabella con CTAS. Qual è il modo migliore? Per grandi volumi di dati CTAS è in genere più veloce di ALTER INDEX. Per volumi di dati più piccoli, ALTER INDEX è più facile da usare e non richiede la sostituzione della tabella.

-- Rebuild the entire clustered index
ALTER INDEX ALL ON [dbo].[DimProduct] REBUILD;

-- Rebuild a single partition
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5;

-- Rebuild a single partition with archival compression
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5 WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

-- Rebuild a single partition with columnstore compression
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5 WITH (DATA_COMPRESSION = COLUMNSTORE);

La ricompilazione di un indice nel pool SQL dedicato è un'operazione offline. Per altre informazioni sulla ricompilazione di indici, vedere la sezione ALTER INDEX REBUILD in Deframmentazione degli indici columnstore e ALTER INDEX.

Passaggio 3: Verificare che la qualità dei segmenti di columnstore clusterizzato sia migliorata.

Eseguire nuovamente la query che ha identificato la tabella con una qualità scadente dei segmenti e verificare che la qualità sia migliorata. Se la qualità del segmento non è migliorata, è possibile che le righe della tabella siano eccessivamente larghe. È consigliabile usare una classe di risorse superiore o una DWU durante la ricompilazione degli indici.

Ricompilare gli indici con CTAS e la commutazione delle partizioni

In questo esempio viene usata l'istruzione CREATE TABLE AS SELECT (CTAS) e il cambio di partizione per ricompilare una partizione di tabella.

-- Step 1: Select the partition of data and write it out to a new table using CTAS
CREATE TABLE [dbo].[FactInternetSales_20000101_20010101]
    WITH    (   DISTRIBUTION = HASH([ProductKey])
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101,20010101
                                )
                            )
            )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
WHERE   [OrderDateKey] >= 20000101
AND     [OrderDateKey] <  20010101
;

-- Step 2: Switch IN the rebuilt data with TRUNCATE_TARGET option
ALTER TABLE [dbo].[FactInternetSales_20000101_20010101] SWITCH PARTITION 2 TO  [dbo].[FactInternetSales] PARTITION 2 WITH (TRUNCATE_TARGET = ON);

Per altre informazioni sulla ricreazione delle partizioni tramite CTAS, vedere Uso di partizioni nel pool SQL dedicato.

Contenuto correlato

Per altre informazioni sullo sviluppo di tabelle, vedere Developing tables (Sviluppo di tabelle).