Indici columnstore - Prestazioni delle queryColumnstore indexes - query performance

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

Indicazioni per ottenere prestazioni delle query elevate che è possibile raggiungere con la progettazione degli indici columnstore.Recommendations for achieving the very fast query performance that columnstore indexes are designed to provide.

Gli indici columnstore possono raggiungere un miglioramento fino a 100 volte in termini di prestazioni per le analisi e i carichi di lavoro di data warehouse e fino a 10 volte in termini di compressione dei dati rispetto agli indici rowstore tradizionali.Columnstore indexes can achieve up to 100x better performance on analytics and data warehousing workloads and up to 10x better data compression than traditional rowstore indexes. Queste indicazioni di ottenere prestazioni delle query elevate che è possibile raggiungere con la progettazione degli indici columnstore.These recommendations will help your queries achieve the very fast query performance that columnstore indexes are designed to provide. Alla fine dell'articolo vengono fornite ulteriori spiegazioni sulle prestazioni di columnstore.Further explanations about columnstore performance are at the end.

Indicazioni per migliorare le prestazioni delle queryRecommendations for improving query performance

Di seguito sono fornite alcune indicazioni per ottenere le prestazioni elevate che è possibile raggiungere con la progettazione degli indici columnstore.Here are some recommendations for achieving the high performance columnstore indexes are designed to provide.

1. Organizzare i dati per eliminare più rowgroup da una scansione di tabella completa1. Organize data to eliminate more rowgroups from a full table scan

  • Usare l'ordine di inserimento.Leverage insert order. In genere, in un data warehouse tradizionale i dati vengono inseriti in ordine temporale e le analisi vengono eseguite in una dimensione temporale,In common case in traditional data warehouse, the data is indeed inserted in time order and analytics is done in time dimension. come nel caso delle analisi delle vendite per trimestre.For example, analyzing sales by quarter. Per questo tipo di carico di lavoro, l'eliminazione del rowgroup viene eseguita automaticamente.For this kind of workload, the rowgroup elimination happens automatically. In SQL Server 2016 diversi rowgroup vengono ignorati durante l'elaborazione della query.In SQL Server 2016, you can find out number rowgroups skipped as part of query processing.

  • Usare l'indice rowstore cluster.Leverage the rowstore clustered index. Se il predicato della query comune è in una colonna (ad esempio C1) che non è correlata all'ordine di inserimento della riga, è possibile creare un indice rowstore cluster nelle colonne C1 e quindi creare l'indice columstore cluster eliminando l'indice rowstore cluster.If the common query predicate is on a column (e.g. C1) that is unrelated to insert order of the row, you can create a rowstore clustered index on columns C1 and then create clustered columstore index by dropping the rowstore clustered index. Se si crea l'indice columnstore cluster in modo esplicito usando DOP (grado di parallelismo) = 1, l'indice columnstore cluster risultante verrà ordinato perfettamente nella colonna C1.if you create the clustered columnstore index explicitly using DOP (degree of parallelism) = 1, the resultant clustered columnstore index will be perfectly ordered on column C1. Se si specifica DOP=8, viene visualizzata una sovrapposizione di valori in 8 rowgroup.If you specify DOP=8, then you will see overlap of values across 8 rowgroups. Un caso comune di applicazione di questa strategia riguarda l'indice columnstore creato inizialmente con un set di dati di grandi dimensioni.A common case of this strategy when you initially create columnstore index with large set of data. Per un indice columnstore non cluster (NCCI), se la tabella rowstore di base include un indice cluster, le righe risultano già ordinate.Note, for nonclustered columnstore index (NCCI), if the base rowstore table has a clustered index, the rows are already ordered. In questo caso, l'indice columnstore non cluster risultante sarà ordinato automaticamente.In this case, the resultant nonclustered columnstore index will automatically be ordered. È importante notare che l'indice columnstore non mantiene automaticamente l'ordine delle righe.One important point to note is that columnstore index does not inherently maintain the order of rows. Quando vengono inserite nuove righe o vengono aggiornate righe meno recenti, potrebbe essere necessario ripetere il processo a causa di un possibile deterioramento delle prestazioni delle query di analisi.As new rows are inserted or older rows are updated, you may need to repeat the process as the analytics query performance may deteriorate

  • Usare il partizionamento delle tabelle.Leverage table partitioning. È possibile partizionare l'indice columnstore e usare l'eliminazione delle partizioni per ridurre il numero di rowgroup da analizzare.You can partition the columnstore index and then use partition elimination to reduce number of rowgroups to scan. Ad esempio, se si ha una tabella dei fatti in cui vengono archiviati gli acquisti eseguiti dai clienti e si usa un modello di query comune per cercare gli acquisti eseguiti trimestralmente da un determinato cliente, è possibile combinare l'ordine di inserimento con il partizionamento nella colonna del cliente.For example, a fact table stores purchases made by customers and a common query pattern is to find quarterly purchases done by a specific customer, you can combine the insert order with partitioning on customer column. Ogni partizione contiene righe in ordine temporale per un determinato cliente.Each partition will contain rows in time order for specific customer.

