Ajuste de desempenho com índices columnstore clusterizados ordenados

  • Artigo

Aplica-se a: SQL Server 2022 (16.x) Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Ao habilitar a eliminação eficiente de segmentos, os CCI (índices columnstore) clusterizados ordenados fornecem um desempenho muito mais rápido, ignorando grandes quantidades de dados ordenados que não correspondem ao predicado de consulta. Carregar dados em uma tabela de CCI ordenado pode levar mais tempo do que em uma tabela de CCI não ordenado devido à operação de classificação de dados, no entanto, as consultas podem ser executadas posteriormente de maneira mais rápida com o CCI ordenado.

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 terminar mais rapidamente, se o número de segmentos a serem lidos e o tamanho total forem pequenos.

Para obter a disponibilidade do índice columnstore ordenado, consulte Disponibilidade do índice de coluna ordenada.

Comparação entre os índices columnstore clusterizados ordenado e não ordenado

Por padrão, um componente interno (construtor de índice) cria um CCI (índice columnstore clusterizado) não ordenado em cada tabela criada sem uma opção de índice. Os dados de cada coluna são compactados em diferentes segmentos de rowgroup de CCI. Há metadados no intervalo de valores de cada segmento, de modo que 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. O CCI oferece o nível mais alto de compactação de dados e reduz o tamanho dos segmentos a serem lidos para que as consultas possam ser executadas mais rapidamente. No entanto, como o construtor de índice não classifica os dados antes de compactá-los em segmentos, podem ocorrer segmentos com intervalos de valores sobrepostos, o que faz com que as consultas leiam mais segmentos do disco e demorem mais tempo para serem concluídas.

Ao criar uma CCI ordenada, o Mecanismo de Banco de Dados SQL classifica os dados existentes na memória pelas chaves de ordem antes que o construtor de índice os compacte em segmentos de índice. Com dados classificados, a sobreposição é reduzida, o que permite que as consultas tenham uma eliminação de segmento mais eficiente e um desempenho mais rápido, pois o número de segmentos a serem lidos do disco é menor. Se todos os dados puderem ser classificados na memória de uma vez, será possível evitar a sobreposição de segmento. Devido às grandes tabelas dos data warehouses, esse cenário não acontece com frequência.

Para verificar os intervalos de segmento de uma coluna, execute o seguinte comando com os nomes da tabela e da coluna:

SELECT
o.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,
cls.max_data_id-cls.min_data_id as difference 

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 o.name = '<Table Name>' and cols.name = '<Column Name>'  
ORDER BY o.name, pnp.index_id, cls.min_data_id;

Observação

Em uma tabela de CCI ordenado, os novos dados resultantes do mesmo lote de operações de carregamento de dados ou DML são ordenados no lote, sem classificação global em todos os dados na tabela. Os usuários podem RECRIAR o CCI ordenado para classificar todos os dados da tabela. Para uma tabela particionada, o REBUILD é feito em uma partição por vez. Os dados da partição que está sendo recriada estão "offline" e indisponíveis até que REBUILD seja concluída para ela.

Desempenho de consulta

O ganho de desempenho de uma consulta de um CCI ordenado depende dos padrões de consulta, do tamanho e da classificação dos dados, da estrutura física dos segmentos e da classe de recurso e DWU escolhida para a execução da consulta. Os usuários devem revisar todos esses fatores antes de escolher as colunas de ordenação ao criar uma tabela de CCI ordenado.

Consultas com todos esses padrões normalmente são executadas mais rapidamente com o CCI ordenado.

  • As consultas têm predicados de igualdade, desigualdade ou intervalo
  • As colunas de predicado e de CCI ordenado 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 da consulta 1 e da consulta 2 pode se beneficiar mais do CCI ordenado do que das outras consultas, pois fazem referência a todas as colunas CCI 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 do carregamento de dados

