Rendimiento de las consultas de índices de almacén de columnasColumnstore indexes - Query performance

SE APLICA A: síSQL Server síAzure SQL Database síAzure Synapse Analytics (SQL DW) síAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Recomendaciones para lograr el rendimiento de las consultas muy rápido que se espera que proporcionen los índices de almacén de columnas.Recommendations for achieving the very fast query performance that columnstore indexes are designed to provide.

Los índices de almacén de columnas pueden lograr un rendimiento hasta 100 veces mayor en las cargas de almacenamiento de datos y análisis y hasta 10 veces mejor en la compresión de datos que los índices de almacén de filas tradicionales.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. Estas recomendaciones ayudan a que las consultas consigan el rendimiento de las consultas muy rápido que se espera que proporcionen los índices de almacén de columnas.These recommendations help your queries achieve the very fast query performance that columnstore indexes are designed to provide. Al final hay más explicaciones sobre el rendimiento del almacén de columnas.Further explanations about columnstore performance are at the end.

Recomendaciones para mejorar el rendimiento de las consultasRecommendations for improving query performance

Aquí se proporcionan algunas recomendaciones para lograr el rendimiento alto que se espera que proporcionen los índices de almacén de columnas.Here are some recommendations for achieving the high-performance columnstore indexes are designed to provide.

1. Organización de los datos para eliminar más grupos de filas de un recorrido de tabla completo1. Organize data to eliminate more rowgroups from a full table scan

  • Aproveche el orden de inserción.Leverage insert order. Normalmente, en el almacén de datos tradicional, los datos se insertan realmente en orden cronológico y el análisis se realiza en la dimensión de tiempo.In common case in traditional data warehouse, the data is indeed inserted in time order and analytics is done in time dimension. Por ejemplo, en los análisis de ventas por trimestre.For example, analyzing sales by quarter. Para este tipo de carga de trabajo, se produce la eliminación del grupo de filas automáticamente.For this kind of workload, the rowgroup elimination happens automatically. En SQL Server 2016 (13.x)SQL Server 2016 (13.x), puede encontrar que se ha omitido una serie de grupos de filas como parte del procesamiento de consulta.In SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can find out number rowgroups skipped as part of query processing.

  • Aproveche el índice agrupado del almacén de filas.Leverage the rowstore clustered index. Si el predicado de consulta común está en una columna (por ejemplo, C1) que no está relacionada con el orden de inserción de la fila, puede crear un índice agrupado de almacenamiento de filas en la columna C1 y, después, crear un índice agrupado de almacén de columnas al quitar el índice agrupado de almacenamiento de filas.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. Si crea el índice de almacén de columnas agrupado explícitamente con MAXDOP = 1, el índice de almacén de columnas agrupado se ordena perfectamente en la columna C1.if you create the clustered columnstore index explicitly using MAXDOP = 1, the resulting clustered columnstore index is perfectly ordered on column C1. Si especifica MAXDOP = 8, verá la superposición de los valores en los ocho grupos de filas.If you specify MAXDOP = 8, then you will see overlap of values across 8 rowgroups. Un caso común de esta estrategia es cuando crea inicialmente el índice de almacén de columnas con un conjunto grande de datos.A common case of this strategy when you initially create columnstore index with large set of data. Tenga en cuenta que, para el índice de almacén de columnas no agrupado (NCCI), si la tabla de base del almacén de filas tiene un índice agrupado, las filas ya están ordenadas.Note, for nonclustered columnstore index (NCCI), if the base rowstore table has a clustered index, the rows are already ordered. En este caso, el índice de almacén de columnas no agrupado resultante se ordenará automáticamente.In this case, the resultant nonclustered columnstore index will automatically be ordered. Un punto importante a tener en cuenta es que ese índice de almacén de columnas no mantiene de manera inherente el orden de las filas.One important point to note is that columnstore index does not inherently maintain the order of rows. Como se insertan filas nuevas o se actualizan las filas más antiguas, deberá repetir el proceso, ya que el rendimiento de la consulta de análisis puede deteriorarse.As new rows are inserted or older rows are updated, you may need to repeat the process as the analytics query performance may deteriorate

  • aprovechamiento de la partición de tablas.Leverage table partitioning. Puede dividir el índice de almacén de columnas y, después, usar la eliminación de particiones para reducir el número de grupos de filas que se van a analizar.You can partition the columnstore index and then use partition elimination to reduce number of rowgroups to scan. Por ejemplo, una tabla de hechos almacena las compras realizadas por los clientes y un patrón de consulta común va a encontrar las compras trimestrales realizadas por un cliente específico. Para esto, puede combinar el orden de inserción con la partición en la columna 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. Cada partición contendrá filas en orden cronológico para el cliente específico.Each partition will contain rows in time order for specific customer.