2. Pianificare una quantità di memoria sufficiente per creare indici columnstore in parallelo2. Plan for enough memory to create columnstore indexes in parallel

Per impostazione predefinita, la creazione di un indice columnstore è un'operazione parallela, a meno che la memoria non sia vincolata.Creating a columnstore index is by default a parallel operation unless memory is constrained. La creazione dell'indice in parallelo richiede più memoria rispetto alla creazione dell'indice in modo seriale.Creating the index in parallel requires more memory than creating the index serially. Se si dispone di un'ampia quantità di memoria, la creazione di un indice columnstore richiede un tempo di circa 1,5 volte superiore rispetto alla compilazione di un albero B nelle stesse colonne.When there is ample memory, creating a columnstore index takes on the order of 1.5 times as long as building a B-tree on the same columns.

La memoria richiesta per la creazione di un indice columnstore dipende dal numero di colonne, dal numero di colonne stringa, dal grado di parallelismo e dalle caratteristiche dei dati.The memory required for creating a columnstore index depends on the number of columns, the number of string columns, the degree of parallelism (DOP), and the characteristics of the data. Ad esempio, se la tabella contiene meno di un milione di righe, SQL Server utilizzerà un solo thread per creare l'indice columnstore.For example, if your table has fewer than one million rows, SQL Server will use only one thread to create the columnstore index.

Se la tabella dispone di più di un milione di righe, ma SQL Server non ha a disposizione memoria sufficiente per creare l'indice utilizzando MAXDOP, SQL Server ridurrà automaticamente MAXDOP secondo le esigenze per adattarsi alla memoria disponibile.If your table has more than one million rows, but SQL Server cannot get a large enough memory grant to create the index using MAXDOP, SQL Server will automatically decrease MAXDOP as needed to fit into the available memory grant. In alcuni casi, è necessario ridurre il grado di parallelismo a uno per compilare l'indice nella memoria vincolata.In some cases, DOP must be decreased to one in order to build the index under constrained memory.

A partire da SQL Server 2016, la query viene eseguita sempre in modalità batch.Beginning with SQL Server 2016, the query will always operate in batch mode. Nelle versioni precedenti l'esecuzione batch viene usata solo quando DOP è maggiore di uno.In previous releases, batch execution is only used when DOP is greater than one.

Spiegazione delle prestazioni columnstoreColumnstore Performance Explained

Gli indici columnstore raggiungono prestazioni delle query ottimali combinando l'elaborazione in modalità batch in memoria ad alta velocità con tecniche che riducono significativamente i requisiti per le operazioni I/O.Columnstore indexes achieve high query performance by combining high-speed in-memory batch mode processing with techniques that greatly reduce IO requirements. Poiché le query di analisi analizzano un numero elevato di righe, in genere sono associate alle operazioni I/O, quindi la riduzione di tali operazioni durante l'esecuzione delle query è fondamentale per la progettazione di indici columnstore.Since analytics queries scan large numbers of rows, they are typically IO-bound, and therefore reducing IO during query execution is critical to the design of columnstore indexes. Dopo la lettura dei dati in memoria, è molto importante ridurre il numero di operazioni in memoria.Once data has been read into memory, it is critical to reduce the number of in-memory operations.

