Indicizzazione di tabelle in SQL Data WarehouseIndexing tables in SQL Data Warehouse

Raccomandazioni ed esempi per l'indicizzazione di tabelle in Azure SQL Data Warehouse.Recommendations and examples for indexing tables in Azure SQL Data Warehouse.

Quali sono le opzioni di indicizzazione?What are index choices?

SQL Data Warehouse offre diverse opzioni di indicizzazione, inclusi indici columnstore cluster, indici cluster e indici non cluster e un'opzione non di indice nota anche come heap.SQL Data Warehouse offers several indexing options including clustered columnstore indexes, clustered indexes and nonclustered indexes, and a non-index option also known as heap.

Per creare una tabella con un indice, vedere la documentazione su CREATE TABLE (Azure SQL Data Warehouse).To create a table with an index, see the CREATE TABLE (Azure SQL Data Warehouse) documentation.

Indici columnstore clusterClustered columnstore indexes

Per impostazione predefinita, SQL Data Warehouse crea un indice columnstore cluster quando non vengono specificate opzioni di indice in una tabella.By default, SQL Data Warehouse creates a clustered columnstore index when no index options are specified on a table. Le tabelle columnstore cluster offrono sia il livello massimo di compressione dei dati che le migliori prestazioni query generali.Clustered columnstore tables offer both the highest level of data compression as well as the best overall query performance. 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.Clustered columnstore tables will generally outperform clustered index or heap tables and are usually the best choice for large tables. Per questi motivi, l'indice columnstore cluster è il modo migliore per iniziare quando non si è certi di come indicizzare una tabella.For these reasons, clustered columnstore is the best place to start when you are unsure of how to index your table.

Per creare una tabella columnstore cluster è sufficiente specificare CLUSTERED COLUMNSTORE INDEX nella clausola WITH o lasciare la clausola WITH disabilitata:To create a clustered columnstore table, simply specify CLUSTERED COLUMNSTORE INDEX in the WITH clause, or leave the WITH clause off:

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

In alcuni scenari l'indice columnstore cluster potrebbe non essere la scelta ideale:There are a few scenarios where clustered columnstore may not be a good option:

  • Le tabelle columnstore non supportano varchar(max), nvarchar(max) e varbinary(max).Columnstore tables do not support varchar(max), nvarchar(max) and varbinary(max). È consigliabile usare tabelle heap o di indici cluster.Consider heap or clustered index instead.
  • Le tabelle columnstore potrebbero risultare meno efficiente per i dati temporanei.Columnstore tables may be less efficient for transient data. È consigliabile usare tabelle heap oppure tabelle temporanee.Consider heap and perhaps even temporary tables.
  • Tabelle di piccole dimensioni con meno di 100 milioni di righe.Small tables with less than 100 million rows. È consigliabile usare tabelle heap.Consider heap tables.

Tabelle heapHeap tables

Quando si inseriscono temporaneamente i dati in SQL Data Warehouse, una tabella heap rende più veloce il processo complessivo.When you are temporarily landing data on SQL Data Warehouse, you may find that using a heap table makes the overall process faster. Questo perché il caricamento negli heap è più veloce rispetto alle tabelle degli indici e in alcuni casi è possibile eseguire la lettura successiva dalla cache.This is because loads to heaps are faster than to index tables and in some cases the subsequent read can be done from 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.If you are loading data only to stage it before running more transformations, loading the table to heap table is much faster than loading the data to a clustered columnstore table. Anche il caricamento dei dati in una tabella temporanea risulta più veloce del caricamento di una tabella in un archivio permanente.In addition, loading data to a temporary table loads faster than loading a table to permanent storage.

Per le tabelle di ricerca di piccole dimensioni, inferiori a 100 milioni di righe, è spesso consigliabile scegliere le tabelle heap.For small lookup tables, less than 100 million rows, often heap tables make sense. Le tabelle columnstore cluster iniziano a raggiungere la compressione ottimale oltre i 100 milioni di righe.Cluster columnstore tables begin to achieve optimal compression once there is more than 100 million rows.

