Índices em tabelas de pool SQL dedicadas no Azure Synapse Analytics

• Inicia um teste gratuito Fabric.
• Assistente de Migração para Fabric Data Warehouse.

Recomendações e exemplos para indexar tabelas em pool SQL dedicado no Azure Synapse Analytics.

Tipos de índice

O pool SQL dedicado oferece várias opções de indexação, incluindo índices clusterizados columnstore, índices clusterizados e índices não clusterizados, e uma opção sem índice, também conhecida como heap.

Para criar uma tabela com um índice, consulte a documentação CREATE TABLE (pool SQL dedicado ).

Índices de colunas armazenadas em cluster

Por padrão, o pool dedicado do SQL cria um índice columnstore clusterizado quando nenhuma opção de índice é especificada numa tabela. As tabelas clustered columnstore oferecem tanto o mais alto nível de compressão de dados como o melhor desempenho global de consulta. As tabelas de clustered columnstore geralmente apresentam um desempenho superior às tabelas de índice clusterizado ou heap e são, muitas vezes, a melhor escolha para tabelas grandes. Por estas razões, o clustered columnstore é o melhor ponto de partida quando não tem a certeza de como indexar a sua tabela.

Para criar uma tabela columnstore clusterizada, especifique CLUSTERED COLUMNSTORE INDEX na cláusula WITH ou deixe a cláusula WITH desligada:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( CLUSTERED COLUMNSTORE INDEX );

Existem alguns cenários em que o clustered columnstore pode não ser uma boa opção:

• As tabelas Columnstore não suportam varchar(max), nvarchar(max) e varbinary(max). Considere, em vez disso, heap ou um índice agregado.
• As tabelas Columnstore podem ser menos eficientes para dados transitórios. Considere tabelas heap e temporárias.
• Tabelas pequenas com menos de 60 milhões de linhas. Considere as tabelas de pilha.

Tabelas heap

Quando arquiva temporariamente dados num pool SQL dedicado, pode perceber que usar uma tabela heap torna o processo geral mais rápido. Isto deve-se ao facto de as cargas para heaps serem mais rápidas do que para tabelas de índice e, em alguns casos, a leitura subsequente pode ser feita a partir da cache. Se estiveres a carregar dados apenas para os preparar antes de realizar mais transformações, carregar a tabela para uma tabela heap é muito mais rápido do que carregar os dados numa tabela columnstore clusterizada. Além disso, carregar dados para uma tabela temporária carrega mais rápido do que carregar uma tabela para armazenamento permanente. Após o carregamento dos dados, pode criar índices na tabela para um desempenho de consulta mais rápido.

As tabelas columnstore clusterizadas começam a atingir compressão ótima quando há mais de 60 milhões de linhas. Para tabelas de consulta pequenas, com menos de 60 milhões de linhas, considere usar HEAP ou índice clusterizado para um desempenho de consulta mais rápido.

Para criar uma tabela de heap, especifique HEAP na cláusula WITH:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( HEAP );

Índices agrupados e não agrupados

Índices agrupados podem superar as tabelas de dados colunar agrupadas quando uma única linha precisa ser rapidamente recuperada. Para consultas em que é necessária uma única ou poucas linhas de consulta para ser executada com extrema velocidade, considere um índice agrupado ou um índice secundário não agrupado. A desvantagem de usar um índice agrupado é que apenas as consultas que beneficiam são aquelas que usam um filtro altamente seletivo na coluna do índice agrupado. Para melhorar o filtro noutras colunas, pode ser adicionado um índice não agrupado a outras colunas. No entanto, cada índice adicionado a uma tabela acrescenta tanto espaço como tempo de processamento aos carregamentos.

Para criar uma tabela de índices agrupada, especifique o ÍNDICE AGRUPADO na cláusula WITH:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( CLUSTERED INDEX (id) );

Para adicionar um índice não agrupado numa tabela, use a seguinte sintaxe:

CREATE INDEX zipCodeIndex ON myTable (zipCode);

Otimização de índices columnstore agrupados

As tabelas clustered columnstore organizam os dados em segmentos. Ter alta qualidade de segmento é crucial para alcançar desempenho ótimo de consulta numa tabela columnstore. A qualidade dos segmentos pode ser medida pelo número de linhas num grupo de linhas comprimidas. A qualidade dos segmentos é ideal quando há pelo menos 100.000 linhas por grupo comprimido, com melhoria no desempenho à medida que o número de linhas por grupo de linhas se aproxima de 1 048 576, que é o máximo de linhas que um grupo pode conter.