Gli indici columnstore riducono le operazioni I/O e ottimizzano le operazioni in memoria grazie a un'elevata compressione dei dati, l'eliminazione di columnstore, l'eliminazione di rowgroup e l'elaborazione batch.Columnstore indexes reduce IO and optimize in-memory operations through high data compression, columnstore elimination, rowgroup elimination, and batch processing.

Compressione datiData compression

Gli indici columnstore raggiungono una compressione dei dati 10 volte superiore a quella degli indici rowstore.Columnstore indexes achieve up to 10x greater data compression than rowstore indexes. Ciò riduce significativamente le operazioni I/O richieste per eseguire query di analisi e, di conseguenza, migliora le prestazioni delle query.This greatly reduces the IO required to execute analytics queries and therefore improves query performance.

  • Gli indici columnstore leggono i dati compressi dal disco, quindi il numero di byte che deve essere letto nella memoria risulta ridotto.Columnstore indexes read compressed data from disk, which means fewer bytes of data need to be read into memory.

  • Gli indici columnstore archiviano i dati in formato compresso nella memoria. In questo modo diminuisce il numero di volte in cui gli stessi dati vengono letti in memoria e si riducono di conseguenza le operazioni I/O.Columnstore indexes store data in compressed form in memory which reduces IO by reducing the number of times the same data is read into memory. Ad esempio, con una compressione di 10 volte superiore, gli indici columnstore possono mantenere in memoria volumi di dati 10 volte maggiori di quelli archiviati in formato non compresso.For example, with 10x compression, columnstore indexes can keep 10x more data in memory compared to storing the data in uncompressed form. La presenza di più dati nella memoria incrementa la probabilità che l'indice columnstore trovi i dati necessari nella memoria, senza ulteriori letture dal disco.With more data in memory, it is more likely that the columnstore index will find the data it needs in memory with incurring additional reads from disk.

  • Gli indici columnstore comprimono i dati per colonne anziché per righe. Questo consente di ottenere percentuali di compressione maggiori e di ridurre le dimensioni dei dati archiviati su disco.Columnstore indexes compress data by columns instead of by rows which achieves high compression rates and reduces the size of the data stored on disk. Ogni colonna viene compressa e archiviata in modo indipendente.Each column is compressed and stored independently. Tutti i dati all'interno di una colonna sono sempre dello tipo e tendono ad avere valori simili.Data within a column always has the same data type and tends to have similar values. Le tecniche di compressione dei dati sono molto efficaci per raggiungere percentuali di compressione maggiori quando i valori sono simili.Data compression techniques are very good at achieving higher compression rates when values are similar.

  • Ad esempio, se una tabella dei fatti archivia gli indirizzi dei clienti e include una colonna per il paese, il numero totale di valori possibili è inferiore a 200.For example, if a fact table stores customer addresses and has a column for country, the total number of possible values is fewer than 200. Alcuni di questi valori saranno ripetuti più volte.Some of those values will be repeated many times. Se la tabella dei fatti contiene 100 milioni di righe, la colonna per il paese verrà compressa facilmente e non richiederà molto spazio di archiviazione.If the fact table has 100 million rows, the country column will compress easily and require very little storage. La compressione riga per riga non riesce a sfruttare appieno la somiglianza dei valori di colonna e userà più byte per comprimere i valori nella colonna per il paese.Row-by-row compression is not able to capitalize on the similarity of column values in this way and will use more bytes to compress the values in the country column.

Eliminazione di colonneColumn elimination

