Compartilhar via

Ajuste de desempenho com índices columnstore ordenados

  • Artigo

Aplica-se a: SQL Server 2022 (16.x) Banco de Dados SQL do AzureInstância Gerenciada de SQL do AzureBanco de dados SQL no Microsoft Fabric

Ao permitir a eliminação eficiente de segmentos, os índices columnstore ordenados oferecem um desempenho mais rápido ao ignorar grandes quantidades de dados ordenados que não correspondem ao predicado da consulta. Carregar dados em um índice columnstore ordenado e mantê-los ordenados por meio de recompilaçõ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 consultas de índices columnstore ordenados podem ser executadas mais rapidamente posteriormente.

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 poderá ser concluída mais rapidamente se o número de segmentos a serem lidos e seu tamanho total for menor.

Para obter a disponibilidade do índice columnstore ordenado, confira a Disponibilidade do índice columnstore ordenado.

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 em cada coluna de cada grupo de linhas são compactados em segmentos separados. Cada segmento contém metadados que descrevem seus valores mínimos e máximos, de modo que 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 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 tenham uma eliminação de segmento mais eficiente e, portanto, um desempenho mais rápido porque há menos segmentos a serem lidos do disco.

Dependendo da memória disponível, do tamanho dos dados, do grau de paralelismo, do tipo de índice (clusterizado versus não clusterizado) e do tipo de build de índice (offline versus online), a classificação para índices columnstore ordenados pode estar 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 columnstore clusterizados ordenados criados ou recriados 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 se encaixam na memória. Isso pode tornar o processo de build de índice mais lento devido à E/S tempdb adicional. No entanto, com uma recompilação de índice online, as consultas podem continuar usando o índice existente enquanto o novo índice ordenado está sendo recriado.

A classificação completa também pode ser fornecida para índices columnstore ordenados, tanto clusterizados quanto não clusterizados, que foram criados ou recriados 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.

Observação

Atualmente, os índices columnstore ordenados podem ser criados ou recriados online somente no Banco de Dados SQL do Azure e na Instância Gerenciada de SQL do Azure com a Política de atualização de manter sempre atualizado.

Para verificar os intervalos de segmento de uma coluna e determinar se há sobreposição de segmentos, use a seguinte consulta, substituindo os espaços reservados pelos seus 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 dessa consulta para um índice columnstore totalmente classificado pode ser a seguinte. 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

Observação

Em um índice columnstore ordenado, os novos dados resultantes do mesmo lote de operações de carregamento de dados ou DML são classificados somente nesse 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, recompile o índice.

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

Os dados permanecem disponíveis durante uma recompilaçã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, do quão bem os dados são classificados, da estrutura física dos segmentos e dos recursos de computação disponíveis para execução da consulta.

As consultas com os seguintes padrões geralmente são executadas mais rapidamente quando utilizam í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 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 posteriormente.

Reduzir a sobreposição de segmentos

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

  • Se o build 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 o build de índice online não estiver disponível, use a opção MAXDOP = 1.
  • Pré-classifique os dados pelas chaves de classificação antes da carga.

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 gera mais segmentos sobrepostos do que ao usar um único thread. O uso de uma única operação threaded oferece a maior qualidade de compactação. Você pode especificar MAXDOP com o comando CREATE INDEX.

Exemplos

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

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

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

Conteúdo relacionado