Indici columnstore - Linee guida per la progettazioneColumnstore indexes - design guidance

In questo argomento si applica a: SìSQL ServerSìDatabase SQL di AzureSìAzure SQL Data Warehouse Sì Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Suggerimenti generali per la progettazione di indici columnstore.High-level recommendations for designing columnstore indexes. Bastano poche scelte oculate per ottenere gli alti livelli di compressione dei dati e di prestazioni delle query per cui sono progettati gli indici columnstore.A small number of good design decisions helps you achieve the high data compression and query performance that columnstore indexes are designed to provide.

PrerequisitiPrerequisites

In questo articolo si presuppone una certa familiarità con la terminologia e l'architettura degli indici columnstore.This article assumes you are familiar with columnstore architecture and terminology. Per altre informazioni, vedere Indici columnstore - Panoramica e Indici columnstore - Architettura.For more information, see Columnstore indexes - overview and Columnstore indexes - architecture.

Conoscere i requisiti per i datiKnow your data requirements

Prima di progettare un indice columnstore, è importante conoscere i requisiti per i dati nel modo più approfondito possibile.Before designing a columnstore index, understand as much as possible about your data requirements. Ad esempio, valutare le risposte a queste domande:For example, think through the answers to these questions:

  • Quanto è grande la tabella?How large is my table?
  • Le query vengono usate principalmente per operazioni di analisi su intervalli di valori di grande estensione?Do my queries mostly perform analytics that scan large ranges of values? La progettazione degli indici columnstore li rende efficaci per analisi di intervalli di grandi dimensioni, piuttosto che per la ricerca di valori specifici.Columnstore indexes are designed to work well for large range scans rather than looking up specific values.
  • Il carico di lavoro prevede un numero elevato di aggiornamenti ed eliminazioni?Does my workload perform lots of updates and deletes? Gli indici columnstore sono utili quando i dati sono stabili.Columnstore indexes work well when the data is stable. Le query dovrebbero includere l'aggiornamento e l'eliminazione di meno del 10% delle righe.Queries should be updating and deleting less than 10% of the rows.
  • Sono disponibili tabelle dei fatti e delle dimensioni per un data warehouse?Do I have fact and dimension tables for a data warehouse?
  • È necessario eseguire analisi su un carico di lavoro transazionale?Do I need to perform analytics on a transactional workload? In questo caso, vedere le linee guida per la progettazione degli indici columnstore per l'analisi operativa in tempo reale.If this is the case, see the columnstore design guidance for real-time operational analytics.

Non è detto che sia necessario un indice columnstore.You might not need a columnstore index. Le tabelle rowstore con indici heap e cluster offrono prestazioni ottimali con le query che eseguono la ricerca di un valore specifico o all'interno di un intervallo di valori di piccole dimensioni.Rowstore tables with heaps or clustered indexes perform best on queries that seek into the data, searching for a particular value, or for queries on a small range of values. Usare gli indici rowstore con carichi di lavoro transazionali, perché per i carichi di lavoro di questo tipo sono in genere necessarie ricerche all'interno delle tabelle anziché analisi di intervalli estesi nelle tabelle.Use rowstore indexes with transactional workloads since they tend to require mostly table seeks instead of large range table scans.

Scegliere l'indice columnstore migliore per le proprie esigenzeChoose the best columnstore index for your needs

Un indice columnstore può essere cluster o non cluster.A columnstore index is either clustered or nonclustered. Un indice columnstore cluster può avere uno o più indici albero B non cluster.A clustered columnstore index can have one or more nonclustered btree indexes. È facile provare gli indici columnstore.Columnstore indexes are easy to try. Se si crea una tabella come indice columnstore, è possibile riconvertire facilmente la tabella in tabella rowstore eliminando l'indice columnstore.If you create a table as a columnstore index, you can easily convert the table back to a rowstore table by dropping the columnstore index.

Di seguito è riportato un riepilogo delle opzioni e dei suggerimenti.Here is a summary of the options and recommendations.