Gli indici columnstore non leggono le colonne non rilevanti ai fini della query.Columnstore indexes skip reading in columns that are not required for the query result. Questa capacità, denominata eliminazione di colonne, riduce ulteriormente le operazioni I/O per l'esecuzione delle query e, di conseguenza, migliora le prestazioni delle query.This ability, called column elimination, further reduces IO for query execution and therefore improves query performance.

  • L'eliminazione di colonne è possibile perché i dati sono organizzati e compressi colonna per colonna.Column elimination is possible because the data is organized and compressed column by column. Al contrario, quando i dati sono archiviati riga per riga, i valori della colonna in ogni riga vengono archiviati fisicamente insieme e non possono essere separati facilmente.In contrast, when data is stored row-by-row, the column values in each row are physically stored together and cannot be easily separated. Query Processor deve leggere un'intera riga per recuperare specifici valori della colonna, aumentando così le operazioni I/O a causa delle letture superflue dei dati aggiuntivi in memoria.The query processor needs to read in an entire row to retrieve specific column values, which increases IO because extra data is unnecessarily read into memory.

  • Ad esempio, se una tabella contiene 50 colonne e la query ne usa solo 5, l'indice columnstore recupera solo le 5 colonne rilevanti dal disco.For example, if a table has 50 columns and the query only uses 5 of those columns, the columnstore index only fetches the 5 columns from disk. Non legge i dati delle altre 45 colonne.It skips reading in the other 45 columns. In questo modo, le operazioni I/O vengono ridotte di un altro 90%, presupponendo che tutte le colonne abbiano dimensioni simili.This reduces IO by another 90% assuming all columns are of similar size. Se gli stessi dati vengono archiviati in un rowstore, Query Processor deve leggere le altre 45 colonne.If the same data are stored in a rowstore, the query processor needs to read the additional 45 columns.

Eliminazione di rowgroupRowgroup elimination

Per le scansioni di tabelle complete, un'alta percentuale di dati spesso non corrisponde ai criteri del predicato della query.For full table scans, a large percentage of the data usually does not match the query predicate criteria. Usando i metadati, l'indice columnstore può ignorare la lettura nei rowgroup che non contengono dati necessari per il risultato della query, senza eseguire operazioni I/O.By using metadata, the the columnstore index is able to skip reading in the rowgroups that do not contain data required for the query result, all without actual IO. Questa capacità, denominata eliminazione di rowgroup, riduce le operazioni I/O per le scansioni di tabelle complete e, di conseguenza, migliora le prestazioni delle query.This ability, called rowgroup elimination, reduces IO for full table scans and therefore improves query performance.

Quando è necessario che un indice columnstore esegua una scansione di tabella completa?When does a columnstore index need to perform a full table scan?

A partire da SQL Server 2016, è possibile creare uno o più indici BTree non cluster normali in un indice columnstore cluster con una procedura analoga a quella per un heap rowstore.Starting with SQL Server 2016, you can create one or more regular nonclustered btree indexes on a clustered columnstore index just like you can on a rowstore heap. Gli indici BTree non cluster possono velocizzare una query con un predicato di uguaglianza o un predicato con un intervallo di valori limitato.The nonclustered btree indexes can speed up a query that has an equality predicate or a predicate with a small range of values. Per i predicati più complessi, Query Optimizer potrebbe optare per una scansione di tabella completa.For more complicated predicates, the query optimizer might choose a full table scan. Senza la possibilità di ignorare i rowgroup, una scansione di tabella completa richiederebbe molto tempo, in particolare per tabelle di grandi dimensioni.Without the ability to skip rowgroups, a full table scan would be very time-consuming, especially for large tables.

In che occasioni una query di analisi trae vantaggio dall'eliminazione di rowgroup per una scansione di tabella completa?When does an analytics query benefit from rowgroup elimination for a full-table scan?