A vista abaixo pode ser criada e usada no seu sistema para calcular a média de linhas por grupo de linhas e identificar quaisquer índices de coluna de cluster subótimos. A última coluna desta vista gera uma instrução SQL que pode ser usada para reconstruir os seus índices.

CREATE VIEW dbo.vColumnstoreDensity
AS
SELECT
        GETDATE()                                                               AS [execution_date]
,       DB_Name()                                                               AS [database_name]
,       s.name                                                                  AS [schema_name]
,       t.name                                                                  AS [table_name]
,       MAX(p.partition_number)                                                 AS [table_partition_count]
,       SUM(rg.[total_rows])                                                    AS [row_count_total]
,       SUM(rg.[total_rows])/COUNT(DISTINCT rg.[distribution_id])               AS [row_count_per_distribution_MAX]
,       CEILING((SUM(rg.[total_rows])*1.0/COUNT(DISTINCT rg.[distribution_id]))/1048576) AS [rowgroup_per_distribution_MAX]
,       SUM(CASE WHEN rg.[State] = 0 THEN 1                   ELSE 0    END)    AS [INVISIBLE_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE 0    END)    AS [INVISIBLE_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 1 THEN 1                   ELSE 0    END)    AS [OPEN_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE 0    END)    AS [OPEN_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 2 THEN 1                   ELSE 0    END)    AS [CLOSED_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE 0    END)    AS [CLOSED_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 3 THEN 1                   ELSE 0    END)    AS [COMPRESSED_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE 0    END)    AS [COMPRESSED_rowgroup_rows]
,       SUM(CASE WHEN rg.[State] = 3 THEN rg.[deleted_rows]   ELSE 0    END)    AS [COMPRESSED_rowgroup_rows_DELETED]
,       MIN(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_AVG]
,       'ALTER INDEX ALL ON ' + s.name + '.' + t.NAME + ' REBUILD;'             AS [Rebuild_Index_SQL]
FROM    sys.[dm_pdw_nodes_db_column_store_row_group_physical_stats] rg
JOIN    sys.[pdw_nodes_tables] nt                   ON  rg.[object_id]          = nt.[object_id]
                                                    AND rg.[pdw_node_id]        = nt.[pdw_node_id]
                                                    AND rg.[distribution_id]    = nt.[distribution_id]
JOIN    sys.[pdw_permanent_table_mappings] mp                 ON  nt.[name]               = mp.[physical_name]
JOIN    sys.[tables] t                              ON  mp.[object_id]  = t.[object_id]
JOIN    sys.[schemas] s                             ON t.[schema_id]    = s.[schema_id]
JOIN    sys.[partitions] p                          ON P.object_id      = t.object_id
GROUP BY
        s.[name]
,       t.[name];

Agora que criou a vista, execute esta consulta para identificar tabelas com grupos de linhas com menos de 100 mil linhas. Pode querer aumentar o limiar de 100 K se procura uma qualidade de segmento mais ótima.

SELECT    *
FROM    [dbo].[vColumnstoreDensity]
WHERE    COMPRESSED_rowgroup_rows_AVG < 100000
        OR INVISIBLE_rowgroup_rows_AVG < 100000;

Depois de executar a consulta, pode começar a analisar os dados e analisar os seus resultados. Esta tabela explica o que procurar na análise dos grupos de linhas.

Impacto da manutenção do índice

A coluna Rebuild_Index_SQL na visão vColumnstoreDensity contém uma instrução ALTER INDEX REBUILD que pode ser usada para reconstruir os seus índices. Ao reconstruir os seus índices, certifique-se de alocar memória suficiente para a sessão que reconstrói o seu índice. Para isso, aumente a classe de recurso de um utilizador que tenha permissões para reconstruir o índice nesta tabela até ao mínimo recomendado. Por exemplo, veja Reconstruir índices para melhorar a qualidade dos segmentos mais adiante neste artigo.

Para uma tabela com um índice de coluna ordenado e agrupado, ALTER INDEX REBUILD irá reordenar os dados usando tempdb. Monitorizar a tempdb durante as operações de reconstrução. Se necessitares de mais espaço tempdb, aumenta o pool de bases de dados. Reduza a escala assim que a reconstrução do índice estiver concluída.

