Indici columnstore - Linee guida per il caricamento di datiColumnstore indexes - data loading guidance

QUESTO ARGOMENTO SI APPLICA A:sìSQL Server (a partire dalla versione 2012)sìDatabase SQL di AzuresìAzure SQL Data Warehouse sìParallel Data Warehouse THIS TOPIC APPLIES TO:yesSQL Server (starting with 2012)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Opzioni e suggerimenti per il caricamento di dati in un indice columnstore usando i metodi standard di caricamento bulk e con inserimento singolo di SQL.Options and recommendations for loading data into a columnstore index by using the standard SQL bulk loading and trickle insert methods. 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.Loading data into a columnstore index is an essential part of any data warehousing process because it moves data into the index in preparation for analytics.

Se non si ha esperienza con gli indici columnstore,New to columnstore indexes? Vedere Indici columnstore - Panoramica e Indici columnstore - Architettura.See Columnstore indexes - overview and Columnstore indexes - architecture.

Che cos'è il caricamento bulk?What is bulk loading?

Il termine caricamento bulk fa riferimento al modo in cui viene aggiunto un numero elevato di righe a un archivio dati.Bulk loading refers to the way large numbers of rows are added to a data store. Questo è il modo che offre le prestazioni migliori per spostare i dati in un indice columnstore, perché si basa su batch di righe.It is the most performant way to move data into a columnstore index because it operates on batches of rows. Il caricamento bulk riempie i rowgroup fino alla capacità massima e li comprime direttamente nel columnstore.Bulk loading fills rowgroups to maximum capacity and compresses them directly into the columnstore. Solo le righe alla fine di un carico che non soddisfano il requisito minimo di 102.400 righe per ogni rowgroup passano all'archivio differenziale.Only rows at the end of a load that don't meet the minimum of 102,400 rows per rowgroup go to the deltastore.
Per eseguire un caricamento bulk, è possibile usare l'utilità bcp, Integration Services oppure selezionare righe da una tabella di gestione temporanea.s To perform a bulk load, you can use bcp Utility, Integration Services, or select rows from a staging table.

Caricamento in un indice columnstore clusterLoading into a clustered columnstore index

Come illustrato nel diagramma, un caricamento bulk:As the diagram suggests, a bulk load::

  • Non esegue il preordinamento dei dati.Does not pre-sort the data. I dati vengono inseriti nei rowgroup secondo l'ordine di ricezione.Data is inserted into rowgroups in the order it is received.
  • Se le dimensioni del batch sono > = 102.400, le righe vengono caricate direttamente nei rowgroup compressi.If the batch size is >= 102400, the rows are directly into the compressed rowgroups. Affinché l'importazione bulk sia efficiente è consigliabile scegliere una dimensione di batch > = 102.400, poiché è possibile evitare lo spostamento di righe di dati in un rowgroup delta prima che le righe siano spostate nei rowgroup compressi da un thread in background, dal motore di tuple.It is recommended that you choose a batch size >=102400 for efficient bulk import because you can avoid moving data rows to a delta rowgroups before the rows are eventually moved to compressed rowgroups by a background thread, 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.If the batch size < 102400 or if the remaining rows are < 102400, the rows are loaded into delta rowgroups.

![Note] In una tabella rowstore con dati di un indice columnstore non cluster, SQL ServerSQL Server inserisce sempre i dati nella tabella di base.![Note] On a rowstore table with a nonclustered columnstore index data, SQL ServerSQL Server always inserts data into the base table. I dati non vengono mai inseriti direttamente nell'indice columnstore.The data is never inserted directly into the columnstore index.