Ad esempio, un'azienda di vendita al dettaglio ha modellato i dati di vendita usando una tabella dei fatti con un indice columnstore cluster.For example, a retail business has modelled their sales data using a fact table with clustered columnstore index. Per ogni nuova vendita vengono archiviati diversi attributi della transazione inclusa la data della vendita.Each new sale stores various attributes of the transaction including the date is was sold. È interessante notare che, anche se gli indici columnstore non garantiscono un ordinamento, le righe in questa tabella vengono caricate ordinate per data.Interestingly, even though columnstore indexes do not guarantee a sorted order, the rows in this table will loaded in a date-sorted order. Questa tabella si espande nel tempo.Over time this table will grow. Anche se l'azienda di vendita al dettaglio conserva i dati di vendita degli ultimi 10 anni, per una query di analisi potrebbe essere necessario solo il calcolo di un'aggregazione dell'ultimo trimestre.Although the retail business might keep sales data for the last 10 years, an analytics query might only need to compute an aggregate for last quarter. Gli indici columnstore possono eliminare l'accesso ai dati dei 39 trimestri precedenti semplicemente esaminando i metadati per la colonna della data.Columnstore indexes can eliminate accessing the data for the previous 39 quarters by just looking at the metadata for the date column. Si tratta di un'ulteriore riduzione del 97% della quantità di dati letti in memoria ed elaborati.This is an additional 97% reduction in the amount of data that is read into memory and processed.

Quali rowgroup vengono ignorati in una scansione di tabella completa?Which rowgroups are skipped in a full table scan?

Per determinare quali rowgroup eliminare, l'indice columnstore usa i metadati per archiviare i valori minimi e massimi di ogni segmento di colonna per ogni rowgroup.To determine which rows groups to eliminate, the columnstore index uses metadata to store the minimum and maximum values of each column segment for each rowgroup. Se nessuno degli intervalli dei segmenti di colonna soddisfa i criteri del predicato della query, l'intero rowgroup viene ignorato senza eseguire alcuna operazione I/O.When none of the column segment ranges meet the query predicate criteria, the entire rowgroup is skipped without doing any actual IO. Questo procedimento funziona perché in genere i dati vengono caricati con un ordinamento e, anche se l'ordinamento delle righe non è sempre garantito, i valori dei dati simili spesso si trovano all'interno dello stesso rowgroup o in un rowgroup adiacente.This works because the data is usually loaded in a sorted order and although rows are not guaranteed to be sorted, similar data values are often located within the same rowgroup or a neighboring rowgroup.

Per altri dettagli sui rowgroup, vedere Guida agli indici columnstoreFor more details about rowgroups, see Columnstore Indexes Guide

Esecuzione in modalità batchBatch Mode Execution

L'esecuzione in modalità batch indica l'elaborazione congiunta di un set di righe, generalmente non più di 900, per migliorare l'efficienza di esecuzione.Batch mode execution refers to processing a set of rows, typically up to 900 rows, together for execution efficiency. Ad esempio, la query Select SUM (Sales)from SalesData aggrega le vendite totali della tabella SalesData.For example, the query Select SUM (Sales)from SalesData aggregates the total sales from the table SalesData. Nell'esecuzione in modalità batch, il motore di esecuzione delle query calcola l'aggregato in gruppi di 900 valori.In batch mode execution, the query execution engine computes the aggregate in group of 900 values. In questo modo, invece di pagare il costo delle singole righe, i metadati, i costi di accesso e altri tipi di costi generali vengono suddivisi su tutte le righe in un batch, riducendo notevolmente il percorso del codice.This spreads metadata the access costs and other types of overhead over all the rows in a batch, rather than paying the cost for each row thereby significantly reducing the code path. L'elaborazione in modalità batch funziona sui dati compressi, quando disponibili, ed elimina alcuni degli operatori di scambio usati dall'elaborazione in modalità riga.Batch mode processing operates on compressed data when possible and eliminates some of the exchange operators used by row mode processing. Questo velocizza l'esecuzione delle query di analisi per ordini di grandezza.This speeds up execution of analytics queries by orders of magnitude.