Para uma tabela com um índice de coluna de armazenamento agrupado ordenado, ALTER INDEX REORGANIZE não reordena os dados. Para reordenar dados, use ALTER INDEX REBUILD.

Para obter mais informações sobre índices columnstore clusterizados ordenados, consulte Ajuste de desempenho com índice columnstore clusterizado ordenado.

Causas da má qualidade do índice columnstore

Se identificou tabelas com má qualidade segmentada, quer identificar a causa raiz. Abaixo estão algumas outras causas comuns da má qualidade dos segmentos:

1. Pressão de memória quando o índice foi construído
2. Elevado volume de operações DML
3. Operações de carga pequena ou carga gotejante
4. Demasiadas partições

Estes fatores podem fazer com que um índice de coluna tenha significativamente menos do que o ótimo 1 milhão de linhas por grupo de linhas. Também podem fazer com que as linhas passem para o grupo de linhas delta em vez de um grupo de linhas comprimidas.

Pressão de memória quando o índice foi construído

O número de linhas por grupo de linhas comprimido está diretamente relacionado com a largura da linha e a quantidade de memória disponível para processar o grupo de linhas. Quando linhas são escritas em tabelas de armazenamento de colunas sob pressão de memória, a qualidade dos segmentos de colunas pode sofrer. Portanto, a melhor prática é dar à sessão que está a escrever nas tabelas de índice do seu Columnstore acesso ao máximo de memória possível. Como há uma compensação entre memória e concorrência, a orientação para a correta alocação de memória depende dos dados em cada linha da sua tabela, das unidades do data warehouse atribuídas ao seu sistema e do número de slots de concorrência que pode atribuir à sessão que está a escrever dados na sua tabela.

Elevado volume de operações DML

Um elevado volume de operações DML que atualizam e eliminam linhas pode introduzir ineficiência no armazenamento de colunas. Isto é especialmente verdade quando a maioria das linhas de um grupo de linhas é modificada.

• Eliminar uma linha de um grupo comprimido apenas marca logicamente a linha como eliminada. A linha permanece no grupo de linhas comprimidas até que a partição ou tabela seja reconstruída.
• A inserção de uma linha adiciona a linha a uma tabela de rowstore interna chamada grupo de linhas delta. A linha inserida não é convertida para columnstore até que o grupo delta de linhas esteja cheio e marcado como fechado. Os grupos de filas são encerrados assim que atingem a capacidade máxima de 1.048.576 filas.
• Atualizar uma linha no formato columnstore é processado como uma eliminação lógica e depois uma inserção. A linha inserida pode ser armazenada no armazenamento delta.

As operações de atualização e inserção em lote que excedam o limiar em massa de 102.400 linhas por distribuição alinhada à partição vão diretamente para o formato columnstore. No entanto, assumindo uma distribuição uniforme, seria necessário modificar mais de 6,144 milhões de linhas numa única operação para que isso ocorresse. Se o número de linhas para uma dada distribuição alinhada a partições for inferior a 102.400, as linhas vão para o armazenamento delta e permanecem lá até que linhas suficientes sejam inseridas ou modificadas para fechar o grupo de linhas ou até que o índice seja reconstruído.

Operações de carga pequena ou carga gotejante

Cargas pequenas que fluem para um pool SQL dedicado são também por vezes conhecidas como cargas de goteo. Normalmente representam um fluxo quase constante de dados a ser ingeridos pelo sistema. No entanto, como este curso de água é quase contínuo, o volume de filas não é particularmente grande. Frequentemente, os dados estão significativamente abaixo do limiar exigido para um carregamento direto no formato de armazenamento de colunas.

Nestas situações, muitas vezes é melhor colocar os dados primeiro no armazenamento de blob do Azure e deixá-los acumular-se antes de carregarem. Esta técnica é frequentemente conhecida como micro-batching.

Demasiadas partições

Outra coisa a considerar é o impacto da partição nas suas tabelas clusterizadas de colunas de armazenamento. Antes de particionar, um pool SQL dedicado já divide os seus dados em 60 bases de dados. A partição divide ainda mais os seus dados. Se particionar os seus dados, considere que cada partição precisa de pelo menos 1 milhão de linhas para beneficiar de um índice clusterizado de columnstore. Se particionar a sua tabela em 100 partições, então a sua tabela precisa de pelo menos 6 mil milhões de linhas para beneficiar de um índice columnstore clusterizado (60 distribuições 100 partições 1 milhão de linhas). Se a sua tabela de 100 partições não tiver 6 mil milhões de linhas, reduza o número de partições ou considere usar uma tabela heap em vez disso.

