Partilhar via

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 carregamento de dados, os dados num índice de coluna podem ser ordenados implicitamente sem especificar a ORDER cláusula. 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 o índice da coluna pela mesma load_date coluna explicitamente na ORDER cláusula 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 cria um índice ordenado de columnstore especificando a cláusula ORDER na instrução CREATE COLUMNSTORE INDEX, o Motor de Base de Dados ordena os dados em cada segmento de cada coluna de ordem antes de o construtor de índice comprimir os dados 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 e melhorar o desempenho das consultas

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 clusterizado) e do tipo de construção do índice (offline vs. online), a ordem numa coluna num índice de coluna pode ser completa sem sobreposição de segmentos, ou parcial com alguma sobreposição de segmentos. Quando há menos segmentos sobrepostos, uma consulta que pode tirar partido da ordem das colunas é executada mais rapidamente.

Sugestão

Mesmo que a ordem numa coluna de um índice de coluna seja parcial, os segmentos ainda podem ser eliminados (ignorados). Não é necessária uma ordem completa para obter benefícios de desempenho se uma ordem parcial evitar muitas sobreposições de segmentos.

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

Pré-requisitos Tipo de encomenda
ONLINE = ON e MAXDOP = 1 Completo
ONLINE = OFF, MAXDOP = 1, e os dados a ordenar cabem totalmente na memória do workspace de consulta Completo
Todos os outros casos Parcial

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 à E/S adicional tempdb , e requer espaço livre suficiente em 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.

Para certos tipos de dados e codificações, a vista do sistema sys.column_store_segments pode ajudar a encontrar o número de sobreposições de segmentos. Um script de exemplo baseado nesta vista determina a qualidade da ordem para as colunas elegíveis de todos os índices de columnstore na base de dados atual.

Desempenho de consulta

O ganho de desempenho de um índice ordenado de coluna depende dos padrões de consulta, do tamanho dos dados, do número de segmentos sobrepostos 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.

No exemplo seguinte, a tabela T1 tem um índice agrupado de columnstore com Col_C, Col_B, e Col_A como colunas ordenadas.

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

A Consulta 1 beneficia mais do índice ordenado de colunas do que as consultas 2 e 3, porque a Consulta 1 faz referência a todas as colunas ordenadas do seu predicado.

-- 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_A = 'a';

-- query 3
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.

Adicionar novos dados ou atualizar dados existentes

Os novos dados resultantes de um lote de DML ou de uma operação de carregamento em massa numa tabela com um índice columnstore ordenado são ordenados apenas nesse lote. Não existe uma ordenação global que inclua dados existentes na tabela porque os grupos de linhas comprimidos num índice de column store são imutáveis.

Para reduzir a sobreposição de segmentos após inserir novos dados ou atualizar dados existentes, reconstrua o índice de columnstore.

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);

Crie um índice ordenado de clusters de colunas online com a ordem completa numa tabela de heap

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

Reconstruir online um índice columnstore agrupado ordenado com ordem completa

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

Conteúdo relacionado

  • Last updated on