Indici columnstore - Linee guida per il caricamento dei dati

Si applica a:SQL ServerAzure SQL DatabaseIstanza gestita di Azure SQLAzure Synapse AnalyticsAnalytics Platform System (PDW)Database SQL in Microsoft Fabric

Opzioni e suggerimenti per il caricamento di dati in un columnstore index utilizzando i metodi standard di caricamento bulk e inserimento graduale di SQL. Il caricamento di dati in un indice columnstore è una parte essenziale di qualsiasi processo di data warehousing perché i dati vengono spostati nell'indice in preparazione per l'analisi.

Sei nuovo/a agli indici columnstore? Vedere Indici columnstore - Panoramica e Architettura degli indici columnstore.

Che cos'è il caricamento di massa?

Il termine caricamento massivo fa riferimento al modo in cui viene aggiunto un gran numero di righe a un archivio dati. Questo è il modo che offre le prestazioni migliori per spostare i dati in un indice columnstore, perché si basa su batch di righe. Il caricamento massivo riempie i gruppi di righe fino alla capacità massima e li comprime direttamente nel magazzino di colonne. Solo le righe alla fine del processo di caricamento che non soddisfano il requisito minimo di 102.400 righe per rowgroup passano all'archivio differenziale.

Per eseguire un caricamento bulk è possibile usare l'utilità bcp, Integration Services oppure selezionare righe da una tabella di staging.

Come illustrato nel diagramma, il caricamento in blocco:

• Non pre-ordina i dati. I dati vengono inseriti nei rowgroup secondo l'ordine di ricezione.
• Se le dimensioni del batch sono >= 102.400, le righe vengono caricate direttamente nei gruppi di dati compressi. È consigliabile scegliere una dimensione del batch >= 102400 per un'importazione bulk efficiente, perché è possibile evitare di spostare righe di dati nei delta rowgroup prima che le righe vengano spostate nei rowgroup compressi da un thread in background, un tuple mover (TM).
• Se le dimensioni del batch sono < 102.400 o se le righe rimanenti sono < 102.400, le righe vengono caricate in rowgroup delta.

Nota

In una tabella di archivio a righe con dati di un indice columnstore non clusterizzato, SQL Server inserisce sempre i dati nella tabella di base. I dati non vengono mai inseriti direttamente nell'indice columnstore.

Il caricamento in blocco ha queste ottimizzazioni integrate per le prestazioni:

• Caricamenti in parallelo: è possibile eseguire in simultanea più caricamenti bulk (bcp o bulk insert), ognuno dei quali carica un file di dati separato. Diversamente dai caricamenti bulk di rowstore in SQL Server, non è necessario specificare TABLOCK, poiché ogni thread di importazione in blocco caricherà i dati esclusivamente in un rowgroup distinto (compresso o delta) con un blocco esclusivo.

• Registrazione ridotta: i dati caricati direttamente in gruppi di righe compressi portano a una riduzione significativa delle dimensioni del log. Ad esempio, se i dati sono stati compressi di 10 volte, il log delle transazioni corrispondente è di circa 10 volte inferiore senza richiedere TABLOCK o il modello di recupero con registrazione minima o modello di recupero semplice. Per qualsiasi dato destinato a un delta rowgroup, vengono completamente registrati. Ciò include qualsiasi dimensione di batch minore di 102.400 righe. La procedura consigliata corrisponde all'uso di batchsize >= 102.400. Poiché non TABLOCK è necessario, è possibile caricare i dati in parallelo.

• Registrazione minima: è possibile ottenere ulteriori riduzioni nella registrazione se si soddisfano i prerequisiti per la registrazione minima. Tuttavia, a differenza del caricamento dei dati in un rowstore, TABLOCK comporta un X blocco (esclusivo) sulla tabella invece di un BU blocco (aggiornamento in blocco) e quindi non è possibile eseguire il caricamento dati in parallelo. Per ulteriori informazioni sul blocco, vedere Blocco e versionamento delle righe.

• Ottimizzazione del blocco: Il blocco su un gruppo di righe viene acquisito automaticamente durante il X caricamento dei dati in un gruppo di righe compresso. Tuttavia, quando si esegue il caricamento bulk in un rowgroup differenziale, viene acquisito un X blocco per il rowgroup, ma il Motore di Database acquisisce ancora blocchi di pagina ed estensione perché il X blocco rowgroup non fa parte della gerarchia di blocchi.

In presenza di un indice B-tree non cluster su un indice columnstore, non c'è alcun blocco né ottimizzazione del logging per l'indice stesso, ma le ottimizzazioni sull'indice columnstore cluster descritte in precedenza sono applicabili.

Pianificare le dimensioni dei caricamenti di grandi dimensioni per ridurre al minimo i gruppi di righe differenziali

Gli indici columnstore offrono prestazioni ottimali quando la maggior parte delle righe viene compressa nel columnstore e non si trova nei delta rowgroups. È consigliabile dimensionare i carichi in modo che le righe vengano spostate direttamente nel columnstore bypassando il più possibile il deltastore.

Gli scenari seguenti indicano quando le righe caricate vengono direttamente indirizzate al columnstore o passano per il deltastore. Nell'esempio, ogni rowgroup può contenere da 102,400 a 1,048,576 righe per rowgroup. In pratica, la dimensione massima di un rowgroup può essere inferiore a 1.048.576 righe quando è presente un utilizzo elevato di memoria.