2. Planear para que haya suficiente memoria para crear índices de almacén de columnas en paralelo2. Plan for enough memory to create columnstore indexes in parallel

La creación un índice de almacén de columnas es de forma predeterminada una operación paralela a menos que se restrinja la memoria.Creating a columnstore index is by default a parallel operation unless memory is constrained. Crear el índice en paralelo requiere más memoria que crear el índice en serie.Creating the index in parallel requires more memory than creating the index serially. Cuando hay suficiente memoria, la creación de un índice de almacén de columnas tarda aproximadamente 1,5 más que generar un árbol B en las mismas columnas.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 necesaria para crear un índice de almacén de columnas depende del número de columnas, el número de columnas de cadena, el grado de paralelismo (DOP) y las características de los datos.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. Por ejemplo, si la tabla tiene menos de un millón de filas, SQL ServerSQL Server solo usará un subproceso para crear el índice de almacén de columnas.For example, if your table has fewer than one million rows, SQL ServerSQL Server will use only one thread to create the columnstore index.

Si la tabla tiene más de un millón de filas pero SQL ServerSQL Server no puede obtener una concesión de memoria suficiente para crear el índice mediante MAXDOP, SQL ServerSQL Server reducirá automáticamente el valor de MAXDOP según sea necesario para ajustarse a la concesión de memoria disponible.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. En algunos casos, se debe reducir a uno el DOP para generar el índice cuando la memoria está restringida.In some cases, DOP must be decreased to one in order to build the index under constrained memory.

A partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x), la consulta siempre funcionará en modo por lotes.Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the query will always operate in batch mode. En versiones anteriores, la ejecución por lotes solo se utiliza cuando DOP es mayor que uno.In previous releases, batch execution is only used when DOP is greater than one.

Explicación del rendimiento del almacén de columnasColumnstore Performance Explained

Los índices de almacén de columnas logran un rendimiento de consulta elevado mediante la combinación del procesamiento en modo por lotes en memoria de alta velocidad con técnicas que reducen en gran medida los requisitos de E/S.Columnstore indexes achieve high query performance by combining high-speed in-memory batch mode processing with techniques that greatly reduce I/O requirements. Puesto que las consultas de análisis examinan grandes cantidades de filas, tienen normalmente un enlace de optimización de la infraestructura y, por tanto, la reducción de la E/S durante la ejecución de la consulta es fundamental para el diseño de los índices de almacén de columnas.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. Una vez que se han leído los datos en la memoria, es fundamental reducir el número de operaciones en memoria.Once data has been read into memory, it is critical to reduce the number of in-memory operations.

Los índices de almacén de columnas reducen la E/S y optimizan las operaciones en memoria a través de una alta compresión de datos, la eliminación del almacén de columnas, la eliminación del grupo de filas y el procesamiento por lotes.Columnstore indexes reduce I/O and optimize in-memory operations through high data compression, columnstore elimination, rowgroup elimination, and batch processing.

Compresión de datosData compression

