Indici columnstore - Prestazioni delle queryColumnstore indexes - Query performance

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

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 consentono di ottenere le elevate prestazioni delle query per le quali sono progettati gli indici columnstore.These recommendations 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 elencate alcune indicazioni per ottenere prestazioni elevate con gli 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 (13.x)SQL Server 2016 (13.x) diversi rowgroup vengono ignorati durante l'elaborazione della query.In SQL Server 2016 (13.x)SQL Server 2016 (13.x), 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) 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 columnstore index by dropping the rowstore clustered index. Se si crea l'indice columnstore cluster in modo esplicito usando MAXDOP = 1, l'indice risultante verrà ordinato perfettamente in base alla colonna C1.if you create the clustered columnstore index explicitly using MAXDOP = 1, the resulting clustered columnstore index is perfectly ordered on column C1. Se si specifica MAXDOP = 8 si avrà una sovrapposizione di valori in otto rowgroup.If you specify MAXDOP = 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 quindi 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. Se ad esempio 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. Se ad esempio la tabella contiene meno di un milione di righe, SQL ServerSQL Server usa un solo thread per creare l'indice columnstore.For example, if your table has fewer than one million rows, SQL ServerSQL Server will use only one thread to create the columnstore index.

Se la tabella dispone di più di un milione di righe, ma SQL ServerSQL Server non dispone di memoria sufficiente per creare l'indice usando MAXDOP, SQL ServerSQL Server riduce automaticamente MAXDOP in base alle esigenze per adattarsi alla memoria disponibile.If your table has more than one million rows, but SQL ServerSQL Server cannot get a large enough memory grant to create the index using MAXDOP, SQL ServerSQL 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 (13.x)SQL Server 2016 (13.x) la query viene eseguita sempre in modalità batch.Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), 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 ottengono prestazioni di query ottimali combinando l'elaborazione in memoria in modalità batch ad alta velocità con tecniche che riducono significativamente i requisiti I/O.Columnstore indexes achieve high query performance by combining high-speed in-memory batch mode processing with techniques that greatly reduce I/O 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 I/O 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 all'elevata compressione dei dati, all'eliminazione di columnstore, all'eliminazione di rowgroup e all'elaborazione batch.Columnstore indexes reduce I/O 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 I/O 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 memoria in formato compresso. La conseguente riduzione del numero di letture in memoria degli stessi dati riduce a sua volta le operazioni I/O.Columnstore indexes store data in compressed form in memory which reduces I/O by reducing the number of times the same data is read into memory. Ad esempio, con una compressione 10x, 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.

  • Se ad esempio 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 pertanto migliora le prestazioni delle query.This ability, called column elimination, further reduces I/O 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 valori di colonna specifici, aumentando le operazioni I/O con letture in memoria non necessarie di dati superflui.The query processor needs to read in an entire row to retrieve specific column values, which increases I/O 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 presupponendo che tutte le colonne abbiano dimensioni simili, le operazioni I/O vengono ridotte di un ulteriore 90%.This reduces I/O 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 e quindi evitare di eseguire le operazioni I/O corrispondenti.By using metadata, the columnstore index is able to skip reading in the rowgroups that do not contain data required for the query result, all without actual I/O. Questa capacità, denominata eliminazione di rowgroup, riduce le operazioni I/O per le scansioni di tabella complete e di conseguenza migliora le prestazioni delle query.This ability, called rowgroup elimination, reduces I/O 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 (13.x)SQL Server 2016 (13.x) è possibile creare uno o più indici albero B non cluster normali in un indice columnstore cluster con una procedura analoga a quella per un heap rowstore.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can create one or more regular nonclustered B-tree indexes on a clustered columnstore index just like you can on a rowstore heap. Gli indici albero B non cluster possono velocizzare una query con un predicato di uguaglianza o un predicato con un intervallo di valori limitato.The nonclustered B-tree 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 modeled 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 di un articolo.Each new sale stores various attributes of the transaction including the date a product was sold. È interessante notare che anche se gli indici columnstore non garantiscono l'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 be 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. Dall'introduzione dell'indice columnstore in SQL Server 2012 (11.x)SQL Server 2012 (11.x) si sta lavorando costantemente all'incremento degli operatori eseguibili in modalità batch.Since the columnstore index was introduced in SQL Server 2012 (11.x)SQL Server 2012 (11.x), there is a sustained effort to expand the operators that can be executed int the batch mode. La tabella seguente visualizza gli operatori eseguibili 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 2012 (11.x)SQL Server 2012 (11.x) SQL Server 2014 (12.x)SQL Server 2014 (12.x) SQL Server 2016 (13.x)SQL Server 2016 (13.x) e Database SQLSQL Database¹ and Database SQLSQL 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 Si esegue un'operazione di ricerca con un indice albero B non cluster in modalità riga.We perform a seek operation through a nonclustered B-tree 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 (13.x)SQL Server 2016 (13.x).New operator in SQL Server 2016 (13.x)SQL Server 2016 (13.x).

