Otimização do desempenho com índice columnstore em cluster ordenado

  • Artigo

Aplica-se a: conjuntos de SQL dedicados do Azure Synapse Analytics, SQL Server 2022 (16.x) e posterior

Quando os utilizadores consultam uma tabela columnstore no conjunto de SQL dedicado, o otimizador verifica os valores mínimos e máximos 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 respetivo tamanho total forem pequenos.

Índice columnstore ordenado vs. não ordenado

Por predefinição, para cada tabela criada sem uma opção de índice, um componente interno (construtor de índices) cria um índice columnstore (CCI) em cluster não ordenado. Os dados em cada coluna são comprimidos num segmento de grupo de linhas CCI separado. Existem metadados no intervalo de valores de cada segmento, pelo que os segmentos que estão fora dos limites do predicado de consulta não são lidos a partir do disco durante a execução da consulta. O CCI oferece o nível mais elevado de compressão de dados e reduz o tamanho dos segmentos a ler para que as consultas possam ser executadas mais rapidamente. No entanto, como o construtor de índices não ordena os dados antes de os comprimir em segmentos, podem ocorrer segmentos com intervalos de valores sobrepostos, o que faz com que as consultas leiam mais segmentos do disco e demorou mais tempo a terminar.

Encomendou índices columnstore agrupados ao permitir a eliminação eficiente de segmentos, resultando num desempenho muito mais rápido ao ignorar grandes quantidades de dados ordenados que não correspondem ao predicado da consulta. Ao criar um CCI ordenado, o motor de conjunto de SQL dedicado ordena os dados existentes na memória pelas chaves de ordem antes de o construtor de índices os comprimir em segmentos de índice. Com os dados ordenados, a sobreposição de segmentos é reduzida, o que permite que as consultas tenham uma eliminação de segmentos mais eficiente e, portanto, um desempenho mais rápido, porque o número de segmentos a ler a partir do disco é menor. Se todos os dados puderem ser ordenados em memória de uma só vez, pode evitar a sobreposição de segmentos. Devido a tabelas grandes em armazéns de dados, este 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;

Nota

Numa tabela CCI ordenada, os novos dados resultantes do mesmo lote de DML ou operações de carregamento de dados são ordenados nesse lote, não existe uma ordenação global em todos os dados na tabela. Os utilizadores podem RECONSTRUIR o CCI ordenado para ordenar todos os dados na tabela. No conjunto de SQL dedicado, o índice columnstore REBUILD é uma operação offline. Para uma tabela particionada, a função REBUILD é efetuada uma partição de cada vez. Os dados na partição que está a ser reconstruída estão "offline" e indisponíveis até que o REBUILD esteja concluído para essa partição.

Desempenho de consultas

O ganho de desempenho de uma consulta a partir de um CCI ordenado depende dos padrões de consulta, do tamanho dos dados, da ordenação dos dados, da estrutura física dos segmentos e da DWU e da classe de recursos escolhidas para a execução da consulta. Os utilizadores devem rever todos estes fatores antes de escolher as colunas de ordenação ao estruturar uma tabela CCI ordenada.

Normalmente, as consultas com todos estes padrões são executadas mais rapidamente com CCI ordenada.

  1. As consultas têm predicados de igualdade, desigualdade ou intervalo
  2. As colunas de predicado e as colunas CCI ordenadas são as mesmas.

Neste exemplo, a tabela T1 tem um índice columnstore em cluster 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 beneficiar mais do CCI ordenado do que das outras consultas, uma vez que referenciam 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 numa tabela CCI ordenada é semelhante a uma tabela particionada. O carregamento de dados para uma tabela CCI ordenada pode demorar mais do que uma tabela CCI não ordenada devido à operação de ordenação de dados. No entanto, as consultas podem ser executadas mais rapidamente posteriormente com CCI ordenada.

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

Gráfico de barras que mostra a comparação de desempenho do carregamento de dados em tabelas com esquemas diferentes.

Eis um exemplo de comparação de desempenho de consultas entre o CCI e o CCI ordenado.

Gráfico de barras a comparar o desempenho durante data_loading. Um índice columnstore agrupado ordenado tem uma duração mais baixa.