Non tutti gli operatori di esecuzione delle query possono essere eseguiti in modalità batch.Not all query execution operators can be executed in batch mode. Ad esempio, le operazioni DML di inserimento, eliminazione o aggiornamento vengono eseguite una riga alla volta.For example, DML operations such as Insert, Delete or Update are executed row at a time. Gli operatori in modalità batch fanno riferimento agli operatori per velocizzare le prestazioni delle query in operazioni di analisi, join, aggregazione, ordinamento e così via.Batch mode operators target operators for speeding up query performance such as Scan, Join, Aggregate, sort and so on. Poiché l'indice columnstore è stato introdotto in SQL Server 2012, si sta lavorando costantemente per aumentare gli operatori che possono essere eseguiti in modalità batch.Since the columnstore index was introduced in SQL Server 2012, there is a sustained effort to expand the operators that can be executed int the batch mode. La tabella seguente illustra gli operatori eseguiti in modalità batch in base alla versione del prodotto.The table below shows the operators that run in batch mode according to the product version.

Operatori in modalità batchBatch Mode Operators Quando si usa?When is this used? SQL Server 2012SQL Server 2012 SQL Server 2014SQL Server 2014 SQL Server 2016 e database SQL¹SQL Server 2016 and SQL Database¹ CommentiComments
Operazioni DML (insert, delete, update, merge)DML operations (insert, delete, update, merge) nono nono nono DML non è un'operazione in modalità batch perché non è parallela.DML is not a batch mode operation because it is not parallel. Anche quando si abilita l'elaborazione batch in modalità seriale e si consente l'elaborazione in modalità batch di DML, non si rilevano vantaggi significativi.Even when we enable serial mode batch processing, we don't see significant gains by allowing DML to be processed in batch mode.
Index Scan columnstorecolumnstore index scan SCANSCAN NDNA yes yes Per gli indici columnstore è possibile eseguire il push del predicato nel nodo SCAN.For columnstore indexes, we can push the predicate to the SCAN node.
Index Scan (nonclustered) columnstorecolumnstore Index Scan (nonclustered) SCANSCAN yes yes yes yes
Index Seekindex seek NDNA NDNA nono L'operazione di ricerca viene eseguita con un indice BTree non cluster in modalità riga.We perform a seek operation through a nonclustered btree index in rowmode.
Compute Scalarcompute scalar Espressione che restituisce un valore scalare.Expression that evaluates to a scalar value. yes yes yes Esistono alcune restrizioni sul tipo di dati.There are some restrictions on data type. Questo vale per tutti gli operatori in modalità batch.This is true for all batch mode operators.
Concatenationconcatenation UNION e UNION ALLUNION and UNION ALL nono yes yes
filterfilter Applicare i predicatiApplying predicates yes yes yes
Hash Matchhash match Funzioni di aggregazione basate su hash, outer hash join, right hash join, left hash join, right inner join, left inner joinHash-based aggregate functions, outer hash join, right hash join, left hash join, right inner join, left inner join yes yes yes Restrizioni per l'aggregazione: nessun valore min e max per le stringhe.Restrictions for aggregation: no min/max for strings. Le funzioni di aggregazione disponibili sono sum/count/avg/min/max.Aggregation functions available are sum/count/avg/min/max.
Restrizioni per il join: nessun join con tipo non corrispondente per i tipi non integer.Restrictions for join: no mismatched type joins on non-integer types.
Merge Joinmerge join nono nono nono
Query multithreadingmulti-threaded queries yes yes yes
Nested Loopsnested loops nono nono nono
Query a thread singolo in esecuzione in MAXDOP 1single-threaded queries running under MAXDOP 1 nono nono yes
Query a thread singolo con un piano di query serialesingle-threaded queries with a serial query plan nono nono yes
Sortsort Ordinare per clausola in SCAN con l'indice columnstore.Order by clause on SCAN with columnstore index. nono nono yes
Top Sorttop sort nono nono yes
Window Aggregateswindow aggregates NDNA NDNA yes Nuovo operatore in SQL Server 2016.New operator in SQL Server 2016.

¹Si applica a SQL Server 2016, database SQL V12 Premium Edition e SQL Data Warehouse¹Applies to SQL Server 2016, SQL Database V12 Premium Edition, and SQL Data Warehouse

Distribuzione dell'aggregazioneAggregate Pushdown