Los índices de almacén de columnas logran una compresión de datos hasta 10 veces mayor que los índices de almacén de filas.Columnstore indexes achieve up to 10x greater data compression than rowstore indexes. Esto reduce en gran medida la E/S necesaria para ejecutar las consultas de análisis y, por tanto, mejora el rendimiento de las consultas.This greatly reduces the I/O required to execute analytics queries and therefore improves query performance.

  • Los índices de almacén de columnas leen los datos comprimidos del disco, lo que significa que deben leerse menos bytes de datos en la memoria.Columnstore indexes read compressed data from disk, which means fewer bytes of data need to be read into memory.

  • Los índices de almacén de columnas almacenan datos en un formato comprimido en la memoria, lo que reduce la E/S con la disminución del número de veces que se leen los mismos datos en memoria.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. Por ejemplo, con la compresión 10 veces mayor, los índices de almacén de columnas pueden mantener 10 veces más datos en memoria en comparación con el almacenamiento de datos en formato sin comprimir.For example, with 10x compression, columnstore indexes can keep 10x more data in memory compared to storing the data in uncompressed form. Con más datos en memoria, es más probable que el índice de almacén de columnas busque los datos que necesita en la memoria sin generar lecturas adicionales del disco.With more data in memory, it is more likely that the columnstore index will find the data it needs in memory without incurring additional reads from disk.

  • Los índices de almacén de columnas comprimen los datos por columnas en lugar de por filas, lo que genera altas tasas de compresión y reduce el tamaño de los datos almacenados en 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. Se comprime y almacena cada columna de forma independiente.Each column is compressed and stored independently. Los datos de una columna siempre tienen el mismo tipo y suelen tener valores similares.Data within a column always has the same data type and tends to have similar values. Las técnicas de compresión de datos son muy buenas para lograr las mayores índices de compresión cuando los valores son similares.Data compression techniques are very good at achieving higher compression rates when values are similar.

  • Por ejemplo, si una tabla de hechos almacena direcciones de clientes y tiene una columna de país, el número total de valores posibles es inferior 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. Algunos de esos valores se repetirá muchas veces.Some of those values will be repeated many times. Si la tabla de hechos tiene 100 millones de filas, la columna de país se comprimirá fácilmente y requerirá muy poco almacenamiento.If the fact table has 100 million rows, the country column will compress easily and require very little storage. La compresión de fila por fila no puede aprovechar la similitud de los valores de columna de esta manera y usará más bytes para comprimir los valores de la columna de país.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.

Eliminación de la columnaColumn elimination

Los índices de almacén de columnas omiten la lectura de las columnas que no son necesarias para el resultado de la consulta.Columnstore indexes skip reading in columns that are not required for the query result. Esta capacidad, denominada eliminación de la columna, reduce aún más la E/S para la ejecución de la consulta y, por tanto, mejora el rendimiento de las consultas.This ability, called column elimination, further reduces I/O for query execution and therefore improves query performance.

  • La eliminación de la columna es posible porque los datos se organizan y comprimen columna por columna.Column elimination is possible because the data is organized and compressed column by column. En cambio, cuando los datos se almacenan fila por fila, los valores de columna de cada fila se almacenan físicamente juntos y no se pueden separar fácilmente.In contrast, when data is stored row-by-row, the column values in each row are physically stored together and cannot be easily separated. El procesador de consultas tiene que leerse en una fila completa para recuperar valores de columna específicos, lo que aumenta la E/S porque se leen datos adicionales innecesariamente en la memoria.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.

  • Por ejemplo, si una tabla tiene 50 columnas y la consulta usa solo 5 de esas columnas, el índice de almacén de columnas solo captura las 5 columnas del 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. Omite la lectura en las demás 45 columnas.It skips reading in the other 45 columns. Esto reduce la E/S en otro 90 % suponiendo que todas las columnas son de tamaño similar.This reduces I/O by another 90% assuming all columns are of similar size. Si los mismos datos se almacenan en un almacén de filas, el procesador de consultas necesita leer las 45 columnas adicionales.If the same data are stored in a rowstore, the query processor needs to read the additional 45 columns.

Eliminación del grupo de filasRowgroup elimination

Para el análisis de una tabla completa, un gran porcentaje de los datos no coincide normalmente con los criterios de predicado de consulta.For full table scans, a large percentage of the data usually does not match the query predicate criteria. Con los metadatos, el índice de almacén de columnas puede omitir la lectura de los grupos de filas que no contienen los datos necesarios para el resultado de la consulta, sin necesidad de la E/S real.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. Esta capacidad, denominada eliminación del grupo de filas, reduce la E/S para los análisis de tabla completa y, por tanto, mejora el rendimiento de las consultas.This ability, called rowgroup elimination, reduces I/O for full table scans and therefore improves query performance.

