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

• Iniciar uma avaliação gratuita Fabric.
• Assistente de Migração para Fabric Data Warehouse.

Recomendações e exemplos para tabelas de indexação 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 columnstore clusterizados, índices clusterizados e índices não clusterizados, e uma opção sem indexação também conhecida como heap.

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

Índice columnstore clusterizado

Por padrão, o pool de SQL dedicado cria um índice columnstore clusterizado quando nenhuma opção de índice é especificada em uma tabela. As tabelas columnstore clusterizadas oferecem o nível mais alto de compactação de dados e o melhor desempenho de consulta geral. As tabelas columnstore clusterizadas geralmente superam as tabelas de índice clusterizado ou de heap e geralmente são a melhor opção para tabelas grandes. Por esses motivos, columnstore clusterizada é a melhor opção para começar quando não se tem certeza de como indexar uma tabela.

Para criar uma tabela columnstore clusterizada, especifique CLUSTERED COLUMNSTORE INDEX na cláusula WITH, ou não incluir a cláusula WITH:

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

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

• As tabelas columnstore não dão suporte a varchar (max), nvarchar (max) e varbinary (max). Considere o heap ou índice clusterizado.
• As tabelas ColumnStore podem ser menos eficientes para dados transitórios. Considere a possibilidade de tabelas heap ou temporárias.
• Tabelas pequenas com menos de 60 milhões de linhas. Considere as tabelas de heap.

Tabelas de heap

Quando você estiver descarregando temporariamente os dados no pool de SQL dedicado, poderá achar que usar uma tabela de heap torna o processo geral mais rápido. Isso ocorre porque o carregamento para heaps é mais rápido do que para as tabelas de índices e, em alguns casos, a leitura subsequente pode ser feita do cache. Se estiver carregando os dados apenas para prepará-los antes de executar mais transformações, carregar a tabela na tabela de heap é muito mais rápido que carregar os dados em uma tabela columnstore clusterizada. Além disso, o carregamento de dados em uma tabela temporária carrega mais rapidamente do que o carregamento de uma tabela em um armazenamento permanente. Após o carregamento dos dados, você pode criar índices na tabela para um desempenho de consulta mais rápido.

Tabelas columnstore em cluster começam a obter compactação ideal quando há mais de 60 milhões de linhas. Para tabelas de pesquisa pequenas, com menos de 60 milhões de linhas, considere o uso de HEAP ou índice clusterizado para desempenho de consulta mais rápido.

Para criar uma tabela do tipo HEAP, especifique HEAP na cláusula WITH:

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

Índices clusterizados e não clusterizados

Os índices clusterizados podem superar o desempenho de tabelas columnstore clusterizadas quando uma única linha precisa ser recuperada rapidamente. Para consultas em que seja necessário pesquisar uma linha ou poucas linhas para desempenho com extrema velocidade, considere um índice agrupado ou um índice secundário não agrupado. A desvantagem de usar um índice clusterizado é que apenas as consultas que se beneficiam são aquelas que usam um filtro altamente seletivo na coluna de índice clusterizado. Para melhorar o filtro em outras colunas, um índice não clusterizado pode ser adicionado a outras colunas. No entanto, cada índice adicionado a uma tabela adiciona espaço e tempo de processamento às cargas.

Para criar uma tabela de índice clusterizado, especifique o CLUSTERED INDEX 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 clusterizado em uma tabela, use a seguinte sintaxe:

CREATE INDEX zipCodeIndex ON myTable (zipCode);

Otimizando índices columnstore clusterizados

As tabelas columnstore clusterizadas organizam dados em segmentos. Ter segmentos de alta qualidade é fundamental para alcançar um desempenho ideal em uma tabela columnstore. A qualidade de um segmento pode ser medida pelo número de linhas em um grupo de linhas compactadas. A qualidade do segmento é mais otimizada quando há pelo menos 100 mil linhas por grupo de linha compactado e há um ganho de desempenho conforme o número de linhas por grupo de linha se aproxima de 1.048.576 linhas, que é a maior quantidade de linhas que um grupo de linha pode conter.

A visão abaixo pode ser criada e usada em seu sistema para calcular a média de linhas por grupo de linhas e identificar índices columnstore de cluster que podem não estar otimizados. A última coluna nessa exibição gera uma instrução SQL que pode ser usada para recriar os í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 você criou o modo de exibição, execute essa consulta para identificar tabelas com grupos de linhas com menos de 100 mil linhas. Convém aumentar o limite de 100.000 se você está em busca de uma qualidade mais otimizada do segmento.

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

