Rendimiento de las consultas de índices de almacén de columnas

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

En este artículo se incluyen recomendaciones para lograr el rendimiento rápido de las consultas con índices de almacén de columnas.

Los índices de almacén de columnas pueden lograr hasta 100 veces mejor rendimiento en las cargas de trabajo de análisis y almacenamiento de datos, y hasta 10 veces mejor compresión de datos que los índices de almacén de filas tradicionales. Estas recomendaciones ayudan a las consultas a lograr el rendimiento rápido de las consultas que los índices de almacén de columnas están diseñados para proporcionar.

Recomendaciones para mejorar el rendimiento de las consultas

Aquí se proporcionan algunas recomendaciones para lograr el rendimiento alto que se espera que proporcionen los índices de almacén de columnas.

1. Organización de los datos para eliminar más grupos de filas de un recorrido de tabla completo

• Elija cuidadosamente el orden de inserción. 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. Por ejemplo, en los análisis de ventas por trimestre. Para este tipo de carga de trabajo, se produce la eliminación del grupo de filas automáticamente. En SQL Server 2016 (13.x), puede encontrar que se ha omitido una serie de grupos de filas como parte del procesamiento de consulta.

• Use un índice agrupado de almacén de filas. Si el predicado de consulta común está en una columna (por ejemplo, C1) no relacionada con el orden de inserción, cree un índice agrupado de almacén de filas en la columna C1. A continuación, quite el índice agrupado de almacén de filas y cree un índice de almacén de columnas agrupado. Si crea explícitamente el índice de almacén de columnas agrupado mediante MAXDOP = 1, el índice de almacén de columnas agrupado resultante se ordena perfectamente en la columna C1. Si especifica MAXDOP = 8, verá la superposición de valores entre ocho grupos de filas. Para un índice de almacén de columnas no agrupado (NCCI), si la tabla tiene un índice agrupado de almacén de filas, las filas ya están ordenadas por la clave de índice agrupada. En este caso, el índice de almacén de columnas no agrupado también se ordena automáticamente. Un índice de almacén de columnas no mantiene inherentemente el orden de las filas. A medida que se insertan nuevas filas o se actualizan las filas anteriores, es posible que tenga que repetir el proceso a medida que el rendimiento de las consultas de análisis podría deteriorarse.

• Implemente la creación de particiones de tablas. Puede particionar el índice de almacén de columnas y, a continuación, usar la eliminación de particiones para reducir el número de grupos de filas que se van a examinar. Por ejemplo, una tabla de hechos almacena las compras realizadas por los clientes. Un patrón de consulta común es buscar compras trimestrales por customer. En este caso, combine la columna de orden de inserción con la creación de particiones en customer la columna. Cada partición contiene filas para cada customer, ordenadas tras la inserción. Además, considere la posibilidad de usar la creación de particiones de tablas si es necesario quitar datos antiguos del almacén de columnas. La conmutación y truncamiento de particiones que no son necesarias es una estrategia eficaz para eliminar datos sin generar fragmentación.

• Evite la eliminación de grandes cantidades de datos. Quitar las filas comprimidas de un grupo de filas no es una operación sincrónica. Resultaría caro descomprimir un grupo de filas, eliminar la fila y, a continuación, volver a comprimirla. Por lo tanto, cuando se eliminan datos de grupos de filas comprimidos, estos grupos de filas se siguen examinando, aunque devuelvan menos filas. Si el número de filas eliminadas de varios grupos de filas es lo suficientemente grande como para combinarse en menos grupos de filas, la reorganización del almacén de columnas aumenta la calidad del índice y el rendimiento de las consultas mejora. Si el proceso de eliminación de datos suele vaciar grupos de filas completos, considere la posibilidad de usar la creación de particiones de tablas. Cambie las particiones que ya no son necesarias y trunquelas, en lugar de eliminar filas.

  Nota:

  A partir de SQL Server 2019 (15.x), el motor de tupla ayuda con una tarea de combinación en segundo plano. Esta tarea comprime automáticamente grupos de filas delta OPEN más pequeños que han existido durante algún tiempo, según lo determinado por un umbral interno, o combina grupos de filas COMPRIMIDOs desde donde se ha eliminado un gran número de filas. De este modo, se mejora la calidad del índice de almacén de columnas a lo largo del tiempo. Si se requieren grandes cantidades de datos del índice de almacén de columnas, considere la posibilidad de dividir esa operación en lotes de eliminación más pequeños a lo largo del tiempo. El procesamiento por lotes permite que la tarea de combinación en segundo plano controle la tarea de combinar grupos de filas más pequeños y mejore la calidad del índice. A continuación, no es necesario programar ventanas de mantenimiento de reorganización de índices después de la eliminación de datos. Para obtener más información sobre los términos y conceptos de almacén de columnas, vea Índices de almacén de columnas: información general.