Il caricamento bulk include le ottimizzazioni seguenti per le prestazioni:Bulk loading has these built-in performance optimizations:

  • Caricamenti paralleli.Parallel loads. È possibile eseguire più caricamenti bulk in simultanea (bcp o bulk insert) con il caricamento di un file di dati separato per ognuno.You can have multiple concurrent bulk loads (bcp or bulk insert) that are each loading a separate data file. Diversamente dai caricamenti bulk di rowstore in SQL Server, non è necessario specificare TABLOCK, poiché ogni thread di importazione bulk caricherà i dati esclusivamente in un rowgroup distinto (compresso o delta) con un blocco esclusivo.Unlike rowstore bulk loads into SQL Server, you don't need to specify TABLOCK because each bulk import thread will load data exclusively into a separate rowgroups (compressed or delta rowgroups) with exclusive lock on it. Specificando TABLOCK verrà forzato il blocco esclusivo sulla tabella e non sarà possibile importare dati in parallelo.Using TABLOCK will force an exclusive lock on the table and you will not be able to import data in parallel.

  • Registrazione minima.Minimal logging. Un caricamento bulk usa la registrazione minima per i dati destinati direttamente a rowgroup compressi.A bulk load uses minimal logging on data that goes directly to compressed rowgroups. Per tutti i dati destinati a un rowgroup differenziale viene usata la registrazione completa.Any data that goes to a delta rowgroup is fully logged. Ciò include qualsiasi dimensione di batch minore di 102.400 righe.This includes any batch sizes that are less than 102,400 rows. L'obiettivo del caricamento bulk, tuttavia, è evitare i rowgroup differenziali per la maggior parte dei dati.However, with bulk loading the goal is for most of the data to bypass delta rowgroups.

  • Ottimizzazione del blocco.Locking Optimization. Durante il caricamento in rowgroup compressi, viene acquisito il blocco X sul rowgroup.When loading into compressed rowgroup, the X lock on rowgroup is acquired. Tuttavia, durante il caricamento bulk in rowgroup delta, viene acquisito il blocco X del rowgroup, ma SQL Server continua a bloccare i blocchi di PAGE/EXTENT perché il blocco X del rowgroup non rientra nella gerarchia di blocco.However, when bulk loading into delta rowgroup, an X lock is acquired at rowgroup but SQL Server still locks the locks PAGE/EXTENT because X rowgroup lock is not part of locking hierarchy.

In presenza di un indice albero B non cluster su un indice columnstore, non sono previsti il blocco o l'ottimizzazione della registrazione per l'indice, ma le ottimizzazioni sull'indice columnstore cluster descritte in precedenza sono ancora presenti.If you have a nonclustered btree index on a columnstore index, there is no locking or logging optimization for the index itself but the optimizations on clustered columnstore index as described above are still there.

Pianificare le dimensioni di caricamento bulk per ridurre al minimo i rowgroup differenzialiPlan bulk load sizes to minimize delta rowgroups

Gli indici columnstore offrono prestazioni ottimali quando la maggior parte delle righe viene compressa in columnstore e non inserita nei rowgroup differenziali.Columnstore indexes perform best when most of the rows are compressed into the columnstore and not sitting in delta rowgroups. È consigliabile ridimensionare i carichi in modo che le righe vengano spostate direttamente nel columnstore ignorando il più possibile l'archivio differenziale.It's best to size your loads so that rows go directly to the columnstore and bypass the deltastore as much as possible.

Gli scenari seguenti indicano quando le righe caricate vengono direttamente indirizzate al columnstore o passano per il deltastore.These scenarios describe when loaded rows go directly to the columnstore or when they go to the deltastore. Nell'esempio, ogni rowgroup può avere 102.400-1.048.576 righe per rowgroup.In the example, each rowgroup can have 102,400-1,048,576 rows per rowgroup. In pratica, la dimensione massima di un rowgroup può essere inferiore a 1.048.576 righe quando sono presenti richieste di memoria.In practice, the maximum size of a rowgroup can be smaller than 1,048,576 rows when there is memory pressure.

Righe per il caricamento bulkRows to Bulk Load Righe aggiunte al rowgroup compressoRows Added to the Compressed Rowgrouo Righe aggiunte al rowgroup deltaRows Added to the Delta Rowgroup
102.000102,000 00 102.000102,000
145.000145,000 145.000145,000