¹Si applica a SQL Server 2016 (13.x)SQL Server 2016 (13.x), ai livelli Standard e Premium del Database SQLSQL Database, S3 e successive, a tutti i livelli vCore e a Parallel Data WarehouseParallel Data Warehouse¹Applies to SQL Server 2016 (13.x)SQL Server 2016 (13.x), Database SQLSQL Database Premium tiers, Standard tiers - S3 and above, and all vCore tiers, and Parallel Data WarehouseParallel 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 (13.x)SQL Server 2016 (13.x) è 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 seguenti:While this delivers good performance, but with SQL Server 2016 (13.x)SQL Server 2016 (13.x), 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 funzioni di aggregazione 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.

    • tinyint, int, bigint, smallint, bittinyint, int, bigint, smallint, bit
    • smallmoney, money, decimal e numeric con precisione <= 18smallmoney, money, decimal and numeric with precision <= 18
    • smalldate, date, datetime, datetime2, timesmalldate, 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 seguenti:For 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

Quando si progetta uno schema del data warehouse, la modellazione consigliata è l'uso di uno schema star o snowflake costituito da una o più tabelle dei fatti e da molte tabelle delle dimensioni.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 da una relazione di tipo chiave primaria/chiave esterna.The fact and dimension tables are connected through 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.

Ad esempio considerare il caso di una tabella delle dimensioni Products.Let us consider a dimension table Products. Una chiave primaria tipica è ProductCode, generalmente rappresentata con il tipo di dati stringa.A typical primary key will be ProductCode which is commonly represented as string data type. Una procedura consigliata per il miglioramento delle prestazioni delle query è la creazione di una chiave surrogata, in genere una colonna di tipo integer, per fare riferimento alla riga della 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 non risultano altrettanto efficaci.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 (13.x)SQL Server 2016 (13.x) 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 SCAN. 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 si basa sul 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, il valore medio dei riferimenti a ogni singolo valore stringa è pari a 10.000.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. Questo migliora le prestazioni in due modi:This improves the performance in two ways:

  1. Vengono restituite solo le righe qualificate, riducendo il numero di righe da trasmettere dal nodo SCAN.Only the qualified row is returned reducing number of the rows that need to flow out of SCAN node.
  2. Il numero dei confronti di stringhe viene notevolmente ridotto.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 seguito:There 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 delta.There is no dictionary for columns in delta rowgroups.
    • Non è consentita la distribuzione del predicato stringa se le voci del dizionario superano i 64 KB.No string predicate pushdown if dictionary exceeds 64 KB entries.
    • Le espressioni che restituiscono NULL non sono supportate.Expression evaluating NULLs are not supported.

Vedere ancheSee Also

Indici columnstore - Linee guida per la progettazione Columnstore Indexes Design Guidance
Indici columnstore - Linee guida per il caricamento di dati Columnstore Indexes Data Loading Guidance
Introduzione a columnstore per l'analisi operativa in tempo reale Get started with Columnstore for real time operational analytics
Indici columnstore per il data warehousing Columnstore Indexes for Data Warehousing
Deframmentazione degli indici columnstore Columnstore Indexes Defragmentation
Architettura degli indici columnstore Columnstore Index Architecture
CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL)ALTER INDEX (Transact-SQL)