Depois de as suas tabelas terem sido carregadas com alguns dados, siga os passos abaixo para identificar e reconstruir tabelas com índices clustered columnstore subótimos.

Reconstituir índices para melhorar a qualidade dos segmentos

Passo 1: Identificar ou criar um utilizador que utilize a classe de recurso certa

Uma forma rápida de melhorar imediatamente a qualidade dos segmentos é reconstruir o índice. O SQL devolvido pela vista acima contém uma instrução ALTER INDEX REBUILD, que pode ser usada para reconstruir os seus índices. Ao reconstruir os seus índices, certifique-se de alocar memória suficiente para a sessão que reconstrói o seu índice. Para isso, aumente a classe de recurso de um utilizador que tenha permissões para reconstruir o índice nesta tabela até ao mínimo recomendado.

Abaixo está um exemplo de como alocar mais memória a um utilizador aumentando a sua classe de recurso. Para trabalhar com classes de recursos, consulte Classes de Recursos para gestão de carga de trabalho.

EXEC sp_addrolemember 'xlargerc', 'LoadUser';

Passo 2: Reconstruir os índices clustered columnstore com utilizadores de classe de recurso superior

Inicie sessão como o utilizador do passo 1 (LoadUser), que agora está a usar uma classe de recurso superior, e execute as instruções ALTER INDEX. Certifique-se de que este utilizador tem permissão ALTER para as tabelas onde o índice está a ser reconstruído. Estes exemplos mostram como reconstruir todo o índice de colonstore ou como reconstruir uma única partição. Em tabelas grandes, é mais prático reconstruir os índices uma única partição de cada vez.

Alternativamente, em vez de reconstruir o índice, podes copiar a tabela para uma nova tabela usando CTAS. Qual é o melhor caminho? Para grandes volumes de dados, o CTAS é geralmente mais rápido do que o ALTER INDEX. Para volumes menores de dados, o ALTER INDEX é mais fácil de usar e não vai exigir que troques a tabela.

-- Rebuild the entire clustered index
ALTER INDEX ALL ON [dbo].[DimProduct] REBUILD;

-- Rebuild a single partition
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5;

-- Rebuild a single partition with archival compression
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5 WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

-- Rebuild a single partition with columnstore compression
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5 WITH (DATA_COMPRESSION = COLUMNSTORE);

Reconstruir um índice num pool SQL dedicado é uma operação offline. Para mais informações sobre a reconstrução de índices, consulte a secção ALTER INDEX REBUILD no Columnstore Indexes Defragmentation e ALTER INDEX.

Passo 3: Verificar que a qualidade dos segmentos de columnstore em clusters melhorou

Execute novamente a consulta que identificou a tabela com má qualidade de segmento e verifique que a qualidade do segmento melhorou. Se a qualidade dos segmentos não melhorou, pode ser que as linhas da sua tabela sejam excessivamente largas. Considera usar uma classe de recursos superior ou DWU ao reconstruir os teus índices.

Reconstruir índices com CTAS e comutação de partições

Este exemplo utiliza a instrução CREATE TABLE AS SELECT (CTAS) e a troca de partição para reconstruir uma partição de tabela.

-- Step 1: Select the partition of data and write it out to a new table using CTAS
CREATE TABLE [dbo].[FactInternetSales_20000101_20010101]
    WITH    (   DISTRIBUTION = HASH([ProductKey])
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101,20010101
                                )
                            )
            )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
WHERE   [OrderDateKey] >= 20000101
AND     [OrderDateKey] <  20010101
;

-- Step 2: Switch IN the rebuilt data with TRUNCATE_TARGET option
ALTER TABLE [dbo].[FactInternetSales_20000101_20010101] SWITCH PARTITION 2 TO  [dbo].[FactInternetSales] PARTITION 2 WITH (TRUNCATE_TARGET = ON);

Para mais informações sobre a recriação de partições usando CTAS, consulte Utilização de partições em pool SQL dedicado.

Conteúdo relacionado

Para mais informações sobre o desenvolvimento de tabelas, consulte Tabelas de desenvolvimento.