Opzione per columnstoreColumnstore option Uso consigliatoRecommendations for when to use CompressioneCompression
Indice columnstore clusterClustered columnstore index Usare per:Use for:
1) Carico di lavoro di data warehouse tradizionale con schema star o snowflake1) Traditional data warehouse workload with a star or snowflake schema
2) Carichi di lavoro Internet delle cose (IOT) per l'inserimento di grandi volumi di dati con aggiornamenti ed eliminazioni minimi.2) Internet of Things (IOT) workloads that insert large volumes of data with minimal updates and deletes.
10x in mediaAverage of 10x
Indici albero B non cluster su un indice columnstore clusterNonclustered btree indexes on a clustered columnstore index Usare per:Use to:
1) Applicare vincoli di chiave primaria e di chiave esterna su un indice columnstore cluster.1) Enforce primary key and foreign key constraints on a clustered columnstore index.
2) Velocizzare le query che eseguono la ricerca di valori specifici o in intervalli di valori limitati.2) Speed up queries that search for specific values or small ranges of values.
3) Velocizzare gli aggiornamenti e le eliminazioni di righe specifiche.3) Speed up updates and deletes of specific rows.
10x in media, con ulteriore spazio di archiviazione per gli indici non cluster.10x on average plus some additional storage for the NCIs.
Indice columnstore non cluster su un indice heap o albero B basato su discoNonclustered columnstore index on a disk-based heap or btree index Usare per:Use for:
1) Un carico di lavoro OLTP con alcune query analitiche.1) An OLTP workload that has some analytics queries. È possibile eliminare gli indici albero B creati per l'analisi e sostituirli con un solo indice columnstore non cluster.You can drop btree indexes created for analytics and replace them with one nonclustered columnstore index.
2) Molti carichi di lavoro OLTP tradizionali che eseguono operazioni di estrazione, trasformazione e caricamento (ETL) per spostare i dati in un data warehouse separato.2) Many traditional OLTP workloads that perform Extract Transform and Load (ETL) operations to move data to a separate data warehouse. È possibile evitare le operazioni ETL e la necessità di un data warehouse separato creando un indice columnstore non cluster su alcune delle tabelle OLTP.You can eliminate ETL and a separate data warehouse by creating a nonclustered columnstore index on some of the OLTP tables.
L'indice columnstore non cluster è un indice aggiuntivo che richiede in media il 10% in più di spazio di archiviazione.NCCI is an additional index that requires 10% more storage on average.
Indice columnstore su una tabella in memoriaColumnstore index on an in-memory table Le stesse indicazioni valide per un indice columnsore non cluster su una tabella basata su disco, ma la tabella di base è una tabella in memoria.Same recommendations as nonclustered columnstore index on a disk-based table, except the base table is an in-memory table. L'indice columnstore è un indice aggiuntivo.Columnstore index is an additional index.

Usare un indice columnstore cluster per tabelle di data warehouse di grandi dimensioniUse a clustered columnstore index for large data warehouse tables

L'indice columnstore cluster non è semplicemente un indice, ma è lo spazio di archiviazione principale per le tabelle.The clustered columnstore index is more than an index, it is the primary table storage. Consente di ottenere alti livelli di compressione dei dati e un miglioramento significativo delle prestazioni delle query per le tabelle dei fatti e delle dimensioni di data warehouse di grandi dimensioni.It achieves high data compression and a significant improvement in query performance on large data warehousing fact and dimension tables. Gli indici columnstore cluster sono più adatti a query di analisi piuttosto che a query transazionali, perché le query di analisi eseguono tendenzialmente operazioni su grandi intervalli di valori piuttosto che ricerche di valori specifici.Clustered columnstore indexes are best suited for analytics queries rather than transactional queries, since analytics queries tend to perform operations on large ranges of values rather than looking up specific values.

Valutare la possibilità di usare un indice columnstore cluster nei casi seguenti:Consider using a clustered columnstore index when:

  • Ogni partizione ha almeno un milione di righe.Each partition has at least a million rows. Gli indici columnstore usano rowgroup all'interno di ogni partizione.Columnstore indexes have rowgroups within each partition. Se la tabella è troppo piccola per riempire un rowgroup all'interno di ogni partizione, non si otterranno i vantaggi della compressione e delle prestazioni delle query offerte dagli indici columnstore.If the table is too small to fill a rowgroup within each partition, you won't get the benefits of columnstore compression and query performance.
  • Le query eseguono principalmente operazioni di analisi su intervalli di valori.Queries primarily perform analytics on ranges of values. Ad esempio, per trovare il valore medio di una colonna, la query deve analizzare tutti i valori della colonnaFor example, to find the average value of a column, the query needs to scan all the column values. e poi aggregare i valori sommandoli per determinare il valore medio.It then aggregates the values by summing them to determine the average.
  • La maggior parte degli inserimenti viene eseguita su grandi volumi di dati con aggiornamenti ed eliminazioni minimi.Most of the inserts are on large volumes of data with minimal updates and deletes. Molti carichi di lavoro, come i carichi Internet delle cose (IOT), eseguono l'inserimento di grandi volumi di dati con aggiornamenti ed eliminazioni minimi.Many workloads such as Internet of Things (IOT) insert large volumes of data with minimal updates and deletes. Questi carichi di lavoro possono trarre vantaggio dai miglioramenti a livello di compressione e prestazioni delle query derivanti dall'uso di un indice columnstore cluster.These workloads can benefit from the compression and query performance gains that comes from using a clustered columnstore index.