2. Planear para que haya suficiente memoria para crear índices de almacén de columnas en paralelo

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. Crear el índice en paralelo requiere más memoria que crear el índice en serie. 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.

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. Por ejemplo, si la tabla tiene menos de un millón de filas, SQL Server usa solo un subproceso para crear el índice de almacén de columnas.

Si la tabla tiene más de un millón de filas, pero SQL Server no puede obtener una concesión de memoria suficientemente grande para crear el índice mediante MAXDOP, SQL Server disminuye MAXDOP automáticamente según sea necesario. En algunos casos, el DOP debe reducirse a uno para crear el índice bajo memoria restringida en la concesión de memoria disponible.

Desde SQL Server 2016 (13.x), la consulta siempre funciona en modo por lotes. En versiones anteriores, la ejecución por lotes solo se utiliza cuando DOP es mayor que uno.

Explicación del rendimiento del almacén de columnas

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. Dado que las consultas de análisis examinan un gran número de filas, normalmente están enlazadas a E/S y, por lo tanto, reducir la E/S durante la ejecución de consultas es fundamental para el diseño de índices de almacén de columnas. Una vez que los datos se leen en la memoria, es fundamental reducir el número de operaciones en memoria.

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.

Compresión de datos

Los índices de almacén de columnas logran hasta 10 veces mayor compresión de datos que los índices de almacén de filas. 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.

• 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.

• Los índices de almacén de columnas almacenan datos en forma comprimida en memoria, lo que reduce la E/S evitando leer los mismos datos en la memoria. Por ejemplo, con una compresión de 10 veces, los índices de almacén de columnas pueden mantener 10 veces más datos en memoria, en comparación con el almacenamiento de los datos en formato sin comprimir. Con más datos en memoria, es más probable que el índice de almacén de columnas encuentre los datos que necesita en la memoria sin incurrir en lecturas innecesarias del disco.

• 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. Se comprime y almacena cada columna de forma independiente. Los datos de una columna siempre tienen el mismo tipo de datos y tiende a tener valores similares. Las técnicas de compresión de datos de almacén de columnas son excelentes para lograr mayores tasas de compresión cuando los valores son similares.

Por ejemplo, una tabla de hechos almacena direcciones de cliente y tiene una columna para country-region. El número total de valores posibles es inferior a 200. Algunos de esos valores se repiten muchas veces. Si la tabla de hechos tiene 100 millones de filas, la country-region columna se comprime fácilmente y requiere poco almacenamiento. La compresión de fila por fila no puede poner en mayúscula la similitud de los valores de columna de esta manera y debe usar más bytes para comprimir los valores de la country-region columna.

Eliminación de la columna

Los índices de almacén de columnas omiten la lectura de las columnas que no son necesarias para el resultado de la consulta. La eliminación de columnas reduce aún más la E/S para la ejecución de consultas y, por tanto, mejora el rendimiento de las consultas.

• La eliminación de la columna es posible porque los datos se organizan y comprimen columna por columna. 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. El procesador de consultas debe leer en una fila completa para recuperar valores de columna específicos, lo que aumenta la E/S porque los datos adicionales se leen innecesariamente en la memoria.

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. Omite la lectura en las otras 45 columnas, lo que reduce la E/S por otro 90 %, suponiendo que todas las columnas tienen un tamaño similar. Si los mismos datos se almacenan en un almacén de filas, el procesador de consultas debe leer las 45 columnas restantes.

Eliminación del grupo de filas

Para el análisis de una tabla completa, un gran porcentaje de los datos no coincide normalmente con los criterios de predicado de consulta. 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. 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.

¿Cuándo un índice de almacén de columnas tiene que realizar un análisis de tabla completa?

A partir de SQL Server 2016 (13.x), puede crear uno o varios índices de almacén de filas no agrupados normales o de árbol B en un índice de almacén de columnas agrupado. 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. Para predicados más complicados, el optimizador de consultas puede elegir un análisis de tabla completa. Sin la capacidad de omitir grupos de filas, un examen de tabla completa puede llevar mucho tiempo, especialmente para tablas grandes.