Depois de executar a consulta, você poderá começar a analisar os dados e seus resultados. Esta tabela explica o que procurar na sua análise do grupo de linhas.

Impacto da manutenção do índice

A coluna Rebuild_Index_SQL na exibição vColumnstoreDensity contém uma instrução ALTER INDEX REBUILD que pode ser usada para recompilar seus índices. Ao recriar os índices, não deixe de alocar memória suficiente para a sessão que recria o índice. Para fazer isso, aumente a classe de recurso de um usuário que tem permissões para recriar o índice nessa tabela para o mínimo recomendado. Para obter um exemplo, consulte Reconstruir índices para melhorar a qualidade dos segmentos posteriormente neste artigo.

Para uma tabela com um índice columnstore clusterizado ordenado, ALTER INDEX REBUILD reordenará os dados usando tempdb. Monitore o tempdb durante operações de recompilação. Se você precisar de mais espaço de tempdb, amplie o pool de banco de dados. Reduza novamente para o estado anterior assim que a reconstrução do índice for concluída.

Para uma tabela com um índice columnstore clusterizado ordenado, ALTER INDEX REORGANIZE não reclassifica os dados. Para reclassificar os dados, use ALTER INDEX REBUILD.

Para saber mais sobre índices columnstore clusterizados ordenados, confira Ajuste de desempenho com índice columnstore clusterizado ordenado.

Causas da baixa qualidade do índice columnstore

Se você tiver identificado tabelas com segmentos de má qualidade, deseja identificar a causa raiz. A seguir estão algumas causas comuns de segmentos de qualidade inferior:

1. Pressão de memória quando o índice foi criado
2. Alto volume de operações DML
3. Operações de carga pequena ou de baixa intensidade
4. Número excessivo de partições

Esses fatores podem fazer com que um índice columnstore tenha menos que o ideal de um milhão de linhas por grupo de linhas. Eles também podem fazer com que as linhas sejam direcionadas para o grupo de linhas delta, em vez do grupo de linhas compactado.

Pressão de memória quando o índice foi criado

O número de linhas por grupo de linhas compactado está diretamente relacionado à largura da linha e à quantidade de memória disponível para processar o grupo de linhas. Quando as linhas são gravadas nas tabelas columnstore sob pressão de memória, a qualidade do segmento columnstore pode ficar prejudicada. Portanto, a prática recomendada é fornecer à sessão que está escrevendo nas tabelas de índice columnstore o máximo de memória possível. Devido à compensação entre memória e simultaneidade, a orientação sobre a alocação correta de memória depende dos dados em cada linha da tabela, das unidades de data warehouse alocadas ao sistema, e do número de slots de simultaneidade que você pode alocar à sessão que está escrevendo dados na tabela.

Alto volume de operações DML

Um alto volume de operações DML que atualizam e excluem linhas pode causar ineficiência no columnstore. Isso acontece principalmente quando a maioria das linhas em um grupo de linhas é modificada.

• A exclusão de uma linha de um rowgroup compactado somente marca logicamente a linha como excluída. A linha permanece no grupo de linhas comprimido até que a partição ou a tabela seja recriada.
• Inserir uma linha a adiciona a uma tabela interna de armazenamento em linhas chamada grupo de linhas delta. A linha inserida não é convertida para a columnstore, até que o rowgroup delta esteja cheio e marcado como fechado. Grupos de linhas são fechados quando atingem a capacidade máxima de 1 048 576 linhas.
• A atualização de uma linha no formato columnstore é processada como uma exclusão lógica e, em seguida, como uma inserção. A linha inserida pode ser armazenada no repositório delta.

As operações de atualização e inserção em lote que excedem o limite em massa de 102.400 linhas por distribuição alinhada em partição vão diretamente no formato columnstore. No entanto, supondo a ocorrência de uma distribuição uniforme, seria necessário modificar mais de 6.144 milhões de linhas em uma única operação para que isso ocorresse. Se o número de linhas de uma determinada distribuição alinhada em partição for menor do que 102.400, as linhas são encaminhadas ao repositório delta e permanecem lá até que uma quantidade suficiente de linhas seja inserida ou modificada, a fim de fechar o grupo de linhas ou recriar o índice.