Un percorso di esecuzione normale per il calcolo di aggregazione che consente di recuperare le righe idonee dal nodo SCAN e aggregare i valori in modalità batch.A normal execution path for aggregate computation to fetch the qualifying rows from the SCAN node and aggregate the values in Batch Mode. Questo metodo offre buone prestazioni, ma con SQL Server 2016 è possibile eseguire il push dell'operazione di aggregazione nel nodo SCAN per migliorare le prestazioni di calcolo di aggregazione per ordini di grandezza durante l'esecuzione in modalità batch, purché vengano soddisfatte le condizioni seguentiWhile this delivers good performance, but with SQL Server 2016, the aggregate operation can be pushed to the SCAN node to improve the performance of aggregate computation by orders of magnitude on top of Batch Mode execution provided the following conditions are met

  • Le aggregazioni sono MIN, MAX, SUM, COUNT e COUNT().The aggregates are MIN, MAX, SUM, COUNT and COUNT().
  • L'operatore di aggregazione deve essere sopra il nodo SCAN o il nodo SCAN con GROUP BY.Aggregate operator must be on top of SCAN node or SCAN node with group by.
  • L'aggregazione non è un'aggregazione distinta.This aggregate is not a distinct aggregate.
  • La colonna di aggregazione non è una colonna stringa.The aggregate column is not a string column.
  • La colonna di aggregazione non è una colonna virtuale.The aggregate column is not a virtual column.
  • Il tipo di dati di input e di output deve essere uno dei seguenti e deve rientrare nei 64 bit:The input and output datatype must be one of the following and must fit within 64 bits.

    • tiny int, int, big int, small int, bitTiny int, int, big int, small int, bit
    • small money, money, decimale e numerico con precisione <= 18Small money, money, decimal and numeric which has precision <= 18
    • small date, date, datetime, datetime2, timeSmall date, date, datetime, datetime2, time

    La distribuzione dell'aggregazione viene ulteriormente accelerata da un'aggregazione efficiente dei dati compressi/codificati durante un'esecuzione di facile integrazione con la cache e sfruttando SIMDAggregate push down is further accelerated by efficient Aggregation on compressed/encoded data in cache-friendly execution and by leveraging SIMD

    aggregate pushdownaggregate pushdown

    Ad esempio, la distribuzione dell'aggregazione viene eseguita in entrambe le query seguentiFor example, aggregate pushdown is done in both of the queries below


SELECT  productkey, SUM(TotalProductCost)    
FROM FactResellerSalesXL_CCI    
GROUP BY productkey    

SELECT  SUM(TotalProductCost)    
FROM FactResellerSalesXL_CCI    

Distribuzione del predicato stringaString predicate pushdown

Motivazione: quando si progetta uno schema del data warehouse, la modellazione dello schema consigliata consiste nell'usare uno schema star o snowflake costituito da una o più tabelle dei fatti e da più tabelle delle dimensioni.Motivation: When designing a data warehouse schema, the recommended schema modeling is to use star-schema or snowflake schema consisting of one or more fact tables and many dimension tables. La tabella dei fatti archivia le misure o le transazioni aziendali e la tabella delle dimensioni archivia le dimensioni di cui analizzare i fatti.The fact table stores the business measurements or transactions and dimension table store the dimensions across which facts need to be analyzed.

Ad esempio, un fatto può essere un record che rappresenta la vendita di un certo prodotto in un'area specifica, mentre la dimensione rappresenta un set di regioni, prodotti e così via.For example, a fact can be a record representing a sale of a particular product in a specific region while the dimension represents a set of regions, products and so on. Le tabelle dei fatti e delle dimensioni sono connesse con una relazione di chiave primaria/esterna.The fact and dimension tables are connected through the a primary/foreign key relationship. Le query di analisi più diffuse creano un join di una o più tabelle delle dimensioni con la tabella dei fatti.Most commonly used analytics queries join one or more dimension tables with the fact table.