Dimensioni rowgroup: 145.000.Rowgroup size: 145,000
00
1.048.5771,048,577 1,048,5761,048,576

Dimensioni rowgroup: 1.048.576.Rowgroup size: 1,048,576.
11
2.252.1522,252,152 2.252.1522,252,152

Dimensioni rowgroup: 1.048.576, 1.048.576, 155.000.Rowgroup sizes: 1,048,576, 1,048,576, 155,000.
00

Nell'esempio seguente vengono illustrati i risultati del caricamento di 1.048.577 righe in una tabella.The following example shows the results of loading 1,048,577 rows into a table. I risultati mostrano che esiste un rowgroup COMPRESSED nel columnstore (come segmenti di colonna compressi) e 1 riga nel deltastore.The results show that one COMPRESSED rowgroup in the columnstore (as compressed column segments), and 1 row in the 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  

Rowgroup e archivio differenziale per un carico batchRowgroup and deltastore for a batch load

Usare una tabella di gestione temporanea per migliorare le prestazioniUse a staging table to improve performance

Se si caricano dati solo per la gestione temporanea, prima di eseguire ulteriori trasformazioni, il caricamento della tabella nella tabella heap sarà molto più rapido del caricamento dei dati in una tabella columnstore cluster.If you are loading data only to stage it before running more transformations, loading the table to heap table will be much faster than loading the data to a clustered columnstore table. Inoltre, il caricamento dei dati in una [tabella temporanea][Temporanea] avverrà molto più velocemente del caricamento di una tabella in un archivio permanente.In addition, loading data to a [temporary table][Temporary] will also load much faster than loading a table to permanent storage.

Il modello comune per il caricamento di dati consiste nel caricare i dati in una tabella di gestione temporanea, eseguire alcune trasformazioni e quindi caricare i dati nella tabella di destinazione usando il comando seguenteA common pattern for data load is to load the data into a staging table, do some transformation and then load it into the target table using the following command

INSERT INTO <columnstore index>  SELECT <list of columns> 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.This command loads the data into the columnstore index in similar ways to BCP or Bulk Insert but in a single batch. Se il numero di righe nella tabella di gestione temporanea è < 102.400, le righe vengono caricate in un rowgroup delta, altrimenti possono essere caricate direttamente in un rowgroup compresso.If the number of rows in the staging table < 102400, the rows are loaded into a delta rowgroup otherwise the rows are directly loaded into compressed rowgroup. Uno dei limiti principali è dato dal fatto che l'operazione INSERT è a thread singolo.One key limitation was that this INSERT operation was single threaded. Per caricare i dati in parallelo, è possibile creare più tabelle di gestione temporanea o immettere i comandi INSERT/SELECT con intervalli non sovrapposti di righe dalla tabella di gestione temporanea.To load data in parallel, you could create multiple staging table or issue INSERT/SELECT with non-overlapping ranges of rows from the staging table. Questa limitazione è stata risolta in SQL Server 2016.This limitation goes away with SQL Server 2016. Il comando seguente carica i dati dalla tabella di gestione temporanea in parallelo, ma è necessario specificare il comando TABLOCK.The command below loads the data from staging table in parallel but you will need to specify TABLOCK

INSERT INTO <columnstore index>  WITH (TABLOCK)  SELECT <list of columns> FROM <Staging Table>  