Per creare una tabella heap è sufficiente specificare HEAP nella clausola WITH:To create a heap table, simply specify HEAP in the WITH clause:

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

Indici cluster e non clusterClustered and nonclustered indexes

Gli indici cluster possono offrire prestazioni migliori rispetto alle tabelle columnstore cluster quando è necessario recuperare rapidamente una singola riga.Clustered indexes may outperform clustered columnstore tables when a single row needs to be quickly retrieved. 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.For queries where a single or very few row lookup is required to performance with extreme speed, consider a cluster index or nonclustered secondary index. Gli indici cluster hanno lo svantaggio di risultare utili solo per le query con un filtro molto selettivo nella colonna dell'indice cluster.The disadvantage to using a clustered index is that only queries that benefit are the ones that use a highly selective filter on the clustered index column. Per migliorare i filtri nelle altre colonne è possibile aggiungere un indice non cluster a tali colonne.To improve filter on other columns a nonclustered index can be added to other columns. Tuttavia, ogni indice che viene aggiunto a una tabella richiede spazio e tempo di elaborazione aggiuntivi.However, each index which is added to a table adds both space and processing time to loads.

Per creare una tabella di indici cluster è sufficiente specificare CLUSTERED INDEX nella clausola WITH:To create a clustered index table, simply specify CLUSTERED INDEX in the WITH clause:

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

Per aggiungere un indice non cluster a una tabella, è sufficiente utilizzare la sintassi seguente:To add a non-clustered index on a table, simply use the following syntax:

CREATE INDEX zipCodeIndex ON myTable (zipCode);

Ottimizzazione degli indici columnstore clusterOptimizing clustered columnstore indexes