Non usare un indice columnstore cluster nei casi seguenti:Don't use a clustered columnstore index when:

  • La tabella richiede tipi di dati varchar(max), nvarchar(max) o varbinary(max).The table requires varchar(max), nvarchar(max), or varbinary(max) data types. In questo caso, progettare l'indice columnstore in modo che non includa queste colonne.Or, design the columnstore index so that it doesn't include these columns.
  • I dati della tabella non sono permanenti.The table data is not permanent. Prendere in considerazione l'uso di un heap o una tabella temporanea quando è necessario archiviare ed eliminare i dati rapidamente.Consider using a heap or temporary table when you need to store and delete the data quickly.
  • La tabella contiene meno di un milione di righe per partizione.The table has less than one million rows per partition.
  • Più del 10% delle operazioni sulla tabella sono aggiornamenti ed eliminazioni.More than 10% of the operations on the table are updates and deletes. Un numero elevato di aggiornamenti ed eliminazioni è causa di frammentazione.Large numbers of updates and deletes cause fragmentation. La frammentazione influisce sui tassi di compressione e sulle prestazioni delle query, fino a quando non si esegue un'operazione detta riorganizzazione che forza tutti i dati nel columnstore e rimuove la frammentazione.The fragmentation affects compression rates and query performance until you run an operation called reorganize that forces all data into the columnstore and removes fragmentation. Per altre informazioni, vedere Riduzione al minimo della frammentazione dell'indice negli indici columnstore.For more information, see Minimizing index fragmentation in columnstore index.

Per altre informazioni, vedere Indici columnstore - Data warehouse.For more information, see Columnstore indexes - data warehousing.

Aggiungere indici non cluster albero B per ricerche efficienti nelle tabelleAdd btree nonclustered indexes for efficient table seeks

A partire da SQL Server 2016, è possibile creare indici albero B non cluster come indici secondari in un indice columnstore cluster.Beginning with SQL Server 2016, you can create nonclustered btree indexes as secondary indexes on a clustered columnstore index. L'indice albero B non cluster viene aggiornato con le modifiche apportate all'indice columnstore.The nonclustered btree index is updated as changes occur to the columnstore index. Si tratta di una funzionalità potente con numerosi vantaggi.This is a powerful feature that you can use to your advantage.

L'uso di un indice albero B secondario consente di eseguire ricerche di righe specifiche in modo efficiente, senza dover analizzare tutte le righe.By using the secondary btree index, you can efficiently search for specific rows without scanning through all the rows. Sono disponibili anche altre opzioni.Other options become available too. È possibile, ad esempio, applicare un vincolo di chiave primaria o chiave esterna tramite un vincolo UNIQUE sull'indice albero B.For example, you can enforce a primary or foreign key constraint by using a UNIQUE constraint on the btree index. Di conseguenza, poiché non è possibile inserire un valore non univoco nell'indice albero B, SQL Server non può inserire il valore nel columnstore.Since an non-unique value will fail to insert into the btree index, SQL Server cannot insert the value into the columnstore.

Valutare la possibilità di usare un indice albero B su un indice columnstore nei casi seguenti:Consider using a btree index on a columnstore index to:

  • Per eseguire query per la ricerca di valori specifici o in intervalli di valori limitati.Run queries that search for particular values or small ranges of values.
  • Per applicare un vincolo, ad esempio un vincolo di chiave primaria o di chiave esterna.Enforce a constraint such as a primary key or foreign key constraint.
  • Per eseguire con efficienza operazioni di aggiornamento ed eliminazione.Efficiently perform update and delete operations. L'indice albero B consente di individuare rapidamente le righe specifiche per gli aggiornamenti e le eliminazioni, senza analizzare l'intera tabella o un'intera partizione di una tabella.The btree index is able to quickly locate the specific rows for updates and deletes without scanning the full table or partition of a table.
  • È disponibile spazio aggiuntivo per l'archiviazione dell'indice albero B.You have additional storage available to store the btree index.

Usare un indice columnstore non cluster per analisi in tempo realeUse a nonclustered columnstore index for real-time analytics