¿Cuándo un índice de almacén de columnas tiene que realizar un análisis de tabla completa?When does a columnstore index need to perform a full table scan?

A partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x), puede crear uno o varios índices no agrupados normales de árbol B en un índice agrupado de almacén de columnas exactamente igual que en un montón de almacén de filas.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. Los índices no agrupados de árbol B pueden acelerar una consulta que tenga un predicado de igualdad o un predicado con un pequeño intervalo de valores.The nonclustered B-tree indexes can speed up a query that has an equality predicate or a predicate with a small range of values. Para predicados más complicados, el optimizador de consultas puede elegir un análisis de tabla completa.For more complicated predicates, the query optimizer might choose a full table scan. Sin la capacidad para omitir los grupos de filas, un análisis de tabla completa sería muy lento, especialmente para las tablas grandes.Without the ability to skip rowgroups, a full table scan would be very time-consuming, especially for large tables.

¿Cuándo se beneficia una consulta de análisis de la eliminación del grupo de filas para un análisis de tabla completa?When does an analytics query benefit from rowgroup elimination for a full-table scan?

Por ejemplo, una empresa minorista ha modelado sus datos de ventas con una tabla de hechos con un índice agrupado de almacén de columnas.For example, a retail business has modeled their sales data using a fact table with clustered columnstore index. Cada nueva venta almacena varios atributos de la transacción, incluida la fecha de venta de un producto.Each new sale stores various attributes of the transaction including the date a product was sold. Curiosamente, aunque los índices de almacén de columnas no garantizan un criterio de ordenación, las filas de la tabla se cargarán ordenadas por fecha.Interestingly, even though columnstore indexes do not guarantee a sorted order, the rows in this table will be loaded in a date-sorted order. Esta tabla crecerá con el tiempo.Over time this table will grow. Aunque la empresa minorista puede conservar los datos de ventas de los últimos 10 años, una consulta de análisis solo necesita calcular un agregado para el último 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. Los índices de almacén de columnas pueden eliminar el acceso a los datos para los 39 trimestres anteriores con solo mirar los metadatos de la columna de fecha.Columnstore indexes can eliminate accessing the data for the previous 39 quarters by just looking at the metadata for the date column. Se trata de una reducción adicional del 97 % en la cantidad de datos que se lee en la memoria y que se procesa.This is an additional 97% reduction in the amount of data that is read into memory and processed.

¿Qué grupos de filas se omiten en un análisis de tabla completa?Which rowgroups are skipped in a full table scan?

Para determinar qué grupos de filas eliminar, el índice de almacén de columnas usa metadatos para almacenar los valores mínimos y máximos de cada segmento de columna para cada grupo de filas.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. Cuando ninguno de los intervalos del segmento de columna cumple los criterios de predicado de consulta, se omite el grupo de filas completo sin realizar el aprovisionamiento de la infraestructura real.When none of the column segment ranges meet the query predicate criteria, the entire rowgroup is skipped without doing any actual IO. Esto funciona porque los datos se cargan normalmente de forma ordenada y aunque no se garantiza que las filas se ordenen, los valores de datos similares a menudo se encuentran en el mismo grupo de filas o un grupo de filas adyacente.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.

Para más información sobre grupos de filas, vea la guía de índices de almacén de columnas.For more details about rowgroups, see Columnstore Indexes Guide

Ejecución del modo por lotesBatch Mode Execution