Righe per il caricamento massivo	Righe aggiunte al gruppo di righe compresso	Righe aggiunte al gruppo di righe delta
102.000	0	102.000
145.000	145.000 Dimensioni del gruppo di righe: 145.000	0
1.048.577	1.048.576 Dimensioni rowgroup: 1.048.576.	1
2.252.152	2.252.152 Dimensioni gruppo di righe: 1.048.576, 1.048.576, 155.000.	0

Nell'esempio seguente vengono illustrati i risultati del caricamento di 1.048.577 righe in una tabella. I risultati mostrano che esiste un rowgroup COMPRESSED nel columnstore (come segmenti di colonna compressi) e 1 riga nel deltastore.

SELECT object_id, index_id, partition_number, row_group_id, delta_store_hobt_id,
    state, state_desc, total_rows, deleted_rows, size_in_bytes
FROM sys.dm_db_column_store_row_group_physical_stats;

Usare una tabella di staging per migliorare le prestazioni

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. Inoltre, il caricamento dei dati in una [tabella temporanea][Temporanea] avverrà molto più velocemente del caricamento di una tabella in un archivio permanente.

Il criterio comune per il caricamento di dati consiste nel caricare i dati in una tabella di staging, eseguire alcune trasformazioni e quindi caricarli nella tabella di destinazione usando il comando seguente

INSERT INTO [<columnstore index>]
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]

Il comando carica i dati nell'indice columnstore in modo analogo ai comandi bcp o bulk insert, ma in un unico batch. Se il numero di righe nella tabella di staging è < 102400, le righe vengono caricate in un rowgroup delta, altrimenti vengono caricate direttamente in un rowgroup compresso. Uno dei limiti principali era il fatto che l'operazione INSERT è a thread singolo. Per caricare i dati in parallelo era possibile creare più tabelle di staging o eseguire i comandi INSERT/SELECT con intervalli non sovrapposti di righe dalla tabella di staging. Questa limitazione è stata risolta in SQL Server 2016 (13.x). Il comando seguente carica i dati dalla tabella di staging in parallelo, ma è necessario specificare TABLOCK. Questo comportamento può sembrare in contraddizione con quanto affermato in precedenza con bulkload, ma la differenza principale è che il caricamento parallelo dei dati dalla tabella di staging viene eseguito nella stessa transazione.

INSERT INTO [<columnstore index>] WITH (TABLOCK)
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]

Per il caricamento in un indice cluster columnstore dalla tabella di staging sono disponibili le seguenti ottimizzazioni:

• Ottimizzazione dei log: Riduzione della registrazione dei log nel momento in cui i dati vengono caricati in un gruppo di righe compresso.
• Ottimizzazione del blocco: Durante il caricamento dei dati in un rowgroup compresso, viene bloccato il X rowgroup. Tuttavia, quando si esegue il caricamento bulk in un delta rowgroup, viene acquisito un X blocco per il rowgroup, ma il Motore di Database acquisisce ancora blocchi di pagina ed estensione perché il X blocco rowgroup non fa parte della gerarchia di blocchi.

In presenza di uno o più indici non clusterizzati, non vi è alcun blocco né ottimizzazione della registrazione per l'indice stesso, ma le ottimizzazioni sull'indice columnstore clusterizzato descritte in precedenza sono ancora presenti.

Che cos'è l'inserimento graduale?

L'inserimento graduale indica il modo in cui le singole righe vengono aggiunte nell'indice columnstore. Gli inserimenti incrementali usano l'istruzione INSERT INTO. Con l'inserimento graduale, tutte le righe vanno al deltastore. Ciò è utile per un numero limitato di righe, ma non è pratico per caricamenti di grandi dimensioni.

INSERT INTO [<table-name>] VALUES ('some value' /*replace with actual set of values*/)

Nota

I thread simultanei che usano INSERT INTO per inserire valori in un indice columnstore cluster possono inserire righe nello stesso rowgroup deltastore.

Quando il rowgroup arriva a contenere 1.048.576 righe, viene contrassegnato come chiuso, ma è ancora disponibile per query e operazioni di aggiornamento/eliminazione. Tuttavia, le righe appena inserite vengono indirizzate a un rowgroup deltastore, sia esso nuovo o esistente. Esiste un thread in background denominato tuple mover (TM) che comprime periodicamente i rowgroup differenziali chiusi ogni 5 minuti, più o meno. È possibile richiamare in modo esplicito il comando seguente per comprimere il rowgroup delta chiuso:

ALTER INDEX [<index-name>] on [<table-name>] REORGANIZE

Per forzare un rowgroup delta chiuso e compresso, eseguire il comando seguente. È consigliabile eseguire questo comando al termine del caricamento delle righe, quando non si prevede l'aggiunta di nuove righe. La chiusura e la compressione esplicita del rowgroup delta consente di risparmiare spazio di archiviazione e incrementare le prestazioni delle query di analisi. È consigliabile richiamare questo comando quando non si prevede l'inserimento di nuove righe.

ALTER INDEX [<index-name>] on [<table-name>] REORGANIZE with (COMPRESS_ALL_ROW_GROUPS = ON)

Come funziona il caricamento in una tabella partizionata

Per i dati partizionati, il motore di database assegna prima ogni riga a una partizione e quindi esegue operazioni columnstore sui dati all'interno della partizione. Ogni partizione ha i propri gruppi di righe e almeno un gruppo di righe delta.

Contenuto correlato

• Valutazioni delle prestazioni nel caricamento dati utilizzando indici columnstore clusterizzati