A partire da SQL Server 2016SQL Server 2016 è possibile creare un indice columnstore non cluster su una tabella rowstore basata su disco o su una tabella OLTP in memoria.Beginning with SQL Server 2016SQL Server 2016, you can have a nonclustered columnstore index on a rowstore disk-based table or an in-memory OLTP table. Questo rende possibile l'esecuzione di analisi in tempo reale su una tabella transazionale.This makes it possible to run the analytics in real-time on a transactional table. In questo modo è possibile eseguire allo stesso tempo analisi sull'indice columnstore e le transazioni sulla tabella sottostante.While transactions are occurring on the underlying table, you can run analytics on the columnstore index. Dato che una sola tabella gestisce entrambi gli indici, le modifiche sono disponibili in tempo reale sia per l'indice rowstore che per l'indice columnstore.Since one table manages both indexes, changes are available in real-time to both the rowstore and the columnstore indexes.

Un indice columnstore consente di ottenere livelli di compressione dei dati 10 volte migliori rispetto a un indice rowstore, quindi richiede solo una piccola quantità di spazio di archiviazione aggiuntivo.Since a columnstore index achieves 10x better data compression than a rowstore index, it only needs a small amount of extra storage. Ad esempio, se la tabella rowstore compressa richiede 20 GB, l'indice columnstore potrebbe richiedere altri 2 GB.For example, if the compressed rowstore table takes 20 GB, the columnstore index might require an additional 2 GB. Lo spazio aggiuntivo necessario dipende anche dal numero di colonne nell'indice columnstore non cluster.The additional space required also depends on the number of columns in the nonclustered columnstore index.

Valutare la possibilità di usare un indice columnstore non cluster nei casi seguenti:Consider using a nonclustered columnstore index to:

  • Per eseguire analisi in tempo reale su una tabella rowstore transazionale.Run analytics in real-time on a transactional rowstore table. È possibile sostituire gli indici albero B esistenti progettati per l'analisi con un indice columnstore non cluster.You can replace existing btree indexes that are designed for analytics with a nonclustered columnstore index.

  • Per evitare la necessità di un data warehouse separato.Eliminate the need for a separate data warehouse. In genere, le aziende eseguono le transazioni in una tabella rowstore e quindi caricano i dati in un data warehouse separato per le operazioni di analisi.Traditionally, companies run transactions on a rowstore table and then load the data into a separate data warehouse to run analytics. Per molti carichi di lavoro, è possibile evitare il processo di caricamento e la disponibilità di un data warehouse separato creando un indice columnstore non cluster sulle tabelle transazionali.For many workloads, you can eliminate the loading process and the separate data warehouse by creating a nonclustered columnstore index on transactional tables.

    SQL Server 2016 offre diverse strategie per rendere efficiente questo scenario.SQL Server 2016 offers several strategies to make this scenario performant. È molto semplice da provare, perché è possibile abilitare un indice columnstore non cluster senza dover modificare l'applicazione OLTP.It's very easy to try it since you can enable a nonclustered columnstore index with no changes to your OLTP application.

Per aggiungere ulteriori risorse di elaborazione, è possibile eseguire le operazioni di analisi su una replica secondaria leggibile.To add additional processing resources, you can run the analytics on a readable secondary. L'uso di una replica secondaria leggibile consente di separare l'elaborazione del carico di lavoro transazionale e del carico di lavoro di analisi.Using a readable secondary separates the processing of the transactional workload and the analytics workload.

Per altre informazioni, vedere Get started with columnstore indexes for real-time operational analytics (Introduzione agli indici columnstore per l'analisi operativa in tempo reale).For more information, see Get started with columnstore indexes for real-time operational analytics

Per altre informazioni sulla scelta dell'indice columnstore ottimale, vedere il blog di Sunil Agarwal Which columnstore index is right for my workload? (Qual è l'indice columnstore più appropriato per un carico di lavoro?).For more information on choosing the best columnstore index, see Sunil Agarwal's blog Which columnstore index is right for my workload?.

Usare le partizioni di tabella per la gestione dei dati e le prestazioni delle queryUse table partitions for data management and query performance

Gli indici columnstore supportano il partizionamento, che rappresenta una soluzione efficace per la gestione e l'archiviazione dei dati.Columnstore indexes support partitioning which is a good way to manage and archive data. Il partizionamento consente anche di migliorare le prestazioni delle query, limitando le operazioni a una o più partizioni.Partitioning also improves query performance by limiting operations to one or more partitions.

Usare le partizioni per semplificare la gestione dei datiUse partitions to make the data easier to manage