La ejecución del modo por lotes hace referencia al procesamiento de un conjunto de filas, normalmente hasta 900 filas juntas para obtener la eficacia de la ejecución.Batch mode execution refers to processing a set of rows, typically up to 900 rows, together for execution efficiency. Por ejemplo, la consulta SELECT SUM (Sales) FROM SalesData agrega las ventas totales de la tabla SalesData.For example, the query SELECT SUM (Sales) FROM SalesData aggregates the total sales from the table SalesData. En la ejecución del modo por lotes, el motor de ejecución de consultas calcula el agregado en el grupo de 900 valores.In batch mode execution, the query execution engine computes the aggregate in group of 900 values. Esto incluye metadatos, los costos de acceso y otros tipos de sobrecarga sobre todas las filas de un lote, en lugar de pagar el costo para cada fila. Por lo tanto, se reduce significativamente la ruta de acceso del código.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. El procesamiento de modo por lotes funciona en los datos comprimidos cuando sea posible y elimina algunos de los operadores de intercambio utilizados por el procesamiento del modo de fila.Batch mode processing operates on compressed data when possible and eliminates some of the exchange operators used by row mode processing. Esto acelera la ejecución de las consultas de análisis por órdenes de magnitud.This speeds up execution of analytics queries by orders of magnitude.

No todos los operadores de ejecución de consultas se pueden ejecutar en el modo por lotes.Not all query execution operators can be executed in batch mode. Por ejemplo, las operaciones de DML Insert, Delete o Update ejecutan una fila cada vez.For example, DML operations such as Insert, Delete or Update are executed row at a time. Los operadores de modo por lote destinan operadores para la aceleración del rendimiento de las consultas como Scan, Join, Aggregate, sort, etc.Batch mode operators target operators for speeding up query performance such as Scan, Join, Aggregate, sort and so on. Dado que el índice de almacén de columnas se introdujo en SQL Server 2012 (11.x)SQL Server 2012 (11.x), hay un esfuerzo sostenido para expandir los operadores que se pueden ejecutar en el modo por lotes.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 in the batch mode. En la tabla siguiente se muestran los operadores que se ejecutan en el modo por lotes según la versión del producto.The table below shows the operators that run in batch mode according to the product version.

Operadores del modo por lotesBatch Mode Operators ¿Cuándo se 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) y SQL DatabaseSQL Database1and SQL DatabaseSQL Database1 ComentariosComments
Operaciones de DML (insert, delete, update y merge)DML operations (insert, delete, update, merge) nono nono nono DML no es una operación de modo por lotes porque no es paralela.DML is not a batch mode operation because it is not parallel. Incluso cuando se habilita el procesamiento por lotes del modo serie, no vemos importantes mejoras si se permite que DML se procese en el modo por lotes.Even when we enable serial mode batch processing, we don't see significant gains by allowing DML to be processed in batch mode.
Exploración de índice de almacén de columnascolumnstore index scan SCANSCAN N/DNA yes yes Para los índices de almacén de columnas, podemos insertar el predicado en el nodo SCAN.For columnstore indexes, we can push the predicate to the SCAN node.
Exploración de índice de almacén de columnas (no agrupado)columnstore Index Scan (nonclustered) SCANSCAN yes yes yes yes
Index Seekindex seek N/DNA N/DNA nono Se lleva a cabo una operación de búsqueda a través de un índice no agrupado de árbol B en el modo de fila.We perform a seek operation through a nonclustered B-tree index in rowmode.
Compute Scalarcompute scalar Expresión que se evalúa en un valor escalar.Expression that evaluates to a scalar value. yes yes yes Sin embargo, hay algunas restricciones sobre el tipo de datos.There are some restrictions on data type. Esto es cierto para todos los operadores de modo por lotes.This is true for all batch mode operators.
concatenaciónconcatenation UNION y UNION ALLUNION and UNION ALL nono yes yes
filterfilter Aplicación de predicadosApplying predicates yes yes yes
Hash Matchhash match Funciones de agregación basada en hash, combinación hash exterior, combinación hash derecha, combinación hash izquierda, combinación derecha interna, combinación interna izquierdaHash-based aggregate functions, outer hash join, right hash join, left hash join, right inner join, left inner join yes yes yes Restricciones para la agregación: sin min/max para las cadenas.Restrictions for aggregation: no min/max for strings. Funciones de agregación disponibles: sum/count/avg/min/max.Aggregation functions available are sum/count/avg/min/max.
Restricciones de combinación: no combinaciones de tipo no coincidente en tipos no enteros.Restrictions for join: no mismatched type joins on non-integer types.
Merge Joinmerge join nono nono nono
Consultas multiprocesomulti-threaded queries yes yes yes
bucles anidadosnested loops nono nono nono
Consultas uniproceso que se ejecutan en MAXDOP 1single-threaded queries running under MAXDOP 1 nono nono yes
Consultas uniproceso con un plan de consulta en seriesingle-threaded queries with a serial query plan nono nono yes
Sortsort Cláusula Order by en SCAN con índice de almacén de columnasOrder by clause on SCAN with columnstore index. nono nono yes
Top Sorttop sort nono nono yes
Agregados de ventanawindow aggregates N/DNA N/DNA yes Nuevo operador en SQL Server 2016 (13.x)SQL Server 2016 (13.x).New operator in SQL Server 2016 (13.x)SQL Server 2016 (13.x).