Operações de carga pequenas ou gotejantes

Pequenas cargas que fluem para um pool dedicado de SQL às vezes também são conhecidas como cargas gotejantes. Normalmente, elas representam um fluxo quase constante de dados que estão sendo incluídos pelo sistema. No entanto, como esse fluxo é quase contínuo, o volume de linhas não é particularmente grande. Frequentemente, os dados ficam consideravelmente abaixo do limite necessário para um carregamento direto no formato columnstore.

Nessas situações, é melhor levar os dados primeiro ao armazenamento de blobs do Azure e deixá-los se acumularem antes do carregamento. Essa técnica é conhecida normalmente como micro envio em lote.

Número excessivo de partições

Outra coisa a considerar é o impacto do particionamento de suas tabelas columnstore clusterizadas. Antes do particionamento, o pool de SQL dedicado já divide seus dados em 60 bancos de dados. O particionamento divide ainda mais seus dados. Se particionar seus dados, considere que cada partição precisa ter pelo menos um milhão de linhas para se beneficiar de um índice columnstore clusterizado. Se você particionar sua tabela em 100 partições, então sua tabela precisará de pelo menos seis bilhões de linhas para se beneficiar de um índice columnstore clusterizado (60 distribuições × 100 partições × 1 milhão de linhas). Se sua tabela de 100 partições não tiver seis bilhões de linhas, reduza o número de partições ou considere usar uma tabela de heap.

Quando as tabelas tiverem sido carregadas com alguns dados, siga as etapas abaixo para identificar e recriar tabelas com índices columnstore clusterizados abaixo do ideal.

Recriar índices a fim de melhorar a qualidade do segmento

Etapa 1: identificar ou criar o usuário que usa a classe de recurso correta

Uma maneira rápida de melhorar a qualidade do segmento imediatamente é recriar o índice. O SQL retornado pela exibição acima contém uma instrução ALTER INDEX REBUILD, que pode ser usada para recriar os índices. Ao recriar os índices, não deixe de alocar memória suficiente para a sessão que recria o índice. Para fazer isso, aumente a classe de recurso de um usuário que tem permissões para recriar o índice nessa tabela para o mínimo recomendado.

Abaixo está um exemplo de como alocar mais memória para um usuário aumentando sua classe de recurso. Para trabalhar com classes de recurso, consulte Classes de recurso para gerenciamento de carga de trabalho.

EXEC sp_addrolemember 'xlargerc', 'LoadUser';

Etapa 2: Recriar os índices clusterizados do tipo columnstore utilizando um usuário com classe de recurso mais alta.

Conecte como o usuário da etapa 1 (LoadUser), que agora está usando uma classe de recurso maior, e execute as instruções ALTER INDEX. Verifique se esse usuário tem a permissão ALTER para as tabelas em que o índice está sendo recriado. Estes exemplos mostram como recriar todo o índice columnstore e como recriar uma partição única. Em tabelas grandes, é mais prático recriar índices, uma partição por vez.

Como alternativa, em vez de recriar o índice, é possível copiar a tabela para uma nova tabela usando CTAS. Qual é a melhor opção? Para grandes volumes de dados, CTAS é geralmente mais rápido do que ALTER INDEX. Para volumes menores de dados, ALTER INDEX é mais fácil de usar e não exige a troca da 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);

A recompilação de um índice em um pool de SQL dedicado é uma operação offline. Para obter mais informações sobre como recompilar índices, consulte a seção ALTER INDEX REBUILD em Desfragmentação dos índices columnstore e ALTER INDEX.

Etapa 3: Verifique se a qualidade do segmento columnstore clusterizado melhorou

Execute novamente a consulta que identificou a tabela com segmentos de má qualidade e verifique se a qualidade melhorou. Se a qualidade do segmento não melhorou, é possível que as linhas da tabela sejam muito amplas. Considere usar uma classe de recurso maior ou mais DWU durante a recriação dos índices.

Reconstruir índices com CTAS e alternância de partição

Este exemplo usa a instrução CRIAR TABELAS COMO SELECT (CTAS) e alternância de partição para recriar 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 saber mais sobre como recriar partições usando CTAS, confira Usar partições em conjunto SQL dedicado.

Conteúdo relacionado

Para obter mais informações sobre como desenvolver tabelas, consulte Desenvolvendo tabelas.