Ajuste de desempenho com índices columnstore ordenados

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores Azure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

Os índices ordenados em columnstore podem proporcionar um desempenho mais rápido ao ignorar grandes quantidades de dados ordenados que não correspondem ao predicado da consulta. Embora carregar dados num índice columnstore ordenado e manter a ordem através da reconstrução do índice demore mais tempo do que num índice não ordenado, as consultas indexadas podem executar mais rapidamente com um índice columnstore ordenado.

Quando uma consulta lê um índice de colonstore, o Motor de Base de Dados verifica os valores mínimos e máximos armazenados em cada segmento da coluna. O processo elimina segmentos que ficam fora dos limites do predicado da consulta. Ou seja, ignora estes segmentos ao ler dados do disco ou da memória. Uma consulta termina mais rapidamente se o número de segmentos a ler e o seu tamanho total forem significativamente menores.

Com certos padrões de carga de dados, os dados num índice de coluna podem já estar ordenados. Por exemplo, se as cargas de dados ocorrerem todos os dias, então os dados podem ser ordenados por coluna load_date . Neste caso, o desempenho da consulta já pode beneficiar desta ordem implícita. Ordenar explicitamente o índice da coluna pela mesma load_date coluna dificilmente trará um benefício extra de desempenho.

Para verificar a disponibilidade do índice ordenado de columnstore em várias plataformas SQL e versões do SQL Server, veja Disponibilidade do índice ordenado de columnstore.

Para obter mais informações sobre as funcionalidades recentemente adicionadas aos índices columnstore, consulte O que há de novo nos índices columnstore.

Índice columnstore ordenado vs. não ordenado

Num índice de columnstore, os dados em cada coluna de cada grupo de linhas são comprimidos em segmentos separados. Cada segmento contém metadados que descrevem os seus valores mínimo e máximo, pelo que o processo de execução da consulta pode saltar segmentos que se enquadram fora dos limites do predicado da consulta.

Quando um índice columnstore não está ordenado, o construtor de índices não ordena os dados antes de os comprimir em segmentos. Isto significa que segmentos com intervalos de valores sobrepostos podem ocorrer, levando as consultas a ler mais segmentos para obter os dados necessários. Como resultado, as consultas podem demorar mais tempo a ser concluídas.

Quando você cria um índice columnstore ordenado, o Mecanismo de Banco de Dados classifica os dados existentes pelas chaves de ordem especificadas antes que o construtor de índices os compacte em segmentos. Com dados ordenados, a sobreposição de segmentos é reduzida ou eliminada, permitindo que as consultas utilizem uma eliminação de segmentos mais eficiente e, assim, um desempenho mais rápido porque há menos segmentos e menos dados para ler.

Reduzir a sobreposição de segmentos

Quando cria um índice de columnstore ordenado, o motor da base de dados ordena os dados com base no máximo esforço. Dependendo da memória disponível, do tamanho dos dados, do grau de paralelismo, do tipo de índice (agrupado vs. não agrupado) e do tipo de construção do índice (offline vs. online), a ordenação dos índices ordenados de colunas pode ser cheia sem sobreposição de segmentos, ou parcial com alguma sobreposição de segmentos.

A tabela seguinte descreve o tipo de ordenação resultante quando cria ou reconstrói um índice ordenado de columnstore, dependendo das opções de construção do índice.

No primeiro caso, quando tanto ONLINE = ON como MAXDOP = 1, a ordenação não está limitada pela memória do espaço de trabalho de consulta porque uma construção online de um índice ordenado de coluna usa a tempdb base de dados para divulgar os dados que não cabem na memória. Esta abordagem pode tornar o processo de construção do índice mais lento devido ao I/O adicional tempdb . No entanto, como a construção do índice é feita online, as consultas podem continuar a usar o índice existente enquanto o novo índice ordenado está a ser construído.

De forma semelhante, com uma reconstrução offline de um índice de Columnstore particionado, a reconstrução é feita uma partição de cada vez. Outras partições permanecem disponíveis para consultas.

Quando MAXDOP é maior que 1, cada thread usado para compilação de índice columnstore ordenada funciona em um subconjunto de dados e os classifica localmente. Não há classificação global entre dados classificados por threads diferentes. Usar threads paralelos pode reduzir o tempo para criar o índice, mas resulta em mais segmentos sobrepostos do que ao usar um único thread.

Pode criar ou reconstruir índices de colunas ordenados online apenas em algumas plataformas e versões do SQL Server. Para mais informações, consulte Resumo de funcionalidades para lançamentos de produtos.

No SQL Server, as operações de índice online não estão disponíveis em todas as edições. Para mais informações, consulte Edições e funcionalidades suportadas do SQL Server 2025 e Executar operações de índice online.

Adicionar novos dados ou atualizar dados existentes

Os novos dados resultantes de um batch DML ou de uma operação de carregamento em massa num índice ordenado de columnstore são ordenados apenas dentro desse lote. Não há classificação global que inclua dados existentes na tabela. Para reduzir sobreposições de segmentos após inserir novos dados ou atualizar dados existentes, reconstrua o índice.

Desempenho de consulta

O ganho de desempenho de um índice ordenado de coluna depende dos padrões de consulta, do tamanho dos dados, da qualidade da ordenação e dos recursos computacionais disponíveis para execução de consultas.

Consultas com os seguintes padrões normalmente correm mais rapidamente com índices ordenados de colunas de armazenamento:

• Consultas que têm predicados de igualdade, desigualdade ou de intervalo.
• Consultas onde as colunas de predicados e as colunas CCI ordenadas são as mesmas.

Neste exemplo, a tabela T1 tem um índice columnstore clusterizado ordenado na sequência de Col_C, Col_Be Col_A.

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

O desempenho das consultas 1 e 2 pode beneficiar-se do índice columnstore ordenado mais do que das consultas 3 e 4, porque elas fazem referência a todas as colunas 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';

Desempenho da carga de dados

O desempenho de uma carga de dados numa tabela com um índice ordenado de columnstore é semelhante ao de uma tabela particionada. Carregar dados pode demorar mais tempo do que com um índice de coluna não ordenado devido à operação de ordenação de dados, mas as consultas podem correr mais rapidamente depois.

Examples

Criar um índice columnstore ordenado

Índice columnstore ordenado em cluster:

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

Índice não clusterizado ordenado de armazenamento em coluna:

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

Verifique se há colunas ordenadas e a ordem 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;

Adicionar ou remover colunas de ordem e reconstruir um índice columnstore ordenado existente

Índice columnstore ordenado em cluster:

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

Índice não clusterizado ordenado de armazenamento em coluna:

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

Criar um índice columnstore clusterizado ordenado online com ordenamento completo em uma tabela de heap

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

Reconstruir um índice columnstore agrupado ordenado online com classificação completa

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

Determinar a qualidade de ordenação para um índice columnstore ordenado

Este exemplo determina a qualidade de ordenação para todos os índices ordenados de colunas na base de dados. Neste exemplo, a qualidade de ordenação é definida como uma razão de segmentos não sobrepostos para o total de segmentos em cada coluna de ordenação, expressa como uma percentagem.

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;

Conteúdo relacionado

• Diretrizes de design de índice Columnstore
• Índices de armazenamento em coluna - orientação de carregamento de dados
• Introdução aos índices columnstore para análise operacional em tempo real
• Índices de Columnstore em armazenamento de dados
• Otimize a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos
• Arquitetura de índice Columnstore
• CRIAR ÍNDICE (Transact-SQL)
• ALTERAR ÍNDICE (Transact-SQL)