1 se aplica a SQL Server 2016 (13.x)SQL Server 2016 (13.x), SQL DatabaseSQL Database a los niveles Premium, Standard, S3 y superiores, a todos los niveles de núcleo virtual y a Almacenamiento de datos paralelosParallel Data Warehouse1 Applies to SQL Server 2016 (13.x)SQL Server 2016 (13.x), SQL DatabaseSQL Database Premium tiers, Standard tiers - S3 and above, and all vCore tiers, and Almacenamiento de datos paralelosParallel Data Warehouse

Aplicación de agregadosAggregate Pushdown

Ruta de acceso de ejecución normal para el cálculo de agregados para capturar las filas calificadas desde el nodo SCAN y agregar los valores en el modo por lotes.A normal execution path for aggregate computation to fetch the qualifying rows from the SCAN node and aggregate the values in Batch Mode. Aunque esto proporciona un buen rendimiento, con SQL Server 2016 (13.x)SQL Server 2016 (13.x), la operación de agregación se puede insertar en el nodo SCAN para mejorar el rendimiento del cálculo de la agregación mediante órdenes de magnitud en la parte superior de la ejecución en el modo por lotes una vez que se cumplan las condiciones siguientes: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:

  • Los agregados son MIN, MAX, SUM, COUNT y COUNT(*).The aggregates are MIN, MAX, SUM, COUNT and COUNT(*).
  • El operador de agregación debe estar en la parte superior del nodo SCAN o el nodo SCAN con GROUP BY.Aggregate operator must be on top of SCAN node or SCAN node with GROUP BY.
  • Este agregado no es un agregado Distinct.This aggregate is not a distinct aggregate.
  • La columna de agregado no es una columna de cadena.The aggregate column is not a string column.
  • La columna de agregado no es una columna virtual.The aggregate column is not a virtual column.
  • El tipo de datos de entrada y salida debe ser uno de los siguientes y debe tener 64 bits o menos.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 y numeric con precisión < = 18smallmoney, money, decimal and numeric with precision <= 18
    • smalldate, date, datetime, datetime2, timesmalldate, date, datetime, datetime2, time

La aplicación de la agregación se acelera aún más con la agregación eficaz en datos comprimidos/codificados en la ejecución compatible con la caché y el aprovechamiento de SIMDAggregate push down is further accelerated by efficient Aggregation on compressed/encoded data in cache-friendly execution and by leveraging SIMD

aggregate pushdownaggregate pushdown

Por ejemplo, la aplicación de la agregación se realiza en las dos consultas siguientes: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    

Aplicación del predicado de la cadenaString predicate pushdown

Cuando se diseña un esquema de almacenamiento de datos, el modelado del esquema recomendado es usar un esquema de estrella o copo de nieve que conste de una o varias tablas de hechos y muchas tablas de dimensión.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 tabla de hechos almacena las transacciones o medidas empresariales y la tabla de dimensiones almacena las dimensiones en las que tienen que analizarse los hechos.The fact table stores the business measurements or transactions and dimension table store the dimensions across which facts need to be analyzed.