Per il caricamento in indici columnstore cluster da una tabella di gestione temporanea sono disponibili le ottimizzazioni seguenti.There are following optimizations available when loading into clustered columnstore index from staging table

  • Ottimizzazione dei log: con registrazione minima quando i dati vengono caricati in rowgroup compressi.Log Optimization: Minimally logged both when the data is loaded into compressed rowgroup. Non si ha registrazione minima quando i dati vengono caricati in rowgroup delta.No minimal logging when data gets loaded into delta rowgroup.

  • Ottimizzazione del blocco: durante il caricamento in rowgroup compressi, viene acquisito il blocco X del rowgroup.Locking Optimization: When loading into compressed rowgroup, the X lock on rowgroup is acquired. Tuttavia, quando si opera con rowgroup delta viene acquisito il blocco X del rowgroup, ma SQL Server continua a bloccare i blocchi di PAGE/EXTENT perché il blocco X del rowgroup non rientra nella gerarchia di blocco.However, with delta rowgroup, an X lock is acquired at rowgroup but SQL Server still locks the locks PAGE/EXTENT because X rowgroup lock is not part of locking hierarchy.

    In caso di più indici non cluster, non si ha blocco o ottimizzazione della registrazione dell'indice, ma le ottimizzazioni sull'indice columnstore cluster descritte in precedenza sono ancora presenti.If you have or more nonclustered indexes, there is no locking or logging optimization for the index itself but the optimizations on clustered columnstore index as described above are still there

Che cos'è il caricamento con inserimento singolo?What is trickle insert?

L'inserimento singolo indica il modo in cui le singole righe vengono spostate nell'indice columnstore.Trickle insert refers to the way individual rows move into the columnstore index. Per gli inserimenti singoli viene usata l'istruzione INSERT INTOTrickle inserts use the INSERT INTO statement. e tutte le righe vengono spostate nell'archivio differenziale.With trickle insert, all of the rows go to the deltastore. Ciò è utile per un numero limitato di righe, ma non è pratico per caricamenti di grandi dimensioni.This is useful for small numbers of rows, but not practical for large loads.

INSERT INTO <table-name> VALUES (<set of values>)  

Si noti che i thread simultanei che usano INSERT INTO per inserire valori in un indice columnstore cluster possono inserire righe nello stesso rowgroup deltastore.Note, concurrent threads using INSERT INTO to insert values into a clustered columnstore index can insert rows into the same deltastore rowgroup.

Quando il rowgroup arriva a contenere 1.048.576 righe viene contrassegnato come chiuso, ma è ancora disponibile per query e operazioni di aggiornamento/eliminazione. Le righe appena inserite verranno indirizzate in un rowgroup deltastore già esistente o nuovo.Once the rowgroup contains 1,048,576 rows, the delta rowgroup us marked closed but it is still available for queries and update/delete operations but the newly inserted rows go into an existing or newly created deltastore rowgroup. In background agisce il motore di tuple , che comprime i rowgroup delta chiusi ogni 5 minuti circa.There is a background thread Tuple Mover (TM) that compresses the closed delta rowgroups periodically every 5 minutes or so. È possibile richiamare in modo esplicito il comando seguente per comprimere il rowgroup delta chiuso:You can explicitly invoke the following command to compress the closed delta rowgroup

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

Per forzare un rowgroup delta chiuso e compresso, eseguire il comando seguente.If you want force a delta rowgroup closed and compressed, you can execute the following command. È consigliabile eseguire questo comando al termine del caricamento delle righe, quando non si prevede l'aggiunta di nuove righe.You may want run this command if you are done loading the rows and don't expect any new rows. La chiusura e la compressione esplicita del rowgroup delta consente di risparmiare spazio di archiviazione e incrementare le prestazioni delle query di analisi.By explicitly closing and compressing the delta rowgroup, you can save storage further and improve the analytics query performance. È consigliabile richiamare questo comando quando non si prevede l'inserimento di nuove righe.A best practice is to invoke this command if you don't expect new rows to be inserted.

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

Come funziona il caricamento in una tabella partizionataHow loading into a partitioned table works

Per i dati partizionati, SQL ServerSQL Server assegna prima ogni riga a una partizione, quindi esegue operazioni columnstore sui dati nella partizione.For partitioned data, SQL ServerSQL Server first assigns each row to a partition, and then performs columnstore operations on the data within the partition. Ogni partizione ha i propri rowgroup e almeno un rowgroup differenziale.Each partition has its own rowgroups and at least one delta rowgroup.

Passaggi successiviNext steps

Per altre informazioni sul caricamento, vedere questo post di blog.For further discussion on loading, see this blog post.