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

Se aplica a: SQL Server 2022 (16.x) y versiones posteriores Azure SQL Database Azure SQL Managed Instance Base de datos SQL en Microsoft Fabric

Los índices de almacén de columnas ordenados pueden proporcionar un rendimiento más rápido omitiendo grandes cantidades de datos ordenados que no coinciden con el predicado de consulta. Al cargar datos en un índice de almacén de columnas ordenado y mantener el orden a través de la recompilación de índices tarda más tiempo que en un índice no ordenado, las consultas indexadas se pueden ejecutar más rápido con el almacén de columnas ordenado.

Cuando una consulta lee un índice de almacén de columnas, el motor de base de datos comprueba los valores mínimos y máximos almacenados en cada segmento de columna. El proceso elimina los segmentos que se encuentran fuera de los límites del predicado de consulta. En otras palabras, omite estos segmentos al leer datos de disco o memoria. Una consulta finaliza más rápido si el número de segmentos que se van a leer y su tamaño total es significativamente menor.

Con determinados patrones de carga de datos, es posible que los datos de un índice de almacén de columnas ya estén ordenados. Por ejemplo, si las cargas de datos se producen todos los días, los datos se pueden ordenar por una load_date columna. En este caso, el rendimiento de las consultas ya puede beneficiarse de este orden implícito. No es probable que ordenar el índice de almacén de columnas por la misma load_date columna proporcione explícitamente un beneficio adicional de rendimiento.

Para obtener disponibilidad ordenada de índices de almacén de columnas en varias plataformas SQL y versiones de SQL Server, consulte Disponibilidad ordenada del índice de almacén de columnas.

Para obtener más información sobre las nuevas características agregadas a los índices columnstore, consulte Novedades en los índices columnstore.

Índice de almacén de columnas ordenado frente a no ordenado

En un índice de almacén de columnas, los datos de cada columna de cada grupo de filas se comprimen en un segmento independiente. Cada segmento contiene metadatos que describen sus valores mínimos y máximos, por lo que el proceso de ejecución de consultas puede omitir segmentos que se encuentran fuera de los límites del predicado de consulta.

Cuando no se ordena un índice de almacén de columnas, el generador de índices no ordena los datos antes de comprimirlos en segmentos. Esto significa que pueden producirse segmentos con intervalos de valores superpuestos, lo que hace que las consultas lean más segmentos para obtener los datos necesarios. Como resultado, las consultas pueden tardar más tiempo en finalizar.

Al crear un índice de almacén de columnas ordenado, el motor de base de datos ordena los datos existentes por las claves de pedido que especifique antes de que el generador de índices los comprima en segmentos. Con los datos ordenados, la superposición de segmentos se reduce o elimina, lo que permite a las consultas usar una eliminación de segmentos más eficaz y, por tanto, un rendimiento más rápido porque hay menos segmentos y menos datos que leer.

Reducción de la superposición de segmentos

Al crear un índice de almacén de columnas ordenado, el motor de base de datos ordena los datos de forma más eficaz. Dependiendo de la memoria disponible, el tamaño de los datos, el grado de paralelismo, el tipo de índice (agrupado frente a no agrupado) y el tipo de construcción del índice (offline frente a online), la ordenación de los índices columnstore ordenados puede ser completa sin superposición de segmentos o parcial con cierta superposición de segmentos.

En la tabla siguiente se describe el tipo de ordenación resultante al crear o recompilar un índice de almacén de columnas ordenado, en función de las opciones de compilación del índice.

Prerrequisitos	Tipo de ordenación
`ONLINE = ON` y `MAXDOP = 1`	Completo
`ONLINE = OFF`, `MAXDOP = 1`, y los datos que se van a ordenar se ajustan completamente a la memoria del área de trabajo de consulta.	Completo
Todos los demás casos	Parcial

En el primer caso, cuando ambos ONLINE = ON y MAXDOP = 1, la ordenación no está limitada por la memoria del área de trabajo de consulta porque una construcción en línea de un índice columnstore ordenado utiliza la base de datos tempdb para volcar los datos que no caben en la memoria. Este enfoque puede hacer que el proceso de construcción del índice sea más lento debido a la E/S adicional tempdb. Sin embargo, dado que la compilación del índice se realiza en línea, las consultas pueden seguir usando el índice existente mientras se compila el nuevo índice ordenado.

De forma similar, en una reconstrucción sin conexión de un índice de columnas almacenado por particiones, la reconstrucción se realiza una partición a la vez. Otras particiones siguen estando disponibles para las consultas.

Cuando MAXDOP es mayor que 1, cada subproceso utilizado para la creación del índice de almacén de columnas 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 para crear el índice, pero da como resultado más segmentos superpuestos que cuando se usa un único subproceso.

Sugerencia

Incluso si la ordenación de un índice de almacén de columnas ordenado es parcial, los segmentos pueden omitirse. No se requiere una ordenación completa para obtener ventajas de rendimiento de consultas si una ordenación parcial evita muchas superposiciones de segmento.

Para buscar el número de segmentos superpuestos y no superpuestos en un índice de almacén de columnas ordenado, consulte el ejemplo Determinación de la calidad de ordenación de un índice de almacén de columnas ordenado .

Puede crear o recompilar índices de almacén de columnas ordenados en línea solo en algunas plataformas SQL y versiones de SQL Server. Para obtener más información, consulte Resumen de características para versiones de productos.