Per le tabelle di grandi dimensioni, l'uso delle partizioni è l'unico modo pratico per gestire gli intervalli di dati.For large tables, the only practical way to manage ranges of data is by using partitions. I vantaggi delle partizioni per le tabelle rowstore si applicano anche agli indici columnstore.The advantages of partitions for rowstore tables also apply to columnstore indexes.

Ad esempio, sia le tabelle rowstore che le tabelle columnstore usano le partizioni per:For example, both rowstore and columnstore tables use partitions to:

  • Controllare le dimensioni dei backup incrementali.Control the size of incremental backups. È possibile eseguire il backup delle partizioni in filegroup separati e quindi contrassegnarli come di sola lettura.You can back up partitions to separate filegroups and then mark them as read-only. In questo modo, i backup futuri ignoreranno i filegroup di sola lettura.By doing this, future backups will skip the read-only filegroups.
  • Ridurre i costi di archiviazione spostando una partizione meno recente in uno spazio di archiviazione meno costoso.Save storage costs by moving an older partition to less expensive storage. Ad esempio, è possibile usare il cambio della partizione per spostare una partizione in una posizione di archiviazione meno costosa.For example, you could use partition switching to move a partition to a less expensive storage location.
  • Eseguire operazioni in modo efficiente limitando le operazioni a una partizione.Perform operations efficiently by limiting the operations to a partition. Ad esempio, è possibile selezionare solo le partizioni frammentate per la manutenzione degli indici.For example, you can target only the fragmented partitions for index maintenance.

Con un indice columnstore è inoltre possibile usare il partizionamento per:Additionally, with a columnstore index, you use partitioning to:

  • Risparmiare un ulteriore 30% sui costi di archiviazione.Save an additional 30% in storage costs. È possibile comprimere le partizioni meno recenti con le opzioni di compressione COLUMNSTORE_ARCHIVE.You can compress older partitions with the COLUMNSTORE_ARCHIVE compression options. Le prestazioni delle query per questi dati saranno più lente, ma ciò è accettabile se le query su questa partizione sono poco frequenti.The data will be slower for query performance, which is acceptable if the partition is queries infrequently.

Usare le partizioni per migliorare le prestazioni delle queryUse partitions to improve query performance

L'uso delle partizioni consente di limitare le query all'analisi di partizioni specifiche, con conseguente contenimento del numero di righe da analizzare.By using partitions, you can limit your queries to scan only specific partitions which limits the number of rows to scan. Ad esempio, se l'indice viene partizionato in base agli anni e la query deve analizzare i dati dell'anno precedente, l'analisi sarà limitata a una sola partizione.For example, if the index is partitioned by year and the query is analyzing data from last year, it only needs to scan the data in one partition.

Usare meno partizioni per un indice columnstoreUse fewer partitions for a columnstore index

A meno che le dimensioni dei dati non siano sufficientemente grandi, un indice columnstore offre prestazioni migliori con meno partizioni, rispetto al numero di partizioni generalmente usato per un indice rowstore.Unless you have a large enough data size, a columnstore index performs best with fewer partitions than what you might use for a rowstore index. Se ogni partizione non include almeno un milione di righe, la maggior parte delle righe potrebbe essere trasferita all'archivio differenziale, perdendo quindi i vantaggi a livello di prestazioni derivanti dalla compressione del columnstore.If you don't have at least one million rows per partition, most of your rows might go to the deltastore where they don't receive the performance benefit of columnstore compression. Ad esempio, se si carica un milione di righe in una tabella con 10 partizioni e ogni partizione riceve 100.000 righe, tutte le righe passeranno ai rowgroup differenziali.For example, if you load one million rows into a table with 10 partitions and each partition receives 100,000 rows, all of the rows will go to delta rowgroups.

Esempio:Example:

  • Caricare 1.000.000 righe in una singola partizione o in una tabella non partizionata.Load 1,000,000 rows into one partition or a non-partitioned table. È possibile ottenere un rowgroup compresso con 1.000.000 di righe.You get one compressed rowgroup with 1,000,000 rows. Questa è la configurazione ideale per ottenere alti livelli una compressione dei dati e buone prestazioni per le query.This is great for high data compression and fast query performance.
  • Caricare 1.000.000 righe in modo uniforme in 10 partizioni.Load 1,000,000 rows evenly into 10 partitions. Ogni partizione riceve 100.000 righe, ovvero un numero minore rispetto alla soglia minima per la compressione del columnstore.Each partition gets 100,000 rows, which is less than the minimum threshold for columnstore compression. Di conseguenza, l'indice columnstore potrebbe avere 10 rowgroup differenziali con 100.000 righe ognuno.As a result the columnstore index could have 10 delta rowgroups with 100,000 rows in each. Esistono modi per forzare i rowgroup differenziali nel columnstore.There are ways to force the delta rowgroups into the columnstore. Tuttavia, se si tratta delle uniche righe nell'indice columnstore, i rowgroup compressi saranno troppo piccoli per ottenere livelli ottimali di compressione e prestazioni delle query.However, if these are the only rows in the columnstore index, the comrpessed rowgroups will be too small for best compression and query performance.