¿Cuándo se beneficia una consulta de análisis de la eliminación del grupo de filas para un análisis de tabla completa?

Por ejemplo, un negocio minorista modela sus datos de ventas mediante una tabla de hechos con el índice de almacén de columnas agrupado. Cada nueva venta almacena varios atributos de la transacción, incluida la fecha en que se vende un producto. Interesantemente, aunque los índices de almacén de columnas no garantizan un orden ordenado, las filas de esta tabla se cargan en un orden ordenado de fecha. Con el tiempo esta tabla crece. 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. 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. Se trata de una reducción del 97 % en la cantidad de datos que se leen en la memoria y se procesan.

¿Qué grupos de filas se omiten en un análisis de tabla completa?

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. Cuando ninguno de los intervalos de segmentos de columna cumple los criterios de predicado de consulta, se omite todo el grupo de filas sin realizar ninguna E/S real. Esto funciona porque los datos normalmente se cargan en un orden ordenado. Aunque no se garantiza la ordenación de filas, los valores de datos similares a menudo se encuentran en el mismo grupo de filas o en un grupo de filas vecino.

Para obtener más información sobre los grupos de filas, consulte Guía de diseño de índices de almacén de columnas.

Ejecución del modo por lotes

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. Por ejemplo, la consulta SELECT SUM (Sales) FROM SalesData agrega las ventas totales de la tabla 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. 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. El procesamiento en modo por lotes funciona en datos comprimidos siempre que sea posible y elimina algunos de los operadores de intercambio utilizados por el procesamiento en modo de fila, lo que acelera las consultas de análisis por orden de magnitud.

No todos los operadores de ejecución de consultas se pueden ejecutar en el modo por lotes. Por ejemplo, las operaciones del lenguaje de manipulación de datos (DML), como insertar, eliminar o actualizar, se ejecutan una fila a la vez. El operador de modo por lotes, como Scan, Join, Aggregate, Sort y otros, puede mejorar el rendimiento de las consultas. Dado que el índice de almacén de columnas se introdujo en SQL Server 2012 (11.x), no existe un esfuerzo sostenido de expansión de los operadores que puedan ejecutarse en el modo por lotes. En la tabla siguiente se muestran los operadores que se ejecutan en modo por lotes según la versión del producto.

¹ Se aplica a SQL Server 2016 (13.x), los niveles Premium de SQL Database, los niveles Estándar: S3 y versiones posteriores, y todos los niveles de núcleo virtual y el sistema de plataforma de análisis (PDW)

Para más información, vea la Guía de arquitectura de procesamiento de consulta.

Aplicación de agregado

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. Aunque esto ofrece un buen rendimiento, a partir de SQL Server 2016 (13.x), la operación de agregado se puede insertar en el nodo SCAN. La aplicación de agregado mejora el rendimiento de los cálculos agregados por orden de magnitud sobre la ejecución del modo por lotes, siempre que se cumplan las condiciones siguientes:

• Los agregados son MIN, MAX, SUM, COUNT y COUNT(*).
• El operador de agregación debe estar en la parte superior del nodo SCAN o el nodo SCAN con GROUP BY.
• Este agregado no es un agregado Distinct.
• La columna de agregado no es una columna de cadena.
• La columna de agregado no es una columna virtual.
• El tipo de datos de entrada y salida debe ser uno de los siguientes y debe ajustarse a 64 bits:
  • tinyint, int, bigint, smallint, bit
  • smallmoney, money, decimal y numeric con precisión <= 18
  • smalldate, date, datetime, datetime2, time

Por ejemplo, la inserción de agregados se realiza en las dos consultas siguientes:

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

Aplicación del predicado de la cadena

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.

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. Las tablas de hechos y dimensiones están conectadas a través de la relación de clave principal o externa. Las consultas de análisis de uso más frecuente unen una o varias tablas de dimensiones con la tabla de hechos.

Consideremos un elemento Products de tabla de dimensiones. Una clave principal típica es , que normalmente se ProductCoderepresenta como cadena. Para el rendimiento de las consultas, es un procedimiento recomendado crear una clave suplente, normalmente una columna de enteros , para hacer referencia a la fila de la tabla de dimensiones de la tabla de hechos.