Por ejemplo, un hecho puede ser un registro que representa una venta de un producto determinado en una región específica, mientras que la dimensión representa un conjunto de regiones, productos y así sucesivamente.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. Las tablas de hechos y dimensiones están conectadas a través de la relación de clave principal o externa.The fact and dimension tables are connected through a primary/foreign key relationship. Las consultas de análisis de uso más frecuente unen una o varias tablas de dimensiones con la tabla de hechos.Most commonly used analytics queries join one or more dimension tables with the fact table.

Consideremos un elemento Products de tabla de dimensiones.Let us consider a dimension table Products. Una clave principal típica será ProductCode, que normalmente se representa como tipo de datos de cadena.A typical primary key will be ProductCode which is commonly represented as string data type. Para el rendimiento de las consultas, es aconsejable crear una clave suplente, normalmente una columna de enteros, para hacer referencia a la fila en la tabla de dimensiones de la tabla de hechos.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.

El índice de almacén de columnas ejecuta las consultas de análisis con combinaciones y predicados que implican claves basadas en enteros o números de forma muy eficiente.The columnstore index runs analytics queries with joins/predicates involving numeric or integer based keys very efficiently. Pero en muchas cargas de trabajo de cliente, encontramos que el uso de las columnas basadas en cadenas que se vinculan con tablas de dimensiones/hechos, lo que, como resultado, genera un rendimiento de la consulta del índice del almacén de columna diferente del esperado.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) mejora el rendimiento de las consultas de análisis con las columnas basadas en cadenas notablemente mediante la aplicación de los predicados con las columnas de cadena en el 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 aplicación del predicado de cadena aprovecha el diccionario principal y secundario creado para que las columnas mejoren el rendimiento de las consultas.String predicate pushdown leverages the primary/secondary dictionary created for column(s) to improve the query performance. Por ejemplo, consideremos el segmento de columna de cadena dentro de un grupo de filas que consta de 100 valores de cadena distintos.For example, let us consider string column segment within a rowgroup consisting of 100 distinct string values. Esto significa que se hace referencia a cada valor de cadena distinto 10 000 veces de media, lo que supone un millón de filas.This means each distinct string value is referenced 10,000 times on average assuming 1 million rows.

Con la aplicación del predicado de la cadena, la ejecución de la consulta computa el predicado con los valores del diccionario, y si son aptos, todas las cadenas referentes al valor del diccionario se aceptan automáticamente.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. Esto mejora el rendimiento de dos maneras:This improves the performance in two ways:

  1. Solo se devuelve la fila apta, lo que reduce el número de filas que fluye fuera del nodo SCAN.Only the qualified row is returned reducing number of the rows that need to flow out of SCAN node.

  2. Se reduce considerablemente el número de comparaciones de cadenas.The number of string comparisons are significantly reduced. En este ejemplo, se requieren solo 100 comparaciones de cadena en 1 millón de comparaciones.In this example, only 100 string comparisons are required as against 1 million comparisons. Existen algunas limitaciones, como se describe a continuación:There are some limitations as described below:

    • No aplicación del predicado de la cadena para grupos de filas delta.No string predicate pushdown for delta rowgroups. No hay ningún diccionario para las columnas en grupos de filas delta.There is no dictionary for columns in delta rowgroups.
    • No aplicación del predicado de la cadena si el diccionario supera las 64 KB de entradas.No string predicate pushdown if dictionary exceeds 64 KB entries.
    • No se admiten valores NULL de la evaluación de expresión.Expression evaluating NULLs are not supported.

Consulte tambiénSee Also

Guía de diseño de índices de almacén de columnas Columnstore Indexes Design Guidance
Guía de carga de datos de los índices de almacén de columnas Columnstore Indexes Data Loading Guidance
Introducción al almacén de columnas para análisis operativos en tiempo real Get started with Columnstore for real time operational analytics
Índices de almacén de columnas para el almacenamiento de datos Columnstore Indexes for Data Warehousing
Reorganizar y volver a generar índices Reorganize and Rebuild Indexes
Diseño de los índices de almacén de columnas Columnstore Index Architecture
CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL)ALTER INDEX (Transact-SQL)