Per altre informazioni sul partizionamento, vedere il post di blog di Sunil Agarwal Should I partition my columnstore index? (È consigliabile partizionare l'indice columnstore?).For more information about partitioning, see Sunil Agarwal's blog post, Should I partition my columnstore index?.

Scegliere il metodo di compressione dei dati appropriatoChoose the appropriate data compression method

L'indice columnstore offre due opzioni per la compressione dei dati: compressione del columnstore e compressione dell'archivio.The columnstore index offers two choices for data compression: columnstore compression and archive compression. È possibile scegliere l'opzione di compressione quando si crea l'indice o modificarlo in un secondo momento con ALTER INDEX ... REBUILD.You can choose the compression option when you create the index, or change it later with ALTER INDEX ... REBUILD.

Usare la compressione del columnstore per ottimizzare le prestazioni di queryUse columnstore compression for best query performance

La compressione del columnstore consente in genere di ottenere tassi di compressione 10 volte migliori rispetto agli indici rowstore.Columnstore compression typically achieves 10x better compression rates over rowstore indexes. Si tratta del metodo di compressione standard per gli indici columnstore e consente di ottenere prestazioni migliori per le query.It is the standard compression method for columnstore indexes and enables fast query performance.

Usare la compressione degli archivi per ottenere livelli ottimali di compressione dei datiUse archive compression for best data compression

La compressione degli archivi è progettata per ottenere la massima compressione quando le prestazioni delle query non sono così importantiArchive compression is designed for maximum compression when query performance is not as important. e consente di ottenere tassi di compressione dei dati migliori rispetto alla compressione del columnstore, anche se questo vantaggio ha un prezzo.It achieves higher data compression rates than columnstore compression, but it comes with a price. La compressione e la decompressione dei dati richiedono infatti più tempo, quindi non è una soluzione adatta se sono necessarie prestazioni veloci per le query.It takes longer to compress and decompress the data, so it is not well-suited for fast query performance.

Usare le ottimizzazioni per convertire una tabella rowstore in un indice columnstoreUse optimizations when you convert a rowstore table to a columnstore index

Se i dati sono già disponibili in una tabella rowstore, è possibile usare l'istruzione CREATE COLUMNSTORE INDEX per convertire la tabella in un indice columnstore cluster.If your data is already in a rowstore table, you can use CREATE COLUMNSTORE INDEX to convert the table to a clustered columnstore index. Esistono un paio di ottimizzazioni in grado di migliorare le prestazioni delle query dopo la conversione della tabella.There's a couple optimizations that will improve query performance after the table is converted.

Usare MAXDOP per migliorare la qualità del rowgroupUse MAXDOP to improve rowgroup quality

È possibile configurare il numero massimo di processori per la conversione di un indice heap o albero B cluster in un indice columnstore.You can configure the maximum number of processors for converting a heap or clustered btree index to a columnstore index. Per configurare i processori, usare l'opzione per il massimo grado di parallelismo (MAXDOP).To configure the processors, use the maximum degree of parallelism option (MAXDOP).

In presenza di grandi quantità di dati, è probabile che l'opzione MAXDOP 1 sia troppo lenta.If you have large amounts of data, MAXDOP 1 will likely be too slow. È possibile ottenere buoni risultati aumentando MAXDOP a 4.Increasing MAXDOP to 4 works fine. Se si ottengono meno rowgroup senza il numero ottimale di righe, è possibile usare ALTER INDEX REORG per unirli in background.If this results in a few rowgroups that do not have the optimal number of rows you can run ALTER INDEX REORG to merge them together in the background.

Mantenere l'ordinamento di un indice albero BKeep the sorted order of a btree index

Dato che le righe vengono già archiviate con un ordine nell'indice albero B, mantenere tale ordinamento quando le righe vengono compresse nell'indice columnstore può portare a un miglioramento delle prestazioni delle query.Since the btree index already stores rows in a sorted order, preserving that order when the rows get compressed into the columnstore index can improve query performance.

L'indice columnstore non ordina i dati, ma usa i metadati per tenere traccia dei valori minimi e massimi di ogni segmento di colonna in ogni rowgroup.The columnstore index does not sort the data, but it does use metadata to track the minimum and maximum values of each column segment in each rowgroup. Durante l'analisi di un intervallo di valori, può rapidamente calcolare quando ignorare il rowgroup.When scanning for a range of values, it can quickly compute when to skip the rowgroup. Quando i dati sono ordinati, possono essere ignorati più rowgroup.When the data is ordered, more rowgroups can be skipped.

Per mantenere l'ordinamento durante la conversione:To preserve the sorted order during conversion:

  • Usare CREATE COLUMNSTORE INDEX con la clausola DROP_EXISTING.Use CREATE COLUMNSTORE INDEX with the DROP_EXISTING clause. Viene così mantenuto anche il nome dell'indice.This also preserves the name of the index. Se esistono script che usano già il nome dell'indice rowstore non sarà necessario aggiornarli.If you have scripts that already use the name of the rowstore index you won't need to update them.

    Questo esempio converte un indice rowstore cluster su una tabella denominata MyFactTable in un indice columnstore cluster.This example converts a clustered rowstore index on a table named MyFactTable to a clustered columnstore index. Il nome dell'indice, ClusteredIndex_d473567f7ea04d7aafcac5364c241e09, rimane invariato.The index name, ClusteredIndex_d473567f7ea04d7aafcac5364c241e09, stays the same.

    CREATE CLUSTERED COLUMNSTORE INDEX ClusteredIndex_d473567f7ea04d7aafcac5364c241e09  
    ON MyFactTable  
    WITH (DROP_EXISTING = ON);  
    

La tabella seguente riepiloga le attività per la creazione e la manutenzione degli indici columnstore.These are tasks for creating and maintaining columnstore indexes.

AttivitàTask Argomenti di riferimentoReference Topics NoteNotes
Creare una tabella come columnstore.Create a table as a columnstore. CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL) A partire da SQL Server 2016SQL Server 2016è possibile creare la tabella come indice columnstore cluster.Beginning with SQL Server 2016SQL Server 2016, you can create the table as a clustered columnstore index. Non è necessario creare prima una tabella rowstore e quindi convertirla in columnstore.You do not have to first create a rowstore table and then convert it to columnstore.
Creare una tabella in memoria con un indice columnstore.Create a memory table with a columnstore index. CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL) A partire da SQL Server 2016SQL Server 2016 è possibile creare una tabella in memoria ottimizzata con un indice columnstore.Beginning with SQL Server 2016SQL Server 2016, you can create a memory-optimized table with a columnstore index. L'indice columnstore può anche essere aggiunto dopo aver creato la tabella, usando la sintassi ALTER TABLE ADD INDEX.The columnstore index can also be added after the table is created, using the ALTER TABLE ADD INDEX syntax.
Convertire una tabella rowstore in un columnstore.Convert a rowstore table to a columnstore. CREATE COLUMNSTORE INDEX (Transact-SQL)CREATE COLUMNSTORE INDEX (Transact-SQL) Convertire un heap o un albero binario esistente o in un columnstore.Convert an existing heap or binary tree to a columnstore. Gli esempi illustrano come gestire gli indici esistenti e il nome dell'indice quando si esegue questa conversione.Examples show how to handle existing indexes and also the name of the index when performing this conversion.
Convertire una tabella columnstore in un rowstore.Convert a columnstore table to a rowstore. CREATE COLUMNSTORE INDEX (Transact-SQL)CREATE COLUMNSTORE INDEX (Transact-SQL) Di solito non è necessario eseguire questa conversione, ma talvolta potrebbe presentarsene la necessità.Usually this is not necessary, but there can be times when you need to perform this conversion. Gli esempi illustrano come convertire un columnstore in un heap o in un indice cluster.Examples show how to convert a columnstore to a heap or clustered index.
Creare un indice columnstore per una tabella rowstore.Create a columnstore index on a rowstore table. CREATE COLUMNSTORE INDEX (Transact-SQL)CREATE COLUMNSTORE INDEX (Transact-SQL) Una tabella rowstore può avere un solo indice columnstore.A rowstore table can have one columnstore index. A partire da SQL Server 2016SQL Server 2016l'indice columnstore può avere una condizione di filtro.Beginning with SQL Server 2016SQL Server 2016, the columnstore index can have a filtered condition. Gli esempi illustrano la sintassi di base.Examples show the basic syntax.
Creare indici ad alte prestazioni per l'analisi operativa.Create performant indexes for operational analytics. Introduzione a columnstore per l'analisi operativa in tempo realeGet started with Columnstore for real time operational analytics Descrive come creare indici columnstore e BTree complementari in modo che le query OLTP usino gli indici BTree e le query di analisi usino gli indici columnstore.Describes how to create complementary columnstore and btree indexes so that OLTP queries use btree indexes and analytics queries use columnstore indexes.
Creare indici columnstore efficienti per il data warehousing.Create performant columnstore indexes for data warehousing. Indici columnstore - Data warehouseColumnstore indexes - data Warehousing Descrive come usare gli indici BTree con le tabelle columnstore per creare query di data warehousing ad alte prestazioni.Describes how to use btree indexes on columnstore tables to create performant data warehousing queries.
Usare un indice BTree per imporre un vincolo di chiave primaria per un indice columnstore.Use a btree index to enforce a primary key constraint on a columnstore index. Indici columnstore - Data warehouseColumnstore indexes - data warehousing Illustra come combinare indici BTree e columnstore per imporre vincoli di chiave primaria per l'indice columnstore.Shows how to combine btree and columnstore indexes to enforce primary key constraints on the columnstore index.
Rimuovere un indice columnstoreDrop a columnstore index DROP INDEX (Transact-SQL)DROP INDEX (Transact-SQL) Per rimuovere un indice columnstore si usa la sintassi DROP INDEX standard usata dagli indici BTree.Dropping a columnstore index uses the standard DROP INDEX syntax that btree indexes use. La rimozione di un indice columnstore cluster converte la tabella columnstore in un heap.Dropping a clustered columnstore index will convert the columnstore table to a heap.
Eliminare una riga da un indice columnstoreDelete a row from a columnstore index DELETE (Transact-SQL)DELETE (Transact-SQL) Usare DELETE (Transact-SQL) per eliminare una riga.Use DELETE (Transact-SQL) to delete a row.