O desempenho do carregamento de dados em uma tabela de CCI ordenado é semelhante ao de uma tabela particionada. Carregar dados em uma tabela de CCI ordenado pode levar mais tempo do que em uma tabela de CCI não ordenado devido à operação de classificação de dados, no entanto, as consultas podem ser executadas posteriormente de maneira mais rápida com o CCI ordenado.

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 da configuração de MAXDOP (grau máximo de paralelismo) durante a criação do CCI ordenado. As estratégias a seguir reduzem a sobreposição de segmento ao criar o CCI ordenado.

  • Crie um CCI ordenado com OPTION (MAXDOP = 1). Cada thread usado para a criação do CCI ordenado funciona em um subconjunto de dados e o classifica localmente. Não há classificação global entre os dados classificados por threads diferentes. O uso de threads paralelos pode reduzir o tempo de criação de um CCI ordenado, mas irá gerar 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 os CREATE INDEX comandos ou CREATE TABLE . Por exemplo:
CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);
  • Classifique previamente os dados pelas chaves de classificação antes de carregá-los em tabelas.

Aqui está um exemplo de uma distribuição de tabela CCI ordenada que tem sobreposição de segmento zero seguindo as recomendações acima. O CCI ordenado é ordenado em uma coluna bigint sem duplicatas.

Captura de tela de dados de texto mostrando nenhum segmento sobreposto.

Criar o CCI ordenado em tabelas grandes

A criação de um CCI ordenado é uma operação offline. Para tabelas sem partições, os dados não poderão ser acessados pelos usuários até que o processo de criação do CCI ordenado seja concluído. Para tabelas particionadas, como o mecanismo cria a partição de CCI ordenado uma partição por vez, os usuários ainda podem acessar os dados em partições em que a criação de CCI ordenado não está em processo. Você pode usar essa opção para minimizar o tempo de inatividade durante a criação de um CCI ordenado em tabelas grandes:

  1. Crie partições na tabela grande de destino (chamada Table_A).
  2. Crie uma tabela vazia de CCI ordenado (chamada Table_B) com o mesmo esquema de tabela e partição da Table_A.
  3. Alterne uma partição da Table_A para a Table_B.
  4. Execute ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID> para recompilar a partição comutada na Table_B.
  5. Repita as etapas 3 e 4 para cada partição na Table_A.
  6. Depois que todas as partições forem alteradas da Table_A para a Table_B e recompiladas, descarte Table_A e renomeie Table_B como Table_A.

Recursos do SQL Server 2022

O SQL Server 2022 (16.x) apresentou índices columnstore clusterizados e ordenados semelhantes à funcionalidade dos pools de SQL dedicados do Azure Synapse.

  • SQL Server 2022 (16.x) e versões posteriores e outras plataformas SQL dão suporte a recursos avançados de eliminação de segmentos columnstore clusterizados para tipos de dados de cadeia de caracteres, binários e guid e o tipo de dados datetimeoffset para escala maior que dois. Anteriormente, essa eliminação de segmento se aplicava a tipos de dados numéricos, de data e hora e ao tipo de dados datetimeoffset com escala menor ou igual a dois.
  • Atualmente, apenas o SQL Server 2022 (16.x) e versões posteriores e outras plataformas SQL dão suporte à eliminação de rowgroup columnstore clusterizado para o prefixo de LIKE predicados, por exemplo column LIKE 'string%'. A eliminação de segmento não é compatível com o uso sem prefixo de LIKE, como column LIKE '%string'.

Para obter a disponibilidade do índice columnstore ordenado, consulte Disponibilidade do índice de coluna ordenada.

Para obter mais informações, confira Novidades nos Índices Columnstore.

Para obter informações sobre índices columnstore ordenados em pools de SQL dedicados no Azure Synapse Analytics, consulte Ajuste de desempenho com índices columnstore clusterizados ordenados.

Exemplos

a. Para verificar se há colunas ordenadas e ordinal de ordem:

SELECT object_name(c.object_id) table_name, c.name column_name, i.column_store_order_ordinal
FROM sys.index_columns i
JOIN sys.columns c ON i.object_id = c.object_id AND c.column_id = i.column_id
WHERE column_store_order_ordinal <>0;

B. Para alterar o ordinal de coluna, adicione ou remova colunas da lista de ordenação ou altere do CCI para o CCI ordenado:

CREATE CLUSTERED COLUMNSTORE INDEX InternetSales ON dbo.InternetSales
ORDER (ProductKey, SalesAmount)
WITH (DROP_EXISTING = ON);

Conteúdo relacionado