En SQL Server, las operaciones de índice en línea no están disponibles en todas las ediciones. Para obtener más información, vea Ediciones y características admitidas de SQL Server 2025 y Realización de operaciones de índice en línea.

Agregar nuevos datos o actualizar los datos existentes

Los nuevos datos resultantes de un lote DML o una operación de carga masiva en un índice de almacén de columnas ordenado se ordenan solo dentro de ese lote. No hay ninguna ordenación global que incluya los datos existentes en la tabla. Para reducir las superposiciones de segmento después de insertar nuevos datos o actualizar los datos existentes, vuelva a generar el índice.

Rendimiento de las consultas

La ganancia de rendimiento de un índice de almacén de columnas ordenado depende de los patrones de consulta, el tamaño de los datos, la calidad de ordenación y los recursos de proceso disponibles para la ejecución de consultas.

Las consultas con los patrones siguientes normalmente se ejecutan más rápido con índices de almacén de columnas ordenados:

Consultas que tienen predicados de igualdad, desigualdad o rango.
Consultas en las que las columnas de predicado y las columnas CCI ordenadas 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 las consultas 1 y 2 puede beneficiarse más del índice columnstore ordenado que las consultas 3 y 4, ya que hacen referencia a todas las columnas 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 una carga de datos en una tabla con un índice de almacén de columnas ordenado es similar a una tabla con particiones. La carga de datos puede tardar más tiempo que con un índice de almacén de columnas no ordenado debido a la operación de ordenación de datos, pero las consultas se pueden ejecutar más rápido después.

Examples

Creación de un índice de almacén de columnas ordenado

Índice de almacén de columnas ordenado agrupado:

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER(Column1, Column2);

Índice de almacén de columnas ordenado no agrupado:

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1(Column1, Column2, Column3)
ORDER(Column1, Column2);

Buscar columnas ordenadas y ordinal

SELECT OBJECT_SCHEMA_NAME(c.object_id) AS schema_name,
       OBJECT_NAME(c.object_id) AS table_name,
       c.name AS column_name,
       i.column_store_order_ordinal
FROM sys.index_columns AS i
     INNER JOIN sys.columns AS c
         ON i.object_id = c.object_id
        AND c.column_id = i.column_id
WHERE column_store_order_ordinal > 0;

Agregar o quitar columnas de pedido y recompilar un índice de almacén de columnas ordenado existente

Índice de almacén de columnas ordenado agrupado:

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER(Column1, Column2)
WITH (DROP_EXISTING = ON);

Índice de almacén de columnas ordenado no agrupado:

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1(Column1, Column2, Column3)
ORDER(Column1, Column2)
WITH (DROP_EXISTING = ON);

Creación de un índice de almacén de columnas agrupado ordenado en línea con ordenación completa en una tabla de montón

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER(Column1)
WITH (ONLINE = ON, MAXDOP = 1);

Recompilación de un índice de almacén de columnas agrupado ordenado en línea con una ordenación completa

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER(Column1)
WITH (DROP_EXISTING = ON, ONLINE = ON, MAXDOP = 1);

Determinar la calidad de ordenación de un índice de almacén de columnas ordenado

En este ejemplo se determina la calidad de la ordenación de todos los índices de columnstore ordenados en la base de datos. En este ejemplo, la calidad de ordenación se define como una relación de segmentos no superpuestos a todos los segmentos de cada columna de pedido, expresada como un porcentaje.

WITH ordered_column_segment
AS (SELECT p.object_id,
           i.name AS index_name,
           ic.column_store_order_ordinal,
           cls.row_count,
           cls.column_id,
           cls.min_data_id,
           cls.max_data_id,
           LAG(max_data_id) OVER (
               PARTITION BY cls.partition_id, ic.column_store_order_ordinal
               ORDER BY cls.min_data_id
           ) AS prev_max_data_id,
           LEAD(min_data_id) OVER (
               PARTITION BY cls.partition_id, ic.column_store_order_ordinal
               ORDER BY cls.min_data_id
           ) AS next_min_data_id
    FROM sys.partitions AS p
         INNER JOIN sys.indexes AS i
             ON p.object_id = i.object_id
            AND p.index_id = i.index_id
         INNER JOIN sys.column_store_segments AS cls
             ON p.partition_id = cls.partition_id
         INNER JOIN sys.index_columns AS ic
             ON ic.object_id = p.object_id
            AND ic.index_id = p.index_id
            AND ic.column_id = cls.column_id
    WHERE ic.column_store_order_ordinal > 0)
SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       OBJECT_NAME(object_id) AS object_name,
       index_name,
       INDEXPROPERTY(object_id, index_name, 'IsClustered') AS is_clustered_column_store,
       COL_NAME(object_id, column_id) AS order_column_name,
       column_store_order_ordinal,
       SUM(row_count) AS row_count,
       SUM(is_overlapping_segment) AS overlapping_segments,
       COUNT(1) AS total_segments,
       (1 - SUM(is_overlapping_segment) / COUNT(1)) * 100 AS order_quality_percent
FROM ordered_column_segment
CROSS APPLY (SELECT CAST (IIF (prev_max_data_id > min_data_id
                 OR next_min_data_id < max_data_id, 1, 0) AS FLOAT) AS is_overlapping_segment
            ) AS ios
GROUP BY object_id, index_name, column_id, column_store_order_ordinal
ORDER BY schema_name, object_name, index_name, column_store_order_ordinal;

Ohar-bidaltzea

Lagungarria izan al da orrialde hau?

Last updated on 2025-12-29