Rigacolumnstore : SQL ServerSQL Server contrassegna la riga come eliminata logicamente ma recupera lo spazio di archiviazione fisico della riga solo dopo che l'indice è stato ricompilato.columnstore row: SQL ServerSQL Server marks the row as logically deleted but does not reclaim the physical storage for the row until the index is rebuilt.

Rigadeltastore : SQL ServerSQL Server elimina la riga logicamente e fisicamente.deltastore row: SQL ServerSQL Server logically and physically deletes the row.
Aggiornare una riga nell'indice columnstoreUpdate a row in the columnstore index UPDATE (Transact-SQL)UPDATE (Transact-SQL) Usare UPDATE (Transact-SQL) per aggiornare una ruga.Use UPDATE (Transact-SQL) to update a row.

Rigacolumnstore : SQL ServerSQL Server contrassegna la riga come eliminata logicamente e quindi inserisce la riga aggiornata nel deltastore.columnstore row: SQL ServerSQL Server marks the row as logically deleted, and then inserts the updated row into the deltastore.

Rigadeltastore : SQL ServerSQL Server aggiorna la riga nel deltastore.deltastore row: SQL ServerSQL Server updates the row in the deltastore.
Forzare il passaggio di tutte le righe del deltastore nel columnstore.Force all rows in the deltastore to go into the columnstore. ALTER INDEX (Transact-SQL) ... REBUILDALTER INDEX (Transact-SQL) ... REBUILD

Indici columnstore - DeframmentazioneColumnstore indexes - defragmentation
ALTER INDEX con l'opzione REBUILD forza il passaggio di tutte le righe nel columnstore.ALTER INDEX with the REBUILD option forces all rows to go into the columnstore.
Deframmentare un indice columnstoreDefragment a columnstore index ALTER INDEX (Transact-SQL)ALTER INDEX (Transact-SQL) ALTER INDEX …ALTER INDEX … REORGANIZE consente di deframmentare indici columnstore online.REORGANIZE defragments columnstore indexes online.
Unire tabelle con indici columnstore.Merge tables with columnstore indexes. MERGE (Transact-SQL)MERGE (Transact-SQL)

Passaggi successiviNext steps

Per creare un indice columnstore vuoto per:To create an empty columnstore index for:

Per convertire un indice heap o albero B rowstore esistente in un indice columnstore cluster o per creare un indice columnstore non cluster, usare:To convert an existing rowstore heap or btree index to a clustered columnstore index, or to create a nonclustered columnstore index, use: