Índices em tabelas do conjunto de SQL dedicado no Azure Synapse Analytics

Recomendações e exemplos para indexação de tabelas no 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 índice também conhecida como heap.

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

Índices columnstore agrupados

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

Para criar uma tabela columnstore clusterizada, basta especificar CLUSTERED COLUMNSTORE INDEX na cláusula WITH ou deixar a cláusula WITH desativada:

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 suportam varchar(max), nvarchar(max) e varbinary(max). Em vez disso, considere o índice de pilha ou clusterizado.
• As tabelas columnstore podem ser menos eficientes para dados transitórios. Considere tabelas de pilha e talvez até temporárias.
• Tabelas pequenas com menos de 60 milhões de linhas. Considere tabelas de pilha.

Tabelas de heap

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

As tabelas columnstore do cluster começam a atingir a 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 usar HEAP ou índice clusterizado para um desempenho de consulta mais rápido.

Para criar uma tabela de heap, basta especificar 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

Os índices clusterizados podem superar as tabelas columnstore clusterizadas quando uma única linha precisa ser recuperada rapidamente. Para consultas em que uma única ou poucas pesquisas de linha são necessárias para serem executadas com extrema velocidade, considere um índice clusterizado ou um índice secundário não clusterizado. 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 clusterizada, basta especificar 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 agrupadas organizam os dados em segmentos. Ter uma qualidade de segmento elevada é fundamental para alcançar o melhor desempenho de consulta numa tabela columnstore. A qualidade dos segmentos pode ser medida pelo número de linhas num grupo de linhas comprimido. A qualidade do segmento é mais ideal quando há pelo menos 100 K linhas por grupo de linhas compactadas e ganho de desempenho à medida que o número de linhas por grupo de linhas se aproxima de 1.048.576 linhas, que é o maior número de linhas que um grupo de linhas pode conter.

A visualização abaixo pode ser criada e usada em seu sistema para calcular a média de linhas por grupo de linhas e identificar quaisquer índices de armazenamento de colunas de cluster subótimos. A última coluna nessa exibição gera uma instrução SQL que pode ser usada para reconstruir 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 você criou o modo de exibição, execute esta consulta para identificar tabelas com grupos de linhas com menos de 100 K linhas. Você pode querer aumentar o limite de 100 K se estiver procurando por uma qualidade de segmento mais ideal.

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

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

Impacto da manutenção do índice

A coluna Rebuild_Index_SQL no vColumnstoreDensity modo de exibição contém uma ALTER INDEX REBUILD instrução que pode ser usada para reconstruir seus índices. Ao reconstruir seus índices, certifique-se de alocar memória suficiente para a sessão que reconstrói seu índice. Para fazer isso, aumente a classe de recurso de um usuário que tem permissões para reconstruir o índice nesta tabela para o mínimo recomendado. Para obter um exemplo, consulte Reconstruindo índices para melhorar a qualidade do segmento mais adiante neste artigo.

Para uma tabela com um índice columnstore clusterizado ordenado, ALTER INDEX REBUILD irá reordenar os dados usando tempdb. Monitore tempdb durante operações de reconstrução. Se precisar de mais espaço tempdb, aumente a escala do pool de banco de dados. Reduza novamente quando a reconstrução do índice for concluída.

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

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

Causas de fraca qualidade do índice columnstore

Se você identificou tabelas com baixa qualidade de segmento, deseja identificar a causa raiz. Abaixo estão algumas outras causas comuns de má qualidade do segmento:

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

Esses fatores podem fazer com que um índice columnstore tenha significativamente menos do que o 1 milhão de linhas ideais por grupo de linhas. Eles também podem fazer com que as linhas vão para o grupo de linhas delta em vez de um grupo de linhas compactadas.

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

O número de linhas por grupo de linhas compactadas está diretamente relacionado à largura da linha e à quantidade de memória disponível para processar o grupo de linhas. Quando as linhas são escritas em tabelas columnstore sob pressão de memória, a qualidade de segmento de columnstore poderá sofrer consequências. Portanto, a prática recomendada é dar à sessão que está gravando em suas tabelas de índice columnstore acesso ao máximo de memória possível. Como há uma 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 dar à sessão que está gravando dados na tabela.

Alto volume de operações DML

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

• Eliminar uma linha num grupo de linhas comprimidas apenas marca logicamente a linha como eliminada. A linha permanece no grupo de linhas comprimidas até que a partição ou a tabela sejam recriadas.
• A inserção de uma linha adiciona a linha a uma tabela de armazenamento de linhas interna chamada grupo de linhas delta. A linha inserida não é convertida em columnstore até que o grupo de linhas delta esteja cheio e marcado como fechado. Os 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, uma inserção. A linha inserida pode ser armazenada no armazenamento delta.

As operações de atualização e inserção em lote que excedem o limite de massa de 102.400 linhas por distribuição alinhada à partição vão diretamente para o formato columnstore. No entanto, supondo uma distribuição uniforme, você precisaria modificar mais de 6,144 milhões de linhas em uma única operação para que isso ocorresse. Se o número de linhas para uma determinada distribuição alinhada à partição for inferior a 102.400, as linhas irão para o armazenamento delta e permanecerão lá até que linhas suficientes tenham sido inseridas ou modificadas para fechar o grupo de linhas ou o índice tenha sido reconstruído.

Operações de carga pequena ou gota a gota

Pequenas cargas que fluem para o pool SQL dedicado também são às vezes conhecidas como cargas por gotejamento. Eles normalmente representam um fluxo quase constante de dados sendo ingeridos pelo sistema. No entanto, como este fluxo é quase contínuo, o volume de linhas não é particularmente grande. Na maioria das vezes, os dados estão significativamente abaixo do limite necessário para um carregamento direto para o formato columnstore.

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

Demasiadas partições

Outra coisa a considerar é o impacto do particionamento em suas tabelas columnstore clusterizadas. Antes do particionamento, o pool SQL dedicado já divide seus dados em 60 bancos de dados. O particionamento divide ainda mais os seus dados. Se você particionar seus dados, considere que cada partição precisa de pelo menos 1 milhão de linhas para se beneficiar de um índice columnstore clusterizado. Se você particionar sua tabela em 100 partições, sua tabela precisará de pelo menos 6 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 6 bilhões de linhas, reduza o número de partições ou considere usar uma tabela de pilha.

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

Reconstruir índices para melhorar a qualidade do segmento

Etapa 1: Identificar ou criar um usuário que usa a classe de recurso certa

Uma maneira rápida de melhorar imediatamente a qualidade do segmento é reconstruir o índice. O SQL retornado pela exibição acima contém uma instrução ALTER INDEX REBUILD, que pode ser usada para reconstruir seus índices. Ao reconstruir seus índices, certifique-se de alocar memória suficiente para a sessão que reconstrói seu índice. Para fazer isso, aumente a classe de recurso de um usuário que tem permissões para reconstruir o índice nesta 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 recursos, consulte Classes de recursos para gerenciamento de carga de trabalho.

EXEC sp_addrolemember 'xlargerc', 'LoadUser';

Etapa 2: Reconstruir índices columnstore clusterizados com usuário de classe de recurso mais alta

Entre como o usuário na etapa 1 (LoadUser), que agora está usando uma classe de recurso mais alta, e execute as instruções ALTER INDEX. Certifique-se de que esse usuário tenha permissão ALTER para as tabelas onde o índice está sendo reconstruído. Estes exemplos mostram como reconstruir todo o índice columnstore ou como reconstruir uma única partição. Em tabelas grandes, é mais prático reconstruir índices de uma única partição de cada vez.

Como alternativa, em vez de reconstruir o índice, você pode 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, ALTER INDEX é mais fácil de usar e não exigirá que você troque 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);

A reconstrução de um índice no pool SQL dedicado é uma operação offline. Para obter mais informações sobre como reconstruir índices, consulte a seção ALTER INDEX REBUILD em Columnstore Indexes Defragmentation e ALTER INDEX.

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

Execute novamente a consulta que identificou a tabela com baixa qualidade de segmento e verifique se a qualidade do segmento melhorou. Se a qualidade do segmento não melhorou, pode ser que as linhas na sua tabela sejam muito largas. Considere o uso de uma classe de recurso mais alta ou DWU ao reconstruir seus índices.

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

Este exemplo usa a instrução CREATE TABLE AS SELECT (CTAS) e a alternância 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 obter mais informações sobre como recriar partições usando CTAS, consulte Usando partições no pool SQL dedicado.

Próximos passos

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