I dati delle tabelle columnstore cluster sono organizzati in segmenti.Clustered columnstore tables are organized in data into segments. Una qualità elevata dei segmenti è fondamentale per ottenere prestazioni ottimali delle query in una tabella columnstore.Having high segment quality is critical to achieving optimal query performance on a columnstore table. La qualità dei segmenti si può misurare in base al numero di righe in un gruppo di righe compresso.Segment quality can be measured by the number of rows in a compressed row group. Per una qualità ottimale dei segmenti devono essere presenti almeno 100.000 righe per ogni gruppo di righe compresso. Le prestazioni migliorano quanto più il numero di righe si avvicina a 1.048.576, ovvero il numero massimo di righe che un gruppo di righe può contenere.Segment quality is most optimal where there are at least 100K rows per compressed row group and gain in performance as the number of rows per row group approach 1,048,576 rows, which is the most rows a row group can contain.

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.The below view can be created and used on your system to compute the average rows per row group and identify any sub-optimal cluster columnstore indexes. L'ultima colonna di questa vista viene generata come istruzione SQL che può essere usata per la ricompilazione degli indici.The last column on this view generates a SQL statement which can be used to rebuild your indexes.

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]
,    COUNT(DISTINCT rg.[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.[pdw_nodes_column_store_row_groups] 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_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]
GROUP BY
        s.[name]
,       t.[name]
;

Dopo aver creato la vista, eseguire la query per identificare le tabelle con gruppi di righe con meno di 100.000 righe.Now that you have created the view, run this query to identify tables with row groups with less than 100K rows. Per una qualità ancora superiore dei segmenti, è consigliabile aumentare la soglia oltre le 100.000 righe.Of course, you may want to increase the threshold of 100K if you are looking for more optimal segment quality.

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.Once you have run the query you can begin to look at the data and analyze your results. Questa tabella illustra gli elementi da cercare nell'analisi dei gruppi di righe.This table explains what to look for in your row group analysis.

ColonnaColumn Come usare questi datiHow to use this data
[table_partition_count][table_partition_count] Se la tabella è partizionata, è prevedibile che sia presente un numero maggiore di gruppi di righe aperti.If the table is partitioned, then you may expect to see higher Open row group counts. Teoricamente, a ogni partizione nella distribuzione può essere associato un gruppo di righe aperto.Each partition in the distribution could in theory have an open row group associated with it. Includere questo fattore nell'analisi.Factor this into your analysis. Una tabella di piccole dimensioni partizionata può essere ottimizzata rimuovendo completamente il partizionamento e migliorando così la compressione.A small table that has been partitioned could be optimized by removing the partitioning altogether as this would improve compression.
[row_count_total][row_count_total] Numero totale di righe per la tabella.Total row count for the table. Ad esempio, è possibile usare questo valore per calcolare la percentuale di righe nello stato compresso.For example, you can use this value to calculate percentage of rows in the compressed state.
[row_count_per_distribution_MAX][row_count_per_distribution_MAX] Se tutte le righe sono distribuite uniformemente, questo valore sarà il numero di righe per ogni distribuzione.If all rows are evenly distributed this value would be the target number of rows per distribution. Confrontare questo valore con compressed_rowgroup_count.Compare this value with the compressed_rowgroup_count.
[COMPRESSED_rowgroup_rows][COMPRESSED_rowgroup_rows] Numero totale di righe nel formato columnstore per la tabella.Total number of rows in columnstore format for the table.
[COMPRESSED_rowgroup_rows_AVG][COMPRESSED_rowgroup_rows_AVG] Se il numero medio di righe è notevolmente inferiore al numero massimo di righe per un gruppo di righe, è consigliabile usare le istruzioni CTAS o ALTER INDEX REBUILD per comprimere nuovamente i dati.If the average number of rows is significantly less than the maximum # of rows for a row group, then consider using CTAS or ALTER INDEX REBUILD to recompress the data
[COMPRESSED_rowgroup_count][COMPRESSED_rowgroup_count] Numero di gruppi di righe nel formato columnstore.Number of row groups in columnstore format. Se questo numero è molto elevato in relazione la tabella, è un indicatore che la densità del columnstore è bassa.If this number is very high in relation to the table it is an indicator that the columnstore density is low.
[COMPRESSED_rowgroup_rows_DELETED][COMPRESSED_rowgroup_rows_DELETED] Le righe vengono eliminate in modo logico nel formato columnstore.Rows are logically deleted in columnstore format. Se il numero è elevato rispetto alle dimensioni della tabella, provare a ricreare la partizione o a ricompilare l'indice, perché in questo modo vengono eliminate fisicamente.If the number is high relative to table size, consider recreating the partition or rebuilding the index as this removes them physically.
[COMPRESSED_rowgroup_rows_MIN][COMPRESSED_rowgroup_rows_MIN] Usare questa colonna insieme alle colonne AVG e MAX per comprendere l'intervallo di valori per i gruppi di righe nel columnstore.Use this in conjunction with the AVG and MAX columns to understand the range of values for the row groups in your columnstore. Un numero basso oltre la soglia di caricamento, ad esempio 102.400 per ogni distribuzione di partizioni allineate, suggerisce che è possibile ottimizzare il caricamento dei dati.A low number over the load threshold (102,400 per partition aligned distribution) suggests that optimizations are available in the data load
[COMPRESSED_rowgroup_rows_MAX][COMPRESSED_rowgroup_rows_MAX] Come sopra.As above
[OPEN_rowgroup_count][OPEN_rowgroup_count] I gruppi di righe aperti sono normali.Open row groups are normal. È ragionevole prevedere un gruppo di righe aperto (OPEN) per ogni distribuzione di tabella (60).One would reasonably expect one OPEN row group per table distribution (60). Un numero eccessivo suggerisce il caricamento di dati tra le partizioni.Excessive numbers suggest data loading across partitions. Verificare la strategia di partizionamento per assicurarsi che sia valida.Double check the partitioning strategy to make sure it is sound
[OPEN_rowgroup_rows][OPEN_rowgroup_rows] Ogni gruppo di righe può contenere al massimo 1.048.576 righe.Each row group can have 1,048,576 rows in it as a maximum. Usare questo valore per controllare l'attuale livello di riempimento dei gruppi di righe aperti.Use this value to see how full the open row groups are currently
[OPEN_rowgroup_rows_MIN][OPEN_rowgroup_rows_MIN] I gruppi aperti indicano che i dati vengono caricati gradualmente nella tabella o che il carico precedente ha distribuito le righe rimanenti in questo gruppo di righe.Open groups indicate that data is either being trickle loaded into the table or that the previous load spilled over remaining rows into this row group. Usare le colonne MIN, MAX e AVG per visualizzare la quantità di dati rimasta nei gruppi di righe OPEN.Use the MIN, MAX, AVG columns to see how much data is sat in OPEN row groups. Per le tabelle di piccole dimensioni potrebbe essere il 100% di tutti i dati.For small tables it could be 100% of all the data! In tal caso, usare ALTER INDEX REBUILD per forzare i dati nel columnstore.In which case ALTER INDEX REBUILD to force the data to columnstore.
[OPEN_rowgroup_rows_MAX][OPEN_rowgroup_rows_MAX] Come sopra.As above
[OPEN_rowgroup_rows_AVG][OPEN_rowgroup_rows_AVG] Come sopra.As above
[CLOSED_rowgroup_rows][CLOSED_rowgroup_rows] Esaminare le righe dei gruppi di righe chiusi come controllo di integrità.Look at the closed row group rows as a sanity check.
[CLOSED_rowgroup_count][CLOSED_rowgroup_count] Il numero di gruppi di righe chiusi deve essere basso o pari a zero.The number of closed row groups should be low if any are seen at all. I gruppi di righe chiusi possono essere convertiti in gruppi di righe compressi usando l'istruzione ALTER INDEX... Comando REORGANIZE.Closed row groups can be converted to compressed row groups using the ALTER INDEX ... REORGANIZE command. In genere questa operazione non è tuttavia richiesta.However, this is not normally required. I gruppi chiusi vengono convertiti automaticamente in gruppi di righe columnstore dal processo in background del motore di tuple.Closed groups are automatically converted to columnstore row groups by the background "tuple mover" process.
[CLOSED_rowgroup_rows_MIN][CLOSED_rowgroup_rows_MIN] I gruppi di righe chiusi devono avere una velocità di riempimento molto elevata.Closed row groups should have a very high fill rate. Se la velocità di riempimento per un gruppo di righe chiuso è bassa, è necessario analizzare ulteriormente il columnstore.If the fill rate for a closed row group is low, then further analysis of the columnstore is required.
[CLOSED_rowgroup_rows_MAX][CLOSED_rowgroup_rows_MAX] Come sopra.As above
[CLOSED_rowgroup_rows_AVG][CLOSED_rowgroup_rows_AVG] Come sopra.As above
[Rebuild_Index_SQL][Rebuild_Index_SQL] Codice SQL per la ricompilazione dell'indice columnstore per una tabellaSQL to rebuild columnstore index for a table

Possibili cause di una qualità scadente dell'indice columnstoreCauses of poor columnstore index quality

Se sono state identificate tabelle di qualità scadente, è consigliabile identificarne la causa radice.If you have identified tables with poor segment quality, you want to identify the root cause. Di seguito sono riportate altre cause comuni della qualità scadente dei segmenti:Below are some other common causes of poor segment quality:

  1. Utilizzo elevato di memoria durante la compilazione dell'indiceMemory pressure when index was built
  2. Volume elevato di operazioni DMLHigh volume of DML operations
  3. Operazioni di caricamento di piccole dimensioni o con un flusso irregolare Small or trickle load operations
  4. Troppe partizioniToo many partitions

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.These factors can cause a columnstore index to have significantly less than the optimal 1 million rows per row group. E possono anche far sì che le righe vengano inserite nel gruppo di righe differenziale anziché nel gruppo di righe compresso.They can also cause rows to go to the delta row group instead of a compressed row group.

Utilizzo elevato di memoria durante la compilazione dell'indiceMemory pressure when index was built

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.The number of rows per compressed row group are directly related to the width of the row and the amount of memory available to process the row group. Quando le righe vengono scritte nelle tabelle columnstore in condizioni di utilizzo elevato di memoria, la qualità dei segmenti columnstore potrebbe risentirne.When rows are written to columnstore tables under memory pressure, columnstore segment quality may suffer. 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.Therefore, the best practice is to give the session which is writing to your columnstore index tables access to as much memory as possible. Visto il compromesso necessario tra memoria e concorrenza, la giusta quantità di memoria da allocare dipende dalla quantità di dati in ogni riga della tabella, dalle unità di data warehouse assegnate al sistema e dal numero di slot di concorrenza che è possibile assegnare alla sessione che sta scrivendo i dati nella tabella.Since there is a trade-off between memory and concurrency, the guidance on the right memory allocation depends on the data in each row of your table, the data warehouse units allocated to your system, and the number of concurrency slots you can give to the session which is writing data to your table. È consigliabile iniziare con xlargerc se si usa un valore DW300 o inferiore, largerc se si usa un valore DW400 o DW600 o mediumrc se si usa un valore DW1000 o superiore.As a best practice, we recommend starting with xlargerc if you are using DW300 or less, largerc if you are using DW400 to DW600, and mediumrc if you are using DW1000 and above.

Volume elevato di operazioni DMLHigh volume of DML operations

Un volume elevato di operazioni DML pesanti per l'aggiornamento e l'eliminazione di righe può causare l'inefficienza del columnstore.A high volume of DML operations that update and delete rows can introduce inefficiency into the columnstore. Ciò vale soprattutto quando viene modificata la maggior parte delle righe di un gruppo di righe.This is especially true when the majority of the rows in a row group are modified.

  • L'eliminazione di una riga da un gruppo di righe compresso si limita a contrassegnare in modo logico la riga come eliminata.Deleting a row from a compressed row group only logically marks the row as deleted. La riga rimane nel gruppo di righe compresso finché la partizione o la tabella non viene ricompilata.The row remains in the compressed row group until the partition or table is rebuilt.
  • Quando si inserisce una riga, questa viene aggiunta a una tabella rowstore interna detta gruppo di righe differenziale.Inserting a row adds the row to an internal rowstore table called a delta row group. La riga inserita non viene convertita in columnstore fino a quando il gruppo di righe differenziale non viene riempito e contrassegnato come chiuso.The inserted row is not converted to columnstore until the delta row group is full and is marked as closed. I gruppi di righe vengono chiusi quando raggiungono la capacità massima di 1.048.576 righe.Row groups are closed once they reach the maximum capacity of 1,048,576 rows.
  • L'aggiornamento di una riga nel formato columnstore viene elaborato come un'eliminazione logica e poi come operazione di inserimento.Updating a row in columnstore format is processed as a logical delete and then an insert. La riga inserita può essere archiviata nell'archivio differenziale.The inserted row may be stored in the 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.Batched update and insert operations that exceed the bulk threshold of 102,400 rows per partition-aligned distribution go directly to the columnstore format. Tuttavia, presupponendo una distribuzione uniforme, perché ciò si verifichi si dovranno modificare più di 6.144.000 righe in una singola operazione.However, assuming an even distribution, you would need to be modifying more than 6.144 million rows in a single operation for this to occur. Se il numero di righe per una determinata distribuzione allineata alle partizioni è inferiore a 102.400, le righe vengono inviate all'archivio differenziale e lì rimangono fino a quando non viene inserito o modificato un numero di righe sufficiente per chiudere il gruppo di righe o fino a quando l'indice non viene ricompilato.If the number of rows for a given partition-aligned distribution is less than 102,400 then the rows go to the delta store andstay there until sufficient rows have been inserted or modified to close the row group or the index has been rebuilt.

Operazioni di caricamento di piccole dimensioni o con un flusso irregolare Small or trickle load operations

I caricamenti di piccole dimensioni in SQL Data Warehouse sono anche definiti flussi irregolari.Small loads that flow into SQL Data Warehouse are also sometimes known as trickle loads. In genere rappresentano un flusso quasi costante di dati inseriti nel sistema.They typically represent a near constant stream of data being ingested by the system. Quando questo flusso è quasi continuo, tuttavia, il volume di righe non è particolarmente elevato.However, as this stream is near continuous the volume of rows is not particularly large. Molto spesso i dati sono notevolmente inferiori alla soglia necessaria per un caricamento diretto nel formato columnstore.More often than not the data is significantly under the threshold required for a direct load to columnstore format.

In queste situazioni è spesso preferibile inserire prima i dati nell'archivio BLOB di Azure e lasciarli accumulare prima di caricarli.In these situations, it is often better to land the data first in Azure blob storage and let it accumulate prior to loading. Questa tecnica viene spesso definita come micro invio in batch.This technique is often known as micro-batching.

Troppe partizioniToo many partitions

Un altro fattore da considerare è l'impatto del partizionamento sulle tabelle columnstore cluster.Another thing to consider is the impact of partitioning on your clustered columnstore tables. Prima di eseguire il partizionamento, SQL Data Warehouse divide già i dati in 60 database.Before partitioning, SQL Data Warehouse already divides your data into 60 databases. Il partizionamento, quindi, suddivide ulteriormente i dati.Partitioning further divides your data. 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.If you partition your data, then consider that each partition needs at least 1 million rows to benefit from a clustered columnstore index. 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.If you partition your table into 100 partitions, then your table needs at least 6 billion rows to benefit from a clustered columnstore index (60 distributions * 100 partitions * 1 million rows). Se la tabella da 100 partizioni non contiene 6 miliardi di righe, occorre ridurre il numero di partizioni o prendere in considerazione l'uso di una tabella heap.If your 100-partition table does not have 6 billion rows, either reduce the number of partitions or consider using a heap table instead.

Dopo aver caricato alcuni dati nelle tabelle, seguire questa procedura per identificare e ricompilare le tabelle con indici columnstore cluster non ottimali.Once your tables have been loaded with some data, follow the below steps to identify and rebuild tables with sub-optimal clustered columnstore indexes.

Ricompilazione degli indici per migliorare la qualità dei segmentiRebuilding indexes to improve segment quality

Passaggio 1: Identificare o creare l'utente per la classe di risorse appropriataStep 1: Identify or create user which uses the right resource class

Un metodo rapido per migliorare immediatamente la qualità dei segmenti consiste nella ricompilazione dell'indice.One quick way to immediately improve segment quality is to rebuild the index. Il codice SQL restituito dalla vista precedente restituisce un'istruzione ALTER INDEX REBUILD che può essere usata per ricompilare gli indici.The SQL returned by the above view returns an ALTER INDEX REBUILD statement which can be used to rebuild your indexes. Durante la ricompilazione degli indici, assicurarsi di allocare memoria sufficiente per la sessione di ricompilazione dell'indice.When rebuilding your indexes, be sure that you allocate enough memory to the session that rebuilds your index. 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.To do this, increase the resource class of a user which has permissions to rebuild the index on this table to the recommended minimum. Non è possibile modificare la classe di risorse dell'utente proprietario del database. Se non è ancora stato creato un utente nel sistema, è quindi necessario eseguire prima di tutto questa operazione.The resource class of the database owner user cannot be changed, so if you have not created a user on the system, you need to do so first. È consigliabile usare almeno la classe di risorse xlargerc con un valore di DW300 o inferiore, largerc con un valore da DW400 a DW600 o mediumrc con un valore di DW1000 o superiore.The minimum recommended resource class is xlargerc if you are using DW300 or less, largerc if you are using DW400 to DW600, and mediumrc if you are using DW1000 and above.

Di seguito è riportato un esempio di come allocare altra memoria per un utente, aumentando la relativa classe di risorse.Below is an example of how to allocate more memory to a user by increasing their resource class. Per informazioni sull'uso delle classi di risorse, vedere Classi di risorse per la gestione del carico di lavoro.To work with resource classes, see Resource classes for workload management.

EXEC sp_addrolemember 'xlargerc', 'LoadUser'

Passaggio 2: Ricompilare gli indici columnstore cluster con un utente che usa una classe di risorse superioreStep 2: Rebuild clustered columnstore indexes with higher resource class user

Accedere con le credenziali dell'utente indicato nel passaggio 1, ad esempio LoadUser, che ora usa una classe di risorse superiore, e quindi eseguire le istruzioni ALTER INDEX.Log in as the user from step 1 (e.g. LoadUser), which is now using a higher resource class, and execute the ALTER INDEX statements. Assicurarsi che l'utente abbia l'autorizzazione ALTER per le tabelle in cui viene ricompilato l'indice.Be sure that this user has ALTER permission to the tables where the index is being rebuilt. Questi esempi illustrano come ricompilare l'intero indice columnstore o una singola partizione.These examples show how to rebuild the entire columnstore index or how to rebuild a single partition. Nelle tabelle di grandi dimensioni, è consigliabile ricompilare gli indici procedendo una partizione alla volta.On large tables, it is more practical to rebuild indexes a single partition at a time.

In alternativa, invece di ricompilare l'indice è possibile copiare la tabella in una nuova tabella con CTAS.Alternatively, instead of rebuilding the index, you could copy the table to a new table using CTAS. Qual è il modo migliore?Which way is best? Per grandi volumi di dati CTAS è in genere più veloce di ALTER INDEX.For large volumes of data, CTAS is usually faster than ALTER INDEX. Per volumi di dati più piccoli, ALTER INDEX è più facile da usare e non richiede la sostituzione della tabella.For smaller volumes of data, ALTER INDEX is easier to use and won't require you to swap out the table. Per altre informazioni su come ricompilare gli indici con CTAS, vedere Ricompilazione degli indici con CTAS e cambio della partizione più avanti.See Rebuilding indexes with CTAS and partition switching below for more details on how to rebuild indexes with CTAS.

-- 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 in SQL Data Warehouse è un'operazione offline.Rebuilding an index in SQL Data Warehouse is an offline operation. Per altre informazioni sulla ricompilazione di indici, vedere la sezione ALTER INDEX REBUILD in Deframmentazione degli indici columnstore e ALTER INDEX.For more information about rebuilding indexes, see the ALTER INDEX REBUILD section in Columnstore Indexes Defragmentation, and ALTER INDEX.

Passaggio 3: Verificare che la qualità dei segmenti columnstore cluster sia migliorataStep 3: Verify clustered columnstore segment quality has improved

Eseguire nuovamente la query che ha identificato la tabella con una qualità scadente dei segmenti e verificare che la qualità sia migliorata.Rerun the query which identified table with poor segment quality and verify segment quality has improved. Se la qualità dei segmenti non è migliorata, le righe della tabella potrebbero essere troppo larghe.If segment quality did not improve, it could be that the rows in your table are extra wide. È consigliabile usare una classe di risorse superiore o una DWU durante la ricompilazione degli indici.Consider using a higher resource class or DWU when rebuilding your indexes.

Ricompilazione degli indici con CTAS e cambio della partizioneRebuilding indexes with CTAS and partition switching

In questo esempio viene usata l'istruzione CREATE TABLE AS SELECT (CTAS) e il cambio di partizione per ricompilare una partizione di tabella.This example uses the CREATE TABLE AS SELECT (CTAS) statement and partition switching to rebuild a table partition.

-- 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: Create a SWITCH out table
CREATE TABLE dbo.FactInternetSales_20000101
    WITH    (   DISTRIBUTION = HASH(ProductKey)
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101
                                )
                            )
            )
AS
SELECT *
FROM    [dbo].[FactInternetSales]
WHERE   1=2 -- Note this table will be empty

-- Step 3: Switch OUT the data 
ALTER TABLE [dbo].[FactInternetSales] SWITCH PARTITION 2 TO  [dbo].[FactInternetSales_20000101] PARTITION 2;

-- Step 4: Switch IN the rebuilt data
ALTER TABLE [dbo].[FactInternetSales_20000101_20010101] SWITCH PARTITION 2 TO  [dbo].[FactInternetSales] PARTITION 2;

Per informazioni dettagliate sulla ricreazione di partizioni tramite CTAS, vedere Using partitions in SQL Data Warehouse (Uso di partizioni in SQL Data Warehouse).For more details about re-creating partitions using CTAS, see Using partitions in SQL Data Warehouse.

Passaggi successiviNext steps

Per altre informazioni sullo sviluppo di tabelle, vedere Developing tables (Sviluppo di tabelle).For more information about developing tables, see Developing tables.