El índice de almacén de columnas ejecuta consultas de análisis con combinaciones y predicados que implican claves numéricas o basadas en enteros de forma eficaz. SQL Server 2016 (13.x) mejoró el rendimiento de las consultas de análisis con columnas basadas en cadenas significativamente, al insertar los predicados con columnas de cadena en el nodo SCAN.

La inserción de predicado de cadena aprovecha el diccionario principal o secundario creado para las columnas para mejorar el rendimiento de las consultas. Por ejemplo, considere un segmento de columna de cadena dentro de un grupo de filas que consta de 100 valores de cadena distintos. Cada valor de cadena distinto se hace referencia a 10 000 veces en promedio, suponiendo un millón de filas. Con la inserción de predicado de cadena, la ejecución de la consulta calcula el predicado con los valores del diccionario. Si el predicado se califica, todas las filas que hacen referencia al valor del diccionario se califican automáticamente. Esto mejora el rendimiento de dos maneras:

• Solo se devuelve la fila calificada, lo que reduce el número de filas que necesitan fluir fuera del nodo de examen.
• Se reduce el número de comparaciones de cadenas. En este ejemplo, se requieren solo 100 comparaciones de cadena en 1 millón de comparaciones. Existen algunas limitaciones:
  • No aplicación del predicado de la cadena para grupos de filas delta. No hay ningún diccionario para las columnas en grupos de filas delta.
  • No aplicación del predicado de la cadena si el diccionario supera las 64 KB de entradas.
  • No se admite la evaluación de expresiones NULL.

Eliminación de segmento

Las opciones de tipo de datos podrían repercutir significativamente sobre los predicados de filtro comunes basados en el rendimiento de las consultas en el índice de almacén de columnas.

En los datos de almacén de columnas, los grupos de filas se componen de segmentos de columna. Hay metadatos con cada segmento para permitir la eliminación rápida de segmentos sin leerlos. Esta eliminación de segmentos se aplica a los tipos de datos numéricos, de fecha y hora, y al tipo de datos datetimeoffset con escala menor o igual que dos. A partir de SQL Server 2022 (16.x), las funcionalidades de eliminación de segmentos se extienden a los tipos de datos string, binary, guid y el tipo de datos datetimeoffset para la escala superior a 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á esta característica hasta que se vuelva a generar mediante o REBUILD/DROPCREATE.

La eliminación de segmentos no se aplica a los tipos de datos LOB, como las longitudes de tipo de datos (max).

Actualmente, solo SQL Server 2022 (16.x) y versiones posteriores admiten la eliminación de grupos de filas de almacén de columnas agrupados para el prefijo de predicados LIKE, por ejemplo column LIKE 'string%'. No se admite la eliminación de segmentos para el uso sin prefijo de LIKE, como column LIKE '%string'.

Los índices de almacén de columnas agrupados ordenados también se benefician de la eliminación de segmentos, especialmente para las columnas de cadena. En los índices de almacén de columnas agrupados ordenados, la eliminación de segmentos en la primera columna de la clave de índice es más eficaz, ya que se ordena. Las mejoras de rendimiento debido a la eliminación de segmentos en otras columnas de la tabla serán menos predecibles. Para más información sobre los índices de almacén de columnas agrupados ordenados, consulte Uso de un índice de almacén de columnas agrupado ordenado para tablas de almacenamiento de datos grandes. Para obtener disponibilidad ordenada del índice de almacén de columnas, consulte Disponibilidad de índices de columna ordenada.

Con la opción de conexión de consulta SET STATISTICS IO, puede ver la eliminación de segmentos en acción. Busque una salida como la siguiente para indicar que se ha producido la eliminación de segmentos. Los grupos de filas se componen de segmentos de columna, por lo que esto podría indicar la eliminación de segmentos. En el ejemplo de salida siguiente SET STATISTICS IO de una consulta, la consulta omitió aproximadamente el 83 % de los datos:

...
Table 'FactResellerSalesPartCategoryFull'. Segment reads 16, segment skipped 83.
...

Contenido relacionado

• Directrices para diseñar índices de almacén de columnas
• Índices de almacén de columnas: Guía de carga de datos
• Introducción al almacén de columnas para análisis operativos en tiempo real
• Índices de almacén de columnas en el almacenamiento de datos
• Optimización del mantenimiento de índices para mejorar el rendimiento de las consultas y reducir el consumo de recursos
• Diseño de los índices de almacén de columnas
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)