Ajuste de desempenho com índice columnstore clusterizado ordenado no Azure Synapse Analytics

Aplica-se a: Pools SQL dedicados do Azure Synapse Analytics

Quando os usuários consultam uma tabela columnstore no pool SQL dedicado, 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 pequenos.

Índice columnstore agrupado ordenado vs. não ordenado

Por padrão, para cada tabela criada sem uma opção de índice, um componente interno (construtor de índice) cria um índice columnstore clusterizado (CCI) não ordenado nela. Os dados em cada coluna são compactados em um segmento de grupo de linhas CCI separado. Há metadados no intervalo de valores de cada segmento, 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. A CCI oferece o mais alto nível 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 índices não classifica os dados antes de compactá-los em segmentos, 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.

Ordenou índices columnstore clusterizados permitindo a eliminação eficiente de segmentos, resultando em um desempenho muito mais rápido ao ignorar grandes quantidades de dados ordenados que não correspondem ao predicado de consulta. Ao criar uma CCI ordenada, o mecanismo de pool SQL dedicado classifica os dados existentes na memória pela(s) chave(s) de ordem antes que o construtor de índices os compacte em segmentos de índice. Com dados classificados, a sobreposição de segmentos é reduzida, permitindo que as consultas tenham uma eliminação de segmento mais eficiente e, portanto, um desempenho mais rápido porque o número de segmentos para ler do disco é menor. Se todos os dados puderem ser classificados na memória de uma só vez, a sobreposição de segmentos pode ser evitada. Devido às grandes tabelas em data warehouses, esse cenário não acontece com frequência.

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

SELECT o.name, pnp.index_id,
cls.row_count, pnp.data_compression_desc,
pnp.pdw_node_id, pnp.distribution_id, 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.pdw_nodes_partitions AS pnp
   JOIN sys.pdw_nodes_tables AS Ntables ON pnp.object_id = NTables.object_id AND pnp.pdw_node_id = NTables.pdw_node_id
   JOIN sys.pdw_table_mappings AS Tmap  ON NTables.name = TMap.physical_name AND substring(TMap.physical_name,40, 10) = pnp.distribution_id
   JOIN sys.objects AS o ON TMap.object_id = o.object_id
   JOIN sys.pdw_nodes_column_store_segments AS cls ON pnp.partition_id = cls.partition_id AND pnp.distribution_id  = cls.distribution_id
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>'  and TMap.physical_name  not like '%HdTable%'
ORDER BY o.name, pnp.distribution_id, cls.min_data_id;

Desempenho de consultas

O ganho de desempenho de uma consulta de uma CCI ordenada 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 da DWU e da classe de recursos escolhida para a execução da consulta. Os usuários devem revisar todos esses fatores antes de escolher as colunas de ordenação ao projetar uma tabela CCI ordenada.

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

1. As consultas têm predicados de igualdade, desigualdade ou alcance
2. 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_B e 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 da CCI ordenada do que das outras consultas, pois elas 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 de carregamento de dados

O desempenho do carregamento de dados em uma tabela CCI ordenada é semelhante a uma tabela particionada. O carregamento de dados em uma tabela CCI ordenada pode levar mais tempo do que uma tabela CCI não ordenada devido à operação de classificação de dados, no entanto, as consultas podem ser executadas mais rapidamente depois com a CCI ordenada.

Aqui está um exemplo de comparação de desempenho do carregamento de dados em tabelas com esquemas diferentes.

Aqui está um exemplo de comparação de desempenho de consulta entre CCI e CCI ordenada.

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

• Use xlargerc a classe de recurso em um DWU mais alto para permitir mais memória para classificação de dados antes que o construtor de índice compacte os dados em segmentos. Uma vez em um segmento de índice, a localização física dos dados não pode ser alterada. Não há classificação de dados dentro de um segmento ou entre segmentos.

• Crie CCI ordenada com OPTION (MAXDOP = 1). Cada thread usado para a criação ordenada de CCI 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 uma CCI ordenada, mas gerará mais segmentos sobrepostos do que o uso de um único thread. O uso de uma única operação roscada oferece a mais alta qualidade de compressão. Por exemplo:

CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);

• Pré-classifique os dados pela(s) chave(s) de classificação antes de carregá-los em tabelas.

Aqui está um exemplo de uma distribuição ordenada de tabela CCI que tem zero segmento sobreposto seguindo as recomendações acima. A tabela CCI ordenada é criada em um banco de dados DWU1000c via CTAS a partir de uma tabela de heap de 20 GB usando MAXDOP 1 e xlargerc. A ICC é ordenada numa coluna BIGINT sem duplicados.

Criar CCI ordenada em tabelas grandes

Criar uma CCI ordenada é uma operação offline. Para tabelas sem partições, os dados não estarão acessíveis aos usuários até que o processo de criação de CCI ordenado seja concluído. Para tabelas particionadas, como o mecanismo cria a partição CCI ordenada por partição, os usuários ainda podem acessar os dados em partições onde a criação ordenada de CCI não está em processo. Você pode usar essa opção para minimizar o tempo de inatividade durante a criação de CCI ordenada em tabelas grandes:

1. Crie partições na tabela grande de destino (chamada Table_A).
2. Crie uma tabela CCI ordenada vazia (chamada Table_B) com a mesma tabela e esquema de partição que Table_A.
3. Alterne uma partição de Table_A para Table_B.
4. Execute ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID> para reconstruir a partição comutada no Table_B.
5. Repita as etapas 3 e 4 para cada partição em Table_A.
6. Depois que todas as partições forem trocadas de Table_A para Table_B e tiverem sido reconstruídas, solte Table_Ae renomeie Table_B para Table_A.

Diferenças de recursos nos recursos do SQL Server 2022

O SQL Server 2022 (16.x) introduziu índices columnstore clusterizados ordenados semelhantes ao recurso nos pools SQL dedicados do Azure Synapse.

• Atualmente, apenas o SQL Server 2022 (16.x) e versões posteriores oferecem suporte a recursos aprimorados de eliminação de segmento de columnstore clusterizado para tipos de dados string, binário e guid e o tipo de dados datetimeoffset para escala maior que dois. Anteriormente, essa eliminação de segmento se aplicava aos 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 oferecem suporte à eliminação de grupos de linhas columnstore clusterizados para o prefixo de LIKE predicados, por exemplo column LIKE 'string%'. A eliminação de segmento não é suportada para o uso sem prefixo de LIKE, como column LIKE '%string'.

Para obter mais informações, consulte O que há de novo nos índices Columnstore.

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 da coluna, adicionar ou remover colunas da lista de ordens ou mudar de CCI para CCI ordenada:

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

Próximos passos

• Para obter mais dicas de desenvolvimento, consulte Visão geral do desenvolvimento.
• Índices columnstore: Descrição geral
• O que há de novo nos índices columnstore
• Índices columnstore – Documentação de orientação de design
• Índices columnstore – Desempenho da consulta