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

Ao permitir a eliminação eficiente de segmentos, os índices columnstore ordenados fornecem um desempenho mais rápido ignorando grandes quantidades de dados ordenados que não correspondem ao predicado de consulta. Carregar dados em um índice columnstore ordenado e mantê-lo ordenado por meio de reconstruções de índice pode levar mais tempo do que em um índice não ordenado devido à operação de classificação de dados, no entanto, com índices columnstore ordenados, as consultas podem ser executadas mais rapidamente depois.

Quando os usuários consultam uma tabela columnstore, o otimizador verifica os valores mínimo e máximo armazenados em cada segmento. Os segmentos que estão fora dos limites do predicado de consulta não são lidos do disco para a memória. Uma consulta pode ser concluída mais rapidamente se o número de segmentos a ler e o seu tamanho total forem menores.

Para obter informações sobre a disponibilidade do índice columnstore ordenado, consulte Disponibilidade do índice columnstore ordenado.

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

Em um índice columnstore, os dados em cada coluna de cada grupo de linhas são compactados em um segmento separado. Cada segmento contém metadados que descrevem seus valores mínimo e máximo, portanto, os segmentos que estão fora dos limites do predicado de consulta não são lidos do disco durante a execução da 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 do disco e levem 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 ordem especificadas antes que o construtor de índices os compacte em segmentos. Com dados classificados, a sobreposição de segmentos é reduzida ou eliminada, permitindo que as consultas tenham uma eliminação de segmento mais eficiente e, portanto, um desempenho mais rápido porque há menos segmentos para ler do disco.

Dependendo da memória disponível, do tamanho dos dados, do grau de paralelismo, do tipo de índice (clusterizado vs. não clusterizado) e do tipo de compilação de índice (offline vs. online), a classificação para índices columnstore ordenados pode ser completa (sem sobreposição de segmento) ou parcial (alguma sobreposição de segmento). Por exemplo, a classificação parcial ocorre quando a memória disponível é insuficiente para uma classificação completa. As consultas que usam um índice columnstore ordenado geralmente são executadas mais rapidamente do que com um índice não ordenado, mesmo que o índice ordenado tenha sido criado usando uma classificação parcial.

A classificação completa é fornecida para índices ordenados columnstore agrupados que são criados ou reconstruídos com as opções ONLINE = ON e MAXDOP = 1. Nesse caso, a classificação não é limitada pela memória disponível porque usa o banco de dados tempdb para derramar os dados que não cabem na memória. Isso pode tornar o processo de compilação do índice mais lento devido a uma operação de entrada/saída adicional de tempdb. No entanto, com uma reconstrução de índice online, as consultas podem continuar usando o índice existente enquanto o novo índice ordenado está sendo reconstruído.

A classificação completa também pode ser fornecida para índices columnstore agrupados e não clusterizados ordenados criados ou reconstruídos com as opções ONLINE = OFF e MAXDOP = 1 se a quantidade de dados a serem classificados for suficientemente pequena para caber totalmente na memória disponível.

Em todos os outros casos, a classificação em índices columnstore ordenados é parcial.

Note

Atualmente, os índices ordenados de colunas podem ser criados ou reconstruídos online apenas no Azure SQL Database, no Azure SQL Managed InstanceAUTD e no SQL Server 2025 (17.x).

Para verificar os intervalos de segmentos de uma coluna e verificar se há alguma sobreposição de segmento, use a seguinte consulta, substituindo marcadores de posição pelos nomes de esquema, tabela e coluna:

SELECT OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
       o.name AS table_name,
       cols.name AS column_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
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 OBJECT_SCHEMA_NAME(o.object_id) = '<Schema Name>'
      AND
      o.name = '<Table Name>'
      AND
      cols.name = '<Column Name>'
ORDER BY o.name, pnp.index_id, cls.min_data_id;

Por exemplo, a saída desta consulta para um índice de armazenamento em coluna completamente ordenado pode ter a seguinte aparência. Observe que não há sobreposição nas colunas min_data_id e max_data_id para segmentos diferentes.

schema_name table_name column_name index_id row_count data_compression_desc segment_id column_id min_data_id max_data_id
----------- ---------- ----------- -------- --------- --------------------- ---------- --------- ----------- -----------
dbo         Table1     Column1     1        479779    COLUMNSTORE           0          1         -17         1469515
dbo         Table1     Column1     1        887658    COLUMNSTORE           1          1         1469516     2188146
dbo         Table1     Column1     1        930144    COLUMNSTORE           2          1         2188147     11072928

Note

Num índice columnstore ordenado, os novos dados resultantes do mesmo lote de operações de DML ou carregamento de dados são ordenados apenas dentro desse lote. Não há classificação global que inclua dados existentes na tabela.

Para classificar dados no índice depois de inserir novos dados ou atualizar dados existentes, recrie o índice.

Para uma reconstrução offline de um índice columnstore particionado, a reconstrução é feita uma partição de cada vez. Os dados na partição que está sendo reconstruída não estarão disponíveis até que a reconstrução seja concluída para essa partição.

Os dados permanecem disponíveis durante uma reconstrução online. Para obter mais informações, consulte Executar operações de índice online.

Desempenho de consulta

O ganho de desempenho de um índice columnstore ordenado depende dos padrões de consulta, do tamanho dos dados, da forma como os dados são classificados, da estrutura física dos segmentos e dos recursos de computação disponíveis para a execução da consulta.

As consultas que apresentam os seguintes padrões costumam ser executadas mais rapidamente com índices columnstore ordenados.

  • 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 da 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, no entanto, as consultas podem ser executadas mais rapidamente depois.

Reduzir a sobreposição de segmentos

O número de segmentos sobrepostos depende do tamanho dos dados a ordenar, da memória disponível e do grau máximo de paralelismo (MAXDOP) definido durante a construção do índice columnstore ordenado. As estratégias a seguir reduzem a sobreposição de segmentos, no entanto, podem fazer com que o processo de construção do índice demore mais.

  • Se a construção de índice online estiver disponível, use as opções ONLINE = ON e MAXDOP = 1 ao criar um índice columnstore clusterizado ordenado. Isso cria um índice totalmente classificado.
  • Se a compilação de índice online não estiver disponível, use a opção MAXDOP = 1.
  • Pré-classifique os dados pelas chaves de classificação antes do carregamento.

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. O uso de threads paralelos pode reduzir o tempo para criar o índice, mas gera mais segmentos sobrepostos do que ao usar um único thread. O uso de uma operação de encadeamento único oferece a mais alta qualidade de compressão. Você pode especificar MAXDOP com o comando CREATE INDEX.

Examples

Verifique se há colunas ordenadas e a ordem ordinal.

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

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

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

Conteúdo relacionado

  • Last updated on