Reduzir 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) durante a criação ordenada de CCI. As seguintes estratégias reduzem a sobreposição de segmentos ao criar CCI ordenada.

  • Utilize xlargerc a classe de recursos numa DWU superior para permitir mais memória para ordenação de dados antes de o construtor de índices comprimir os dados em segmentos. Uma vez num segmento de índice, a localização física dos dados não pode ser alterada. Não existe nenhuma ordenação de dados dentro de um segmento ou entre segmentos.

  • Crie CCI ordenada com OPTION (MAXDOP = 1). Cada thread utilizado para a criação de CCI ordenada funciona num subconjunto de dados e ordena-os localmente. Não existe uma ordenação global entre dados ordenados por threads diferentes. A utilização de threads paralelos pode reduzir o tempo para criar um CCI ordenado, mas irá gerar mais segmentos sobrepostos do que utilizar um único thread. A utilização de uma única operação por thread proporciona a maior 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);

Nota

Atualmente, nos conjuntos de SQL dedicados no Azure Synapse Analytics, a opção MAXDOP só é suportada na criação de uma tabela CCI ordenada com CREATE TABLE AS SELECT o comando . A criação de um CCI encomendado através CREATE INDEX de ou CREATE TABLE comandos não suporta a opção MAXDOP. Esta limitação não se aplica às versões SQL Server 2022 e posteriores, onde pode especificar MAXDOP com os CREATE INDEX comandos ouCREATE TABLE.

  • Pré-ordene os dados pela(s) chave(s) de ordenação antes de os carregar para tabelas.

Eis um exemplo de uma distribuição de tabela CCI ordenada que tem zero segmentos sobrepostos ao seguir as recomendações acima. A tabela CCI ordenada é criada numa base de dados DWU1000c através de CTAS a partir de uma tabela de área dinâmica para dados de 20 GB com MAXDOP 1 e xlargerc. O CCI é ordenado numa coluna BIGINT sem duplicados.

Uma captura de ecrã a mostrar dados de texto sem sobreposição de segmentos.

Criar CCI ordenada em tabelas grandes

Criar um CCI ordenado é uma operação offline. Para tabelas sem partições, os dados não estarão acessíveis aos utilizadores até que o processo de criação de CCI ordenado seja concluído. Para tabelas particionadas, uma vez que o motor cria a partição CCI ordenada por partição, os utilizadores ainda podem aceder aos dados em partições onde a criação ordenada de CCI não está em processo. Pode utilizar esta opção para minimizar o tempo de inatividade durante a criação ordenada de CCI em tabelas grandes:

  1. Criar 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. Mudar 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 Table_Bcomutado em .
  5. Repita os passos 3 e 4 para cada partição no Table_A.
  6. Assim que todas as partições forem mudadas de Table_A para Table_B e tiverem sido reconstruídas, largue Table_Ae mude o nome Table_B para Table_A.

Dica

Para uma tabela de conjunto de SQL dedicada com um CCI encomendado, ALTER INDEX REBUILD irá reordenar os dados com tempdb. Monitorize tempdb durante as operações de reconstrução. Se precisar de mais tempdb espaço, aumente verticalmente o conjunto. Reduza verticalmente quando a recompilação do índice estiver concluída.

Para uma tabela de conjunto de SQL dedicada com um CCI encomendado, ALTER INDEX REORGANIZE não reorganiza os dados. Para recorrer a dados, utilize ALTER INDEX REBUILD.

Para obter mais informações sobre a manutenção ordenada de CCI, veja Otimizar índices columnstore em cluster.

Diferenças de funcionalidades nas capacidades do SQL Server 2022

SQL Server 2022 (16.x) introduziu índices columnstore em cluster ordenados semelhantes à funcionalidade nos conjuntos de SQL dedicados Azure Synapse.

  • Atualmente, apenas SQL Server 2022 (16.x) e versões posteriores suportam capacidades de eliminação melhorada de segmentos columnstore em cluster para tipos de dados de cadeias, binários e guid e o tipo de dados datetimeoffset para dimensionamento superior a dois. Anteriormente, esta eliminação de segmento aplica-se a tipos de dados numéricos, de data e hora e ao tipo de dados datetimeoffset com escala inferior ou igual a dois.
  • Atualmente, apenas SQL Server 2022 (16.x) e versões posteriores suportam a eliminação de grupos de linhas columnstore em cluster para o prefixo de LIKE predicados, por exemplo column LIKE 'string%'. A eliminação de segmentos não é suportada para utilização sem prefixo de LIKE, como column LIKE '%string'.

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

Exemplos

A. Para verificar a existência de colunas ordenadas e ordenar ordinal:

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 a coluna ordinal, adicione ou remova colunas da lista de encomendas ou mude de CCI para CCI ordenada:

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

Passos seguintes