Si prendano in considerazione i prodotti di una tabella delle dimensioni.Let us consider a dimension table products. Una chiave primaria tipica è ProductCode, generalmente rappresentata con un tipo di dati stringa.a typical primary key will be productcode which is commonly represented as string data type. Per le prestazioni delle query, è consigliabile creare una chiave surrogata, in genere una colonna di tipo integer, per fare riferimento alla riga nella tabella delle dimensioni dalla tabella dei fatti.For performance of queries, it is a best practice to create surrogate key, typically an integer column, to refer to the row in the dimension table from the fact table.

L'indice columnstore esegue in modo efficace le query di analisi con join/predicati che includono chiavi di tipo numerico o integer.The columnstore index runs analytics queries with joins/predicates involving numeric or integer based keys very efficiently. Tuttavia, in molti carichi di lavoro del cliente vengono usate colonne basate su stringhe per il collegamento delle tabelle dei fatti e delle dimensioni e le prestazioni delle query con l'indice columnstore si sono rivelate non altrettanto soddisfacenti.However, in many customer workloads, we find the use to string based columns linking fact/dimension tables and with the result the query performance with columnstore index was not as performing. SQL Server 2016 migliora in modo significativo le prestazioni delle query di analisi con le colonne basate su stringhe grazie alla distribuzione dei predicati con colonne di tipo stringa nel nodo SCANSQL Server 2016 improves the performance of analytics queries with string based columns significantly by pushing down the predicates with string columns to the SCAN node

La distribuzione del predicato stringa sfrutta il dizionario primario o secondario creato per le colonne per migliorare le prestazioni delle query.String predicate pushdown leverages the primary/secondary dictionary created for column(s) to improve the query performance. Ad esempio, prendere in considerazione il segmento di colonna stringa all'interno di un rowgroup costituito da 100 valori stringa distinti.For example, let us consider string column segment within a rowgroup consisting of 100 distinct string values. Ipotizzando la presenza di un milione di righe, la media di riferimenti a ogni singolo valore stringa è di 10.000 volte.This means each distinct string value is referenced 10,000 times on average assuming 1 million rows .

Con la distribuzione del predicato stringa, l'esecuzione della query calcola il predicato in base ai valori nel dizionario e, se è qualificato, tutte le righe che fanno riferimento al valore del dizionario risultano automaticamente qualificate.With string predicate pushdown, the query execution computes the predicate against the values in the dictionary and if it qualifies, all rows referring to the dictionary value are automatically qualified. Ciò migliora le prestazioni in due modi.This improves the performance in two ways. In primo luogo, vengono restituite solo le righe qualificate, riducendo il numero di righe da trasmettere dal nodo SCAN.First, only the qualified row are returned reducing number of the rows that need to flow out of SCAN node. In secondo luogo, il numero dei confronti di stringhe viene notevolmente ridotto.Second, the number of string comparisons are significantly reduced. In questo esempio sono necessari solo 100 confronti di stringhe invece di un milione.In this example, only 100 string comparisons are required as against 1 million comparisons. Esistono alcune limitazioni, come descritto di seguitoThere are some limitations as described below

  • Non è consentita la distribuzione del predicato stringa per i rowgroup delta.No string predicate pushdown for delta rowgroups. Non esiste un dizionario per le colonne nei rowgroup deltaThere is no dictionary for columns in delta rowgroups

  • Non è consentita la distribuzione del predicato stringa se il dizionario supera le 64.000 vociNo string predicate pushdown if dictionary exceeds 64k entries

  • Le espressioni che restituiscono NULL non sono supportateExpression evaluating NULLs are not not supported

Vedere ancheSee Also

Guida agli indici columnstoreColumnstore Indexes Guide
Caricamento dati di indici columnstoreColumnstore Indexes Data Loading
Riepilogo delle funzionalità con versione degli indici columnstoreColumnstore Indexes Versioned Feature Summary
Prestazioni delle query per gli indici columnstore Columnstore Indexes Query Performance
Introduzione a columnstore per l'analisi operativa in tempo reale Get started with Columnstore for real time operational analytics
Indici columnstore per il data warehousingColumnstore Indexes for Data Warehousing
Deframmentazione degli indici columnstoreColumnstore Indexes Defragmentation