Compartir a través de

Optimización del rendimiento con índices de almacén de columnas agrupados ordenados

  • Artículo

Se aplica a: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance

Al habilitar la eliminación eficaz de segmentos, los índices de almacén de columnas agrupados ordenados (CCI) proporcionan un rendimiento mucho más rápido omitiendo grandes cantidades de datos ordenados que no coinciden con el predicado de consulta. La carga de datos en una tabla de CCI ordenado puede tardar más que en una tabla de CCI no ordenado debido a la operación de ordenación de datos; sin embargo, posteriormente las consultas podrán ejecutarse más rápidamente con el CCI ordenado.

Cuando los usuarios consultan una tabla de almacén de columnas, el optimizador comprueba los valores mínimos y máximos almacenados en cada segmento. Los segmentos que están fuera de los límites del predicado de la consulta no se leen del disco a la memoria. Una consulta puede finalizar más rápido si el número de segmentos que se van a leer y su tamaño total son pequeños.

Para obtener disponibilidad ordenada del índice de almacén de columnas, consulte Disponibilidad de índices de columna ordenada.

Índice de almacén de columnas agrupado ordenado frente al no ordenado

De forma predeterminada, para cada tabla creada sin una opción de índice, un componente interno (generador de índices) crea en ellas un índice de almacén de columnas agrupado (CCI) no ordenado. Los datos de cada columna se comprimen en un segmento de grupo de filas de CCI independiente. Hay metadatos en el intervalo de valores de cada segmento, por lo que los segmentos que están fuera de los límites del predicado de la consulta no se leen desde el disco durante la ejecución de la consulta. CCI ofrece el máximo nivel de compresión de datos y reduce el tamaño de los segmentos que se van a leer para que las consultas se ejecuten más rápido. Sin embargo, dado que el generador de índices no ordena los datos antes de comprimirlos en segmentos, pueden darse segmentos con intervalos de valores superpuestos, lo que hace que las consultas lean más segmentos del disco y tarden más en finalizar.

Al crear un CCI ordenado, SQL Motor de base de datos ordena los datos existentes en memoria por las claves de orden antes de que el generador de índices los comprima en segmentos de índice. Con los datos ordenados, se reduce la superposición de segmentos, lo que permite que las consultas tengan una eliminación de segmentos más eficaz y, por tanto, un rendimiento más rápido, ya que el número de segmentos que se leerán desde el disco es menor. Si todos los datos se pueden ordenar en memoria de una vez, se puede evitar la superposición de segmentos. Dado el gran tamaño de las tablas de los almacenamientos de datos, este escenario no se produce con frecuencia.

Para comprobar los intervalos de segmentos de una columna, ejecute el comando siguiente con el nombre de la tabla y el nombre de la columna:

SELECT
o.name, pnp.index_id,
cls.row_count, pnp.data_compression_desc,
cls.segment_id,
cls.column_id,
cls.min_data_id, cls.max_data_id,
cls.max_data_id-cls.min_data_id as difference 

FROM sys.partitions AS pnp
 INNER JOIN sys.tables AS t ON pnp.object_id = t.object_id 
 INNER JOIN sys.objects AS o ON t.object_id = o.object_id
 INNER JOIN sys.column_store_segments AS cls ON pnp.partition_id = cls.partition_id
 INNER JOIN sys.columns as cols ON o.object_id = cols.object_id AND cls.column_id = cols.column_id
WHERE o.name = '<Table Name>' and cols.name = '<Column Name>'  
ORDER BY o.name, pnp.index_id, cls.min_data_id;

Nota

En una tabla de CCI ordenada, los nuevos datos resultantes del mismo lote de DML o de operaciones de carga de datos se organizan dentro de ese lote, no hay ninguna organización global de todos los datos de la tabla. Los usuarios pueden RECOMPILAR el CCI ordenado para ordenar todos los datos de la tabla. En el caso de una tabla con particiones, la RECOMPILACIÓN se realiza en una partición cada vez. Los datos de la partición que se está recompilando estarán "sin conexión" y no estarán disponibles hasta que la RECOMPILACIÓN se complete para esa partición.

Rendimiento de las consultas

La mejora del rendimiento de una consulta desde un CCI ordenado depende de los patrones de consulta, el tamaño de los datos, el grado de orden de los datos, la estructura física de los segmentos y la unidad de almacenamiento de datos y la clase de recurso elegidos para la ejecución de la consulta. Los usuarios deben revisar todos estos factores antes de elegir las columnas de ordenación al diseñar una tabla de CCI ordenado.

Las consultas con todos estos patrones suelen ejecutarse más rápido con CCI ordenado.

  • Las consultas tienen predicados de igualdad, desigualdad o intervalo.
  • Las columnas de predicado y las columnas de CCI ordenado son las mismas.

En este ejemplo, la tabla T1 tiene un índice de almacén de columnas agrupado ordenado en la secuencia de Col_C, Col_By Col_A.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON  T1
ORDER (Col_C, Col_B, Col_A);

El rendimiento de la consulta 1 y la consulta 2 puede beneficiarse más del CCI ordenado que las demás consultas, ya que hacen referencia a todas las columnas de CCI ordenadas.

-- Query #1:

SELECT * FROM T1 WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';

-- Query #2

SELECT * FROM T1 WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';

-- Query #3
SELECT * FROM T1 WHERE Col_B = 'b' AND Col_A = 'a';

-- Query #4
SELECT * FROM T1 WHERE Col_A = 'a' AND Col_C = 'c';

Rendimiento de la carga de datos

El rendimiento de la carga de datos en una tabla de CCI ordenado es similar a una tabla con particiones. La carga de datos en una tabla de CCI ordenado puede tardar más que en una tabla de CCI no ordenado debido a la operación de ordenación de datos; sin embargo, posteriormente las consultas podrán ejecutarse más rápidamente con el CCI ordenado.

Reducción de la superposición de segmentos

El número de segmentos superpuestos depende del tamaño de los datos que se van a ordenar, la memoria disponible y el grado máximo de paralelismo (MAXDOP) durante la creación del CCI ordenado. Las estrategias siguientes reducen la superposición de segmentos al crear un CCI ordenado.

  • Cree un CCI ordenado con OPTION (MAXDOP = 1). Cada subproceso que se usa para la creación del CCI ordenado trabaja en un subconjunto de datos y lo ordena localmente. No hay ninguna ordenación global entre los datos ordenados por subprocesos diferentes. El uso de subprocesos paralelos puede reducir el tiempo de creación de un CCI ordenado, pero se generarán más segmentos superpuestos que con el uso de un único subproceso. El uso de una sola operación en subproceso ofrece la máxima calidad de compresión. Puede especificar MAXDOP con los CREATE INDEX comandos o CREATE TABLE . Por ejemplo:
CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);
  • Ordene previamente los datos por las claves de ordenación antes de cargarlos en tablas.

El siguiente es un ejemplo de una distribución de tabla de CCI ordenado que no tiene ningún segmento superpuesto tras aplicar las recomendaciones anteriores. El CCI ordenado se ordena en una columna bigint sin duplicados.

Captura de pantalla de los datos de texto que no muestran ninguna superposición de segmentos.

Creación de un CCI ordenado en tablas grandes

La creación de un CCI ordenado es una operación sin conexión. En el caso de tablas sin particiones, los datos no serán accesibles para los usuarios hasta que se complete el proceso de creación del CCI ordenado. En el caso de tablas con particiones, como el motor crea el CCI ordenado partición a partición, los usuarios todavía pueden acceder a los datos de las particiones en las que la creación del CCI ordenado no está en curso. Puede usar esta opción para minimizar el tiempo de inactividad durante la creación del CCI ordenado en tablas grandes:

  1. Cree particiones en la tabla grande de destino (llamada Table_A).
  2. Cree una tabla de CCI ordenado vacía (llamada Table_B) con la misma tabla y esquema de particiones que Table_A.
  3. Cambie una partición de Table_A a Table_B.
  4. Ejecute ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID> para volver a generar la partición cambiada en Table_B.
  5. Repita los pasos 3 y 4 para cada partición de Table_A.
  6. Una vez que todas las particiones se han cambiado de Table_A a Table_B y se han vuelto a generar, elimine Table_A y cambie el nombre de Table_B a Table_A.

Funcionalidades de SQL Server 2022

SQL Server 2022 (16.x) introdujo índices de almacén de columnas en clúster y ordenados de forma similar a la característica de los grupos de SQL dedicados en Azure Synapse.

  • SQL Server 2022 (16.x) y versiones posteriores y otras plataformas SQL admiten funcionalidades mejoradas de eliminación de segmentos agrupados para tipos de datos string, binarios y guid, y el tipo de datos datetimeoffset para la escala superior a dos. Anteriormente, 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 una escala inferior o igual a dos.
  • Actualmente, solo SQL Server 2022 (16.x) y versiones posteriores y otras plataformas SQL admiten la eliminación de grupos de filas de almacén de columnas en clúster para el prefijo de LIKE predicados, por ejemplo column LIKE 'string%'. No se admite la eliminación de segmentos para el uso sin prefijo de LIKE, como column LIKE '%string'.

Para obtener disponibilidad ordenada del índice de almacén de columnas, consulte Disponibilidad de índices de columna ordenada.

Para obtener más información, consulte Novedades de los índices de almacén de columnas.

Para obtener información sobre los índices de almacén de columnas ordenados en grupos de SQL dedicados en Azure Synapse Analytics, consulte Optimización del rendimiento con índices de almacén de columnas agrupados ordenados.

Ejemplos

A. Para comprobar las columnas ordenadas y el ordinal del orden:

SELECT object_name(c.object_id) table_name, c.name column_name, i.column_store_order_ordinal
FROM sys.index_columns i
JOIN sys.columns c ON i.object_id = c.object_id AND c.column_id = i.column_id
WHERE column_store_order_ordinal <>0;

B. Para cambiar el ordinal de la columna, agregar o eliminar columnas de la lista de ordenación o cambiar de CCI a CCI ordenado:

CREATE CLUSTERED COLUMNSTORE INDEX InternetSales ON dbo.InternetSales
ORDER (ProductKey, SalesAmount)
WITH (DROP_EXISTING = ON);

Contenido relacionado