Ajuste de desempenho com índices columnstore ordenados

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada de SQL do Azurebanco de dados SQL no Microsoft Fabric

Índices columnstore ordenados podem fornecer um desempenho mais eficiente, ignorando grandes quantidades de dados ordenados que não correspondem ao predicado da consulta. Enquanto carregar dados em um índice columnstore ordenado e manter a ordem por meio da recompilação de índice leva mais tempo do que em um índice não ordenado, as consultas indexadas podem ser executadas mais rapidamente com columnstore ordenado.

Quando uma consulta lê um índice columnstore, o Mecanismo de Banco de Dados verifica os valores mínimos e máximos armazenados em cada segmento de coluna. O processo elimina segmentos que estão fora dos limites do predicado de consulta. Em outras palavras, ele ignora esses segmentos ao ler dados do disco ou da memória. Uma consulta é concluída mais rapidamente se o número de segmentos a serem lidos e seu tamanho total for significativamente menor.

Com determinados padrões de carga de dados, os dados em um índice columnstore já podem ser ordenados. Por exemplo, se os carregamentos de dados ocorrerem todos os dias, os dados poderão ser ordenados por uma load_date coluna. Nesse caso, o desempenho da consulta já pode se beneficiar dessa ordem implícita. Ordenar o índice columnstore pela mesma load_date coluna explicitamente não é provável que forneça um benefício de desempenho extra.

Para saber sobre a disponibilidade do índice columnstore ordenado em várias plataformas SQL e versões do SQL Server, consulte Ordered columnstore index availability.

Para obter mais informações sobre os recursos recentemente adicionados aos índices columnstore, consulte Novidades nos índices columnstore.

Índice columnstore ordenado versus não ordenado

Em um índice columnstore, os dados de cada coluna de cada grupo de linhas são compactados em segmentos individuais. Cada segmento contém metadados que descrevem seus valores mínimos e máximos, de modo que o processo de execução da consulta pode ignorar segmentos que estão fora dos limites do predicado de consulta.

Quando um índice columnstore não é ordenado, o construtor de índices não classifica os dados antes de compactá-los em segmentos. Isso significa que segmentos com intervalos de valores sobrepostos podem ocorrer, fazendo com que as consultas leiam mais segmentos para obter os dados necessários. Como resultado, as consultas podem levar mais tempo para serem concluídas.

Quando você cria um índice columnstore ordenado, o Mecanismo de Banco de Dados classifica os dados existentes pelas chaves de pedido especificadas antes que o construtor de índice os compacte em segmentos. Com os dados classificados, a sobreposição de segmento é reduzida ou eliminada, permitindo que as consultas usem uma eliminação de segmento mais eficiente e, portanto, um desempenho mais rápido, pois há menos segmentos e menos dados a serem lidos.

Reduzir sobreposição de segmento

Quando você cria um índice columnstore ordenado, o Mecanismo de Banco de Dados classifica os dados de forma otimizada. Dependendo da memória disponível, do tamanho dos dados, do grau de paralelismo, do tipo de índice (clusterizado ou não clusterizado) e do tipo de construção de índice (offline ou online), a ordenação de índices columnstore pode ser completa, sem sobreposição de segmentos, ou parcial, com alguma sobreposição de segmentos.

A tabela a seguir descreve o tipo de classificação resultante ao criar ou recriar um índice columnstore ordenado, dependendo das opções de construção de índice.

No primeiro caso, quando ambos ONLINE = ON e MAXDOP = 1, a classificação não é limitada pela memória da área de trabalho de consulta porque uma geração online de um índice columnstore ordenado utiliza o banco de dados tempdb para transferir os dados que não cabem na memória. Essa abordagem pode tornar o processo de construção do índice mais lento devido à E/S adicional tempdb. No entanto, como o build de índice é executado online, as consultas podem continuar usando o índice existente enquanto o novo índice ordenado está sendo compilado.

Da mesma forma, com uma recompilação offline de um índice columnstore particionado, a recompilação é feita uma partição por vez. Outras partições permanecem disponíveis para consultas.

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

Você pode criar ou recriar índices columnstore ordenados online somente em algumas plataformas SQL e versões do SQL Server. Para obter mais informações, consulte o resumo de recursos das versões de produto.

No SQL Server, as operações de índice online não estão disponíveis em todas as edições. Para obter mais informações, consulte Edições e recursos com suporte do SQL Server 2025 e execute operações de índice online.

Adicionar novos dados ou atualizar dados existentes

Os novos dados resultantes de um lote DML ou de uma operação de carregamento em massa em um índice columnstore ordenado são classificados somente nesse lote. Não há classificação global que inclua dados existentes na tabela. Para reduzir as sobreposições de segmento depois de inserir novos dados ou atualizar dados existentes, recompile o índice.

Desempenho de consulta

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

As consultas com os seguintes padrões normalmente são executadas mais rapidamente com índices columnstore ordenados:

• Consultas que têm predicados de igualdade, desigualdade ou intervalo.
• Consultas em que as colunas de predicado 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 se beneficiar mais do índice columnstore ordenado do que as consultas 3 e 4, pois 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 em uma tabela com um índice columnstore ordenado é semelhante a uma tabela particionada. O carregamento de dados pode levar mais tempo do que com um índice columnstore não ordenado devido à operação de classificação de dados, mas as consultas podem ser executadas mais rapidamente posteriormente.

Examples

Criar um índice columnstore ordenado

Índice columnstore ordenado clusterizado:

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

Índice columnstore ordenado não clusterizado:

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

Verificar se há colunas ordenadas e ordinal de classificação

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 classificação e recompilar um índice columnstore ordenado existente

Índice columnstore ordenado clusterizado:

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

Índice columnstore ordenado não clusterizado:

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 classificação completa em uma tabela de heap

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

Recompilar um índice columnstore clusterizado 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 classificação de um índice columnstore ordenado

Este exemplo determina a qualidade de ordenação para todos os índices columnstore ordenados no banco de dados. Neste exemplo, a qualidade de classificação é definida como a proporção de segmentos não sobrepostos em todos os segmentos para cada coluna de ordenação, expressa como uma porcentagem.

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 columnstore – diretrizes de carregamento de dados
• Introdução aos índices columnstore para análise operacional em tempo real
• Índices columnstore no data warehousing
• Otimizar a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos
• Arquitetura de índices columnstore
• CRIAR ÍNDICE (Transact-SQL)
• ALTER INDEX (Transact-SQL)