Compartir a través de

Novedades de los índices de almacén de columnas

  • Artículo

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Base de datos SQL de Microsoft Fabric

Obtenga información sobre qué características de almacén de columnas están disponibles para cada versión de SQL Server y las versiones más recientes de SQL Database, Azure Synapse Analytics y Analytics Platform System (PDW).

Resumen de características para las versiones de productos

En esta tabla se resumen las características fundamentales de los índices de almacén de columnas y los productos en los que están disponibles.

Característica de índice de almacén de columnas SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x)1 SQL Server 2017 (14.x) SQL Server 2019 (15.x) SQL Server 2022 (16.x) SQL Database1 Grupo de SQL dedicado de Azure Synapse Analytics
Ejecución del modo por lotes de consultas multiproceso2 sí sí sí sí sí sí sí sí
Ejecución del modo por lotes para las consultas de un solo subproceso sí sí sí sí sí sí
Opción de compresión de archivos sí sí sí sí sí sí sí
Aislamiento de instantánea y aislamiento de instantánea de lectura confirmada sí sí sí sí sí sí
Especificación del índice de almacén de columnas a la hora de crear una tabla sí sí sí sí sí sí
Compatibilidad de Always On con índices de almacén de columnas sí sí sí sí sí sí sí sí
Compatibilidad del elemento secundario legible de Always On con índices de almacén de columnas no agrupados de solo lectura sí sí sí sí sí sí sí sí
Compatibilidad del elemento secundario legible de Always On con índices de almacén de columnas actualizables sí sí sí sí
Índice de almacén de columnas no agrupado de solo lectura en árbol B o montón sí sí sí 3 sí 3 sí 3 sí 3 sí 3 sí 3
Índice de almacén de columnas no agrupado actualizable en árbol B o montón sí sí sí sí sí sí
Índices de árbol B adicionales permitidos en un montón o árbol B con índice de almacén de columnas no agrupado sí sí sí sí sí sí sí sí
Índice de almacén de columnas agrupado actualizable sí sí sí sí sí sí sí
Índice de árbol B en índice de almacén de columnas agrupado sí sí sí sí sí sí
Índice de almacén de columnas en una tabla optimizada para memoria sí sí sí sí sí sí
Compatibilidad de la definición del índice de almacén de columnas no agrupado con el uso de una condición filtrada sí sí sí sí sí sí
Opción de retraso de compresión para los índices de almacén de columnas en CREATE TABLE y ALTER TABLE sí sí sí sí sí sí
Compatibilidad con el tipo nvarchar(max) sí sí sí sí no 4
El índice de almacén de columnas puede tener una columna calculada no persistente sí sí sí
Compatibilidad con la combinación en segundo plano del motor de tuplas sí sí sí sí
Índices de almacén de columnas agrupados ordenados sí sí sí
Índices de almacén de columnas no agrupados ordenados sí

1 Para SQL Database, los índices de almacén de columnas están disponibles en los niveles Azure SQL Database DTU Premium y DTU Standard - S3 y versiones posteriores, y en todos los niveles de núcleo virtual. Para SQL Server 2016 (13.x) SP1 y versiones posteriores, los índices de almacén de columnas están disponibles en todas las ediciones. Para SQL Server 2016 (13.x) (antes de SP1) y versiones anteriores, los índices de almacén de columnas solo están disponibles en Enterprise Edition.

2 El grado de paralelismo (DOP) de las operaciones del modo de proceso por lotes está limitado a 2 para SQL Server Standard Edition y a 1 para SQL Server Web Edition y Express Edition. Esta limitación se refiere a los índices de almacén de columnas que se crean en tablas basadas en disco y en tablas optimizadas para memoria.

3 Para crear un índice de almacén de columnas no agrupado de solo lectura, almacénelo en un grupo de archivos de solo lectura.

4 No se admite en grupos de SQL dedicados, pero se admite en el grupo de SQL sin servidor.

SQL Server 2022 (16.x)

SQL Server 2022 (16.x) agregó estas características.

  • Los índices de almacén de columnas agrupados ordenados mejoran el rendimiento de las consultas basadas en predicados de columna ordenados. Los índices de almacén de columnas ordenados pueden mejorar el rendimiento omitiendo por completo los segmentos de datos. Esto puede reducir drásticamente la E/S necesaria para completar consultas en los datos del almacén de columnas. Para obtener más información, consulte eliminación de segmentos. Los índices de almacén de columnas agrupados ordenados se introdujeron en SQL Server 2022 (16.x). Para obtener más información, consulte CREATE COLUMNSTORE INDEX and Performance tuning with ordered clustered columnstore indexes (Optimización de rendimiento y ÍNDICE DE ALMACÉN DE COLUMNAs con índices de almacén de columnas agrupados ordenados).

  • La delegación de predicado con la eliminación de grupos de filas de almacén de columnas agrupados de cadenas usa valores de límite para optimizar las búsquedas de cadenas. Todos los índices de almacén de columnas se benefician de la eliminación mejorada de segmentos por tipo de datos. A partir de SQL Server 2022 (16.x), las funcionalidades de eliminación de segmentos se extienden a los tipos de datos de cadena, binarios, guid y al tipo de datos datetimeoffset para la escala superior a dos. Anteriormente, esta eliminación de segmentos se aplicaba solo a los tipos de datos numéricos, de fecha y hora, y al tipo de datos datetimeoffset con escala menor o igual que dos. Después de actualizar a una versión de SQL Server que admita la eliminación de segmentos mínimos y máximos de cadena [SQL Server 2022 (16.x) y versiones posteriores], el índice de almacén de columnas no beneficiará a esta característica hasta que se vuelva a generar mediante REBUILD o DROP/CREATE.

  • Para obtener más información sobre las características agregadas, vea Novedades de SQL Server 2022.

SQL Server 2019 (15.x)

SQL Server 2019 (15.x) agrega estas nuevas características:

Funcional

A partir de SQL Server 2019 (15.x), el motor de tupla cuenta con la ayuda de una tarea de combinación en segundo plano que comprime automáticamente los grupos de filas delta OPEN que han existido durante algún tiempo, según lo determinado por un umbral interno, o combina los grupos de filas COMPRESSED desde donde se ha eliminado un gran número de filas. Anteriormente, se necesitaba una operación de reorganización de índices para combinar grupos de filas con datos eliminados parcialmente. De este modo, se mejora la calidad del índice de almacén de columnas a lo largo del tiempo.

SQL Server 2017 (14.x)

SQL Server 2017 (14.x) agrega estas nuevas características.

Funcional

  • SQL Server 2017 (14.x) admite columnas calculadas no persistentes en índices de almacén de columnas agrupados. No se admiten columnas persistentes en índices de almacén de columnas agrupados. No se puede crear un índice no agrupado en un índice de almacén columnas con una columna calculada.

SQL Server 2016 (13.x)

SQL Server 2016 (13.x) incorpora mejoras esenciales destinadas a mejorar el rendimiento y la flexibilidad de los índices de almacén de columnas. Estas mejoras optimizan los escenarios de almacenamiento de datos y facilitan los análisis operativos en tiempo real.

Funcional

  • Una tabla de almacén de filas puede contar con un índice de almacén de columnas no agrupado actualizable. Antes, el índice de almacén de columnas no agrupado era de solo lectura.

  • La definición del índice de almacén de columnas no agrupado admite el uso de una condición de filtrado. Para minimizar el impacto de rendimiento que tiene agregar un índice de almacén de columnas a una tabla OLTP, use una condición de filtrado para crear un índice de almacén de columnas no agrupado únicamente en los datos inactivos de la carga de trabajo operativa.

  • Las tablas en memoria pueden tener un índice de almacén de columnas. Puede crearlo cuando se genere la tabla o agregarlo en otro momento con ALTER TABLE (Transact-SQL). Antes, solo las tablas basadas en disco podían contar con un índice de almacén de columnas.

  • Un índice de almacén de columnas agrupado puede tener uno o varios índices de almacén de filas no agrupados. Antes, el índice de almacén de columnas no admitía índices no agrupados. SQL Server mantiene automáticamente los índices no agrupados para las operaciones DML.

  • Compatibilidad con las claves principales y claves externas mediante un índice de árbol B para aplicar estas restricciones en un índice de almacén de columnas agrupado.

  • Los índices de almacén de columnas tienen una opción de retraso de compresión que minimiza el impacto de la carga de trabajo transaccional en los análisis operativos en tiempo real. Esta opción permite cambiar con frecuencia filas para estabilizarlas antes de comprimirlas en el almacén de columnas. Para más información, consulte CREATE COLUMNSTORE INDEX (Transact-SQL) e Introducción al almacén de columnas para análisis operativos en tiempo real.

Rendimiento del nivel de compatibilidad de base de datos 120 o 130

  • Los índices de almacén de columnas admiten el nivel de aislamiento de instantánea de lectura confirmada (RCSI) y el aislamiento de instantánea (SI). De esta forma, se pueden realizar consultas de análisis homogéneas transaccionales sin ningún bloqueo.

  • El almacén de columnas admite con la desfragmentación de índices mediante la eliminación de filas suprimidas sin necesidad de volver a generar el índice explícitamente. La instrucción ALTER INDEX ... REORGANIZE quita las filas eliminadas, según una directiva definida internamente, del almacén de columnas como una operación en línea

  • Es posible acceder a los índices de almacén de columnas en una réplica secundaria legible de AlwaysOn. Se puede mejorar el rendimiento de los análisis operativos descargando consultas de análisis en una réplica secundaria de AlwaysOn.

  • La Aplicación de agregados calcula las funciones de agregado MIN, MAX, SUM, COUNT y AVG durante los recorridos de tabla cuando el tipo de datos usa un máximo de ocho bytes y no es de cadena. Se admite la Aplicación de agregados, con la cláusula GROUP BY o sin esta, tanto para los índices de almacén de columnas agrupados como para los no agrupados. En SQL Server, esta mejora está reservada para Enterprise Edition.

  • La Aplicación de predicado de la cadena acelera las consultas que comparan cadenas del tipo VARCHAR/CHAR o NVARCHAR/NCHAR. Esto se aplica a los operadores de comparación habituales y se incluyen operadores que utilizan filtros de mapa de bits, como LIKE. Esto funciona con todas las intercalaciones admitidas. En SQL Server, esta mejora está reservada para Enterprise Edition.

  • Mejoras para las operaciones en modo de proceso por lotes mediante el uso de las capacidades de hardware basadas en vectores. El motor de base de datos detecta el nivel de compatibilidad de la CPU para las extensiones de hardware AVX 2 (Extensiones de vector avanzadas) y SSE 4 (Extensiones SIMD de streaming 4) y las usa si son compatibles. En SQL Server, esta mejora está reservada para Enterprise Edition.

Rendimiento del nivel de compatibilidad de base de datos 130

  • El modo de ejecución por lotes admite ahora consultas con cualquiera de estas operaciones:

    • SORT
    • Agregados con varias funciones distintas. Algunos ejemplos son los siguientes: COUNT/COUNT, AVG/SUM, CHECKSUM_AGG y STDEV/STDEVP.
    • Funciones de agregado de ventana: COUNT, COUNT_BIG, SUM, AVG, MIN, MAX y CLR.
    • Agregados definidos por el usuario de ventana: CHECKSUM_AGG, STDEV, STDEVP, VAR, VARP y GROUPING.
    • Funciones analíticas de agregado de ventana: LAG, LEAD, FIRST_VALUE, LAST_VALUE, PERCENTILE_CONT, PERCENTILE_DISC, CUME_DIST y PERCENT_RANK.

  • Las consultas de un solo proceso que se ejecuten con MAXDOP 1 o un plan de consulta en serie se ejecutarán en el modo por lotes. Antes, solo las consultas multiproceso se ejecutaban por lotes.

  • Las consultas de tabla optimizada para memoria pueden tener planes paralelos en el modo de interoperabilidad de SQL a la hora de acceder a datos de índices de almacén de columnas o almacén de filas.

Compatibilidad

Las siguientes vistas del sistema son nuevas para el almacén de columnas:

sys.column_store_row_groups (Transact-SQL)

sys.dm_column_store_object_pool (Transact-SQL)

sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)

sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)

sys.dm_db_index_operational_stats (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

sys.internal_partitions (Transact-SQL)

Estas DMV basadas en OLTP en memoria contienen actualizaciones para el almacén de columnas:

sys.dm_db_xtp_hash_index_stats (Transact-SQL)

sys.dm_db_xtp_index_stats (Transact-SQL)

sys.dm_db_xtp_memory_consumers (Transact-SQL)

sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)

sys.dm_db_xtp_object_stats (Transact-SQL)

sys.dm_db_xtp_table_memory_stats (Transact-SQL)

Limitaciones

  • En el caso de las tablas en memoria, los índices de almacén de columnas deben incluir todas las columnas; estos no pueden tener una condición de filtrado.
  • Para las tablas en memoria, las consultas de índices de almacén de columnas se ejecutan únicamente en el modo de interoperabilidad y no en el modo nativo en memoria. Se admite la ejecución en paralelo.

Problemas conocidos

Se aplica a: SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics (grupo de SQL dedicado)

  • Actualmente, las columnas LOB [varbinary(max), varchar(max) y nvarchar(max)] en segmentos de almacén de columnas comprimidos no se ven afectados por DBCC SHRINKDATABASE y DBCC SHRINKFILE.

SQL Server 2014 (12.x)

SQL Server 2014 (12.x) introdujo el índice de almacén de columnas agrupadas como el formato de almacenamiento principal. Este permitía cargas regulares, así como operaciones de actualización, eliminación e inserción.

  • La tabla puede utilizar un índice de almacén de columnas agrupadas como el almacenamiento de tabla principal. No se permite ningún otro índice en la tabla, pero el índice de almacén de columnas agrupado se puede actualizar. De este modo, se pueden realizar cargas regulares y efectuar cambios en filas individuales.
  • Los índices de almacén de columnas no agrupados siguen presentando la misma funcionalidad que en SQL Server 2012 (11.x), excepto en lo concerniente a los operadores adicionales que se pueden ejecutar en el modo por lotes. Aún no se pueden actualizar, a menos que se reconstruyan o se utilice la modificación de las particiones. Los índices de almacén de columnas solo son compatibles en las tablas basadas en disco y no en las tablas en memoria.
  • Los índices de almacén de columnas agrupados y no agrupados cuentan con una opción de compresión de archivos que comprimen aún más los datos. La opción de archivos resulta útil para reducir el tamaño de los datos en memoria y en disco, pero repercute negativamente en el rendimiento de las consultas. Funciona bien para los datos a los que se accede con poca frecuencia.
  • Los índices de almacén de columnas agrupados y no agrupados funcionan de una manera muy similar; utilizan el mismo formato de almacenamiento en columnas, el mismo motor de procesamiento de consultas y el mismo conjunto de vistas de administración dinámica. La diferencia radica en los tipos de índices primarios frente a los secundarios; además, el índice de almacén de columnas no agrupado es de solo lectura.
  • Estos operadores se ejecutan en modo por lotes de consultas multiproceso: SCAN, FILTER, PROJECT, JOIN, GROUP BY y UNION ALL.

SQL Server 2012 (11.x)

SQL Server 2012 (11.x) introdujo el índice de almacén de columnas no agrupado como otro tipo de índice en las tablas de almacén de filas y el procesamiento por lotes para las consultas de los datos de almacén de columnas.

  • Una tabla de almacén de filas puede contar con un índice de almacén de columnas no agrupado.
  • El índice de almacén de columnas es de solo lectura. Después de crear el índice de almacén, no se puede actualizar la tabla mediante las operaciones INSERT, DELETE y UPDATE; para realizar estas operaciones debe quitar el índice, actualizar la tabla y reconstruir el índice de almacén de columnas. Puede cargar datos adicionales en la tabla mediante la modificación de las particiones. La ventaja de la modificación de las particiones es que puede cargar datos sin quitar y reconstruir el índice de almacén de columnas.
  • El índice de almacén de columnas siempre necesita almacenamiento adicional, normalmente un 10 % más que el almacén de filas, ya que almacena una copia de los datos.
  • El procesamiento por lotes se traduce en que las consultas rinden el doble de bien o mejor, pero solo está disponible para la ejecución de consultas en paralelo.

Contenido relacionado