Indexando tabelas no SQL Data WarehouseIndexing tables in SQL Data Warehouse

Recomendações e exemplos para indexação de tabelas no SQL Data Warehouse do Azure.Recommendations and examples for indexing tables in Azure SQL Data Warehouse.

Tipos de índiceIndex types

O SQL Data Warehouse oferece várias opções de indexação, incluindo índices columnstore clusterizados, índices clusterizados e índices não clusterizados e uma opção de não indexação também conhecida como heap.SQL Data Warehouse offers several indexing options including clustered columnstore indexes, clustered indexes and nonclustered indexes, and a non-index option also known as heap.

Para criar uma tabela com um índice, consulte a documentação CRIAR TABELA (SQL Data Warehouse do Azure).To create a table with an index, see the CREATE TABLE (Azure SQL Data Warehouse) documentation.

Índice columnstore clusterizadoClustered columnstore indexes

Por padrão, o SQL Data Warehouse cria um índice columnstore clusterizado quando nenhuma opção de índice é especificada em uma tabela.By default, SQL Data Warehouse creates a clustered columnstore index when no index options are specified on a table. As tabelas columnstore clusterizadas oferecem o nível mais alto de compactação de dados e o melhor desempenho de consulta geral.Clustered columnstore tables offer both the highest level of data compression as well as the best overall query performance. 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.Clustered columnstore tables will generally outperform clustered index or heap tables and are usually the best choice for large tables. Por esses motivos, columnstore clusterizado é a melhor opção para começar quando você não tem certeza de como indexar sua tabela.For these reasons, clustered columnstore is the best place to start when you are unsure of how to index your table.

Para criar uma tabela columnstore clusterizada, basta especificar CLUSTERED COLUMNSTORE INDEX na cláusula WITH, ou não incluir a cláusula WITH:To create a clustered columnstore table, simply specify CLUSTERED COLUMNSTORE INDEX in the WITH clause, or leave the WITH clause off:

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:There are a few scenarios where clustered columnstore may not be a good option:

  • Tabelas ColumnStore não dão suporte a varchar (max), nvarchar (max) e varbinary (max).Columnstore tables do not support varchar(max), nvarchar(max) and varbinary(max). Considere o heap ou índice clusterizado.Consider heap or clustered index instead.
  • As tabelas ColumnStore podem ser menos eficientes para dados transitórios.Columnstore tables may be less efficient for transient data. Considere a possibilidade de tabelas heap ou até mesmo temporárias.Consider heap and perhaps even temporary tables.
  • Tabelas pequenas com menos de 60 milhões linhas.Small tables with less than 60 million rows. Considere as tabelas de heap.Consider heap tables.

Tabelas de heapHeap tables

Quando você estiver temporariamente destinando dados no SQL Data Warehouse, talvez você ache que usar uma tabela de heap torna o processo geral mais rápido.When you are temporarily landing data in SQL Data Warehouse, you may find that using a heap table makes the overall process faster. Isso ocorre porque carregamentos de heaps são mais rápidos que as tabelas de índice e, em alguns casos, a leitura subsequente pode ser feita no cache.This is because loads to heaps are faster than to index tables and in some cases the subsequent read can be done from 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 clusterizado.If you are loading data only to stage it before running more transformations, loading the table to heap table is much faster than loading the data to a clustered columnstore table. 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.In addition, loading data to a temporary table loads faster than loading a table to permanent storage.

Para tabelas de pesquisa pequenas, menos de 60 milhões linhas, muitas vezes as tabelas de heap fazem sentido.For small lookup tables, less than 60 million rows, often heap tables make sense. As tabelas columnstore do cluster começam a alcançar uma compactação ideal quando há mais de 60 milhões linhas.Cluster columnstore tables begin to achieve optimal compression once there is more than 60 million rows.

Para criar uma tabela de heap, basta especificar HEAP na cláusula WITH:To create a heap table, simply specify HEAP in the WITH clause:

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

Índices clusterizados e não clusterizadosClustered and nonclustered indexes

Os índices clusterizados podem superar o desempenho de tabelas columnstore clusterizadas quando uma única linha precisa ser recuperada rapidamente.Clustered indexes may outperform clustered columnstore tables when a single row needs to be quickly retrieved. Para consultas em que é necessário pesquisar poucas vezes ou somente uma vez para desempenho com extrema velocidade, considere um índice de cluster ou um índice secundário não clusterizado.For queries where a single or very few row lookup is required to performance with extreme speed, consider a cluster index or nonclustered secondary index. 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.The disadvantage to using a clustered index is that only queries that benefit are the ones that use a highly selective filter on the clustered index column. Para melhorar o filtro em outras colunas, um índice não clusterizado pode ser adicionado a outras colunas.To improve filter on other columns a nonclustered index can be added to other columns. No entanto, cada índice adicionado a uma tabela adiciona espaço e tempo de processamento às cargas.However, each index which is added to a table adds both space and processing time to loads.

Para criar uma tabela de índice clusterizado, basta especificar o CLUSTERED INDEX na cláusula WITH:To create a clustered index table, simply specify CLUSTERED INDEX in the WITH clause:

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:To add a non-clustered index on a table, use the following syntax:

CREATE INDEX zipCodeIndex ON myTable (zipCode);

Otimizando índices columnstore clusterizadosOptimizing clustered columnstore indexes

As tabelas columnstore clusterizadas são organizadas em dados em segmentos.Clustered columnstore tables are organized in data into segments. Para segmentos de boa qualidade, é essencial obter um desempenho ideal em uma tabela columnstore.Having high segment quality is critical to achieving optimal query performance on a columnstore table. A qualidade de segmento pode ser medida pelo número de linhas em um grupo de linhas compactado.Segment quality can be measured by the number of rows in a compressed row group. A qualidade do segmento é ideal quando há pelo menos cem mil linhas por grupo de linha compactado e ganho de desempenho conforme o número de linhas por grupo de linhas se aproxima das 1.048.576 linhas, que é a maior quantidade de linhas que um grupo de linhas pode conter.Segment quality is most optimal where there are at least 100K rows per compressed row group and gain in performance as the number of rows per row group approach 1,048,576 rows, which is the most rows a row group can contain.

A exibiçã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 abaixo do ideal.The below view can be created and used on your system to compute the average rows per row group and identify any sub-optimal cluster columnstore indexes. A última coluna nessa exibição gera uma instrução SQL que pode ser usada para recriar os índices.The last column on this view generates a SQL statement which can be used to rebuild your indexes.

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]
,    COUNT(DISTINCT rg.[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.[pdw_nodes_column_store_row_groups] 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_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]
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 cem mil linhas.Now that you have created the view, run this query to identify tables with row groups with less than 100K rows. Claro, convém aumentar o limite de cem mil se você deseja aumentar a qualidade do segmento.Of course, you may want to increase the threshold of 100K if you are looking for more optimal segment quality.

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.Once you have run the query you can begin to look at the data and analyze your results. Esta tabela explica o que procurar em sua análise do rowgroup.This table explains what to look for in your row group analysis.

ColunaColumn Como usar esses dadosHow to use this data
[table_partition_count][table_partition_count] Se a tabela for particionada, espere ver contagens mais altas de grupos de linhas abertos.If the table is partitioned, then you may expect to see higher Open row group counts. Cada partição na distribuição poderia, na teoria, ter um grupo de linhas aberto associado a ela.Each partition in the distribution could in theory have an open row group associated with it. Fatore isso na sua análise.Factor this into your analysis. Uma pequena tabela que foi particionada poderia ser otimizada com a remoção completa do particionamento, pois isso aprimoraria a compactação.A small table that has been partitioned could be optimized by removing the partitioning altogether as this would improve compression.
[row_count_total][row_count_total] Contagem total de linhas para a tabela.Total row count for the table. Por exemplo, você pode usar este valor para calcular a porcentagem de linhas no estado compactado.For example, you can use this value to calculate percentage of rows in the compressed state.
[row_count_per_distribution_MAX][row_count_per_distribution_MAX] Se todas as linhas forem distribuídas uniformemente, esse valor será o número alvo de linhas por distribuição.If all rows are evenly distributed this value would be the target number of rows per distribution. Compare esse valor com compressed_rowgroup_count.Compare this value with the compressed_rowgroup_count.
[COMPRESSED_rowgroup_rows][COMPRESSED_rowgroup_rows] Número total de linhas no formato columnstore para a tabela.Total number of rows in columnstore format for the table.
[COMPRESSED_rowgroup_rows_AVG][COMPRESSED_rowgroup_rows_AVG] Se o número médio de linhas for significativamente menor do que o número máximo de linhas para um grupo de linhas, considere usar CTAS ou ALTER INDEX REBUILD para compactar novamente os dadosIf the average number of rows is significantly less than the maximum # of rows for a row group, then consider using CTAS or ALTER INDEX REBUILD to recompress the data
[COMPRESSED_rowgroup_count][COMPRESSED_rowgroup_count] Número de grupos de linhas no formato columnstore.Number of row groups in columnstore format. Se esse número for muito alto em relação à tabela, isso significa que a densidade de columnstore é baixa.If this number is very high in relation to the table it is an indicator that the columnstore density is low.
[COMPRESSED_rowgroup_rows_DELETED][COMPRESSED_rowgroup_rows_DELETED] Linhas são excluídas de forma lógica no formato columnstore.Rows are logically deleted in columnstore format. Se o número for alto em relação ao tamanho da tabela, considere recriar a partição ou refazer o índice, pois isso as remove fisicamente.If the number is high relative to table size, consider recreating the partition or rebuilding the index as this removes them physically.
[COMPRESSED_rowgroup_rows_MIN][COMPRESSED_rowgroup_rows_MIN] Use em conjunto com as colunas AVG e MAX para entender o intervalo de valores dos grupos de linhas no columnstore.Use this in conjunction with the AVG and MAX columns to understand the range of values for the row groups in your columnstore. Um número baixo acima do limite de carga (102.400 por distribuição alinhada por partição) sugere que as otimizações estão disponíveis na carga de dadosA low number over the load threshold (102,400 per partition aligned distribution) suggests that optimizations are available in the data load
[COMPRESSED_rowgroup_rows_MAX][COMPRESSED_rowgroup_rows_MAX] Como acimaAs above
[OPEN_rowgroup_count][OPEN_rowgroup_count] Grupos de linhas abertos são normais.Open row groups are normal. Seria razoável esperar um grupo de linhas ABERTO de acordo com a distribuição de tabela (60).One would reasonably expect one OPEN row group per table distribution (60). Números excessivos sugerem carregamento de dados nas partições.Excessive numbers suggest data loading across partitions. Verifique a estratégia de particionamento para garantir que ela esteja corretaDouble check the partitioning strategy to make sure it is sound
[OPEN_rowgroup_rows][OPEN_rowgroup_rows] Cada grupo de linhas pode ter 1.048.576 linhas, no máximo.Each row group can have 1,048,576 rows in it as a maximum. Use esse valor para ver quão completo estão os grupos de linhas no momentoUse this value to see how full the open row groups are currently
[OPEN_rowgroup_rows_MIN][OPEN_rowgroup_rows_MIN] Os grupos abertos indicam que os dados estão sendo carregados lentamente na tabela ou que a carga anterior foi despejada sobre as linhas restantes nesse grupo de linhas.Open groups indicate that data is either being trickle loaded into the table or that the previous load spilled over remaining rows into this row group. Use as colunas MIN, MAX, AVG para ver a quantidade de dados colocada nos grupos de linhas ABERTOS.Use the MIN, MAX, AVG columns to see how much data is sat in OPEN row groups. Em tabelas pequenas, esse valor pode ser de 100% de dados!For small tables it could be 100% of all the data! Nesse caso, use ALTER INDEX REBUILD para forçar os dados para columnstore.In which case ALTER INDEX REBUILD to force the data to columnstore.
[OPEN_rowgroup_rows_MAX][OPEN_rowgroup_rows_MAX] Como acimaAs above
[OPEN_rowgroup_rows_AVG][OPEN_rowgroup_rows_AVG] Como acimaAs above
[CLOSED_rowgroup_rows][CLOSED_rowgroup_rows] Analise as linhas do grupo de linhas fechado como uma verificação de integridade.Look at the closed row group rows as a sanity check.
[CLOSED_rowgroup_count][CLOSED_rowgroup_count] O número de grupos de linhas fechados deverá ser baixo se algum for visto.The number of closed row groups should be low if any are seen at all. Os grupos de linhas fechados podem ser convertidos em grupos de linhas compactados usando a instrução ALTER INDEX... Comando reorganizar.Closed row groups can be converted to compressed row groups using the ALTER INDEX ... REORGANIZE command. No entanto, normalmente isso não é obrigatório.However, this is not normally required. Os grupos fechados são convertidos automaticamente em grupos de linhas columnstore pelo processo em segundo plano "motor de tupla".Closed groups are automatically converted to columnstore row groups by the background "tuple mover" process.
[CLOSED_rowgroup_rows_MIN][CLOSED_rowgroup_rows_MIN] Os grupos de linhas fechados devem ter uma taxa de preenchimento alta.Closed row groups should have a very high fill rate. Se a taxa de preenchimento de um grupo de linhas fechado for baixa, será necessário fazer outra análise do columnstore.If the fill rate for a closed row group is low, then further analysis of the columnstore is required.
[CLOSED_rowgroup_rows_MAX][CLOSED_rowgroup_rows_MAX] Como acimaAs above
[CLOSED_rowgroup_rows_AVG][CLOSED_rowgroup_rows_AVG] Como acimaAs above
[Rebuild_Index_SQL][Rebuild_Index_SQL] SQL para recriar o índice columnstore para uma tabelaSQL to rebuild columnstore index for a table

Causas de má qualidade de índice columnstoreCauses of poor columnstore index quality

Se você tiver identificado tabelas com segmentos de má qualidade, deseja identificar a causa raiz.If you have identified tables with poor segment quality, you want to identify the root cause. A seguir estão algumas causas comuns de segmentos de má qualidade:Below are some other common causes of poor segment quality:

  1. Pressão de memória quando o índice foi criadoMemory pressure when index was built
  2. Alto volume de operações DMLHigh volume of DML operations
  3. Operações de carregamento pequenas ou lentasSmall or trickle load operations
  4. Número excessivo de partiçõesToo many partitions

Esses fatores podem fazer com que um índice columnstore tenha menos que o ideal de um milhão de linhas por grupo de linhas.These factors can cause a columnstore index to have significantly less than the optimal 1 million rows per row group. Eles também podem colocar as linhas no rowgroup delta, em vez do rowgroup compactado.They can also cause rows to go to the delta row group instead of a compressed row group.

Pressão de memória quando o índice foi criadoMemory pressure when index was built

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.The number of rows per compressed row group are directly related to the width of the row and the amount of memory available to process the row group. Quando as linhas são gravadas nas tabelas columnstore sob pressão da memória, a qualidade do segmento columnstore pode ficar prejudicada.When rows are written to columnstore tables under memory pressure, columnstore segment quality may suffer. Portanto, a prática recomendada é fornecer à sessão que está gravando o acesso de tabelas de índice columnstore o máximo de memória possível.Therefore, the best practice is to give the session which is writing to your columnstore index tables access to as much memory as possible. Como há uma compensação entre a memória e simultaneidade, a orientação sobre a alocação de memória correta depende dos dados em cada linha da tabela, das unidades de data warehouse alocadas para o seu sistema, e o número de slots de simultaneidade que pode ser dado à sessão que está gravando dados em sua tabela.Since there is a trade-off between memory and concurrency, the guidance on the right memory allocation depends on the data in each row of your table, the data warehouse units allocated to your system, and the number of concurrency slots you can give to the session which is writing data to your table.

Alto volume de operações DMLHigh volume of DML operations

Um alto volume de operações DML pesadas que atualizam e excluem linhas pode causar ineficiência ao columnstore.A high volume of DML operations that update and delete rows can introduce inefficiency into the columnstore. Isso acontece principalmente quando a maioria das linhas em um rowgroup é modificada.This is especially true when the majority of the rows in a row group are modified.

  • A exclusão de uma linha de um rowgroup compactado somente marca logicamente a linha como excluída.Deleting a row from a compressed row group only logically marks the row as deleted. A linha permanece no rowgroup compactado até que a partição ou a tabela seja recriada.The row remains in the compressed row group until the partition or table is rebuilt.
  • A inserção de uma linha a adiciona a uma tabela interna da rowstore chamada de rowgroup delta.Inserting a row adds the row to an internal rowstore table called a delta row group. A linha inserida não é convertida para a columnstore, até que o rowgroup delta esteja cheio e marcado como fechado.The inserted row is not converted to columnstore until the delta row group is full and is marked as closed. Rowgroups são fechados quando atingem a capacidade máxima de 1.048.576 linhas.Row groups are closed once they reach the maximum capacity of 1,048,576 rows.
  • A atualização de uma linha no formato columnstore é processada como uma exclusão lógica e, em seguida, como uma inserção.Updating a row in columnstore format is processed as a logical delete and then an insert. A linha inserida pode ser armazenada no repositório delta.The inserted row may be stored in the delta store.

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.Batched update and insert operations that exceed the bulk threshold of 102,400 rows per partition-aligned distribution go directly to the columnstore format. 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.However, assuming an even distribution, you would need to be modifying more than 6.144 million rows in a single operation for this to occur. Se o número de linhas de uma determinada distribuição alinhada por partição for menor que 102.400, as linhas vão para o repositório Delta e permanecem lá até que as linhas suficientes tenham sido inseridas ou modificadas para fechar o grupo de linhas ou o índice tenha sido recriado.If the number of rows for a given partition-aligned distribution is less than 102,400 then the rows go to the delta store and stay there until sufficient rows have been inserted or modified to close the row group or the index has been rebuilt.

Operações de carregamento pequenas ou lentasSmall or trickle load operations

Às vezes, pequenas cargas que fluem para o SQL Data Warehouse também são chamadas de cargas lentas.Small loads that flow into SQL Data Warehouse are also sometimes known as trickle loads. Normalmente, elas representam um fluxo quase constante de dados que estão sendo incluídos pelo sistema.They typically represent a near constant stream of data being ingested by the system. No entanto, como esse fluxo é quase contínuo, o volume de linhas não é grande.However, as this stream is near continuous the volume of rows is not particularly large. Frequentemente, os dados ficam consideravelmente abaixo do limite necessário para um carregamento direto no formato columnstore.More often than not the data is significantly under the threshold required for a direct load to columnstore format.

Nessas situações, é melhor levar os dados primeiro ao armazenamento de blobs do Azure e deixá-los se acumularem antes do carregamento.In these situations, it is often better to land the data first in Azure blob storage and let it accumulate prior to loading. Essa técnica é conhecida normalmente como micro envio em lote.This technique is often known as micro-batching.

Número excessivo de partiçõesToo many partitions

Outra coisa a considerar é o impacto de particionamento de suas tabelas columnstore clusterizadas.Another thing to consider is the impact of partitioning on your clustered columnstore tables. Antes do particionamento, o SQL Data Warehouse já divide seus dados em 60 bancos de dados.Before partitioning, SQL Data Warehouse already divides your data into 60 databases. O particionamento divide ainda mais seus dados.Partitioning further divides your data. 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.If you partition your data, then consider that each partition needs at least 1 million rows to benefit from a clustered columnstore index. Se você particionar sua tabela em 100 partições, sua tabela precisará de pelo menos 6.000.000.000 linhas para se beneficiar de um índice columnstore clusterizado (60 distribuições 100 partições 1 milhão linhas).If you partition your table into 100 partitions, then your table needs at least 6 billion rows to benefit from a clustered columnstore index (60 distributions 100 partitions 1 million rows). Se a tabela de cem partições não tiver seis bilhões de linhas, reduza o número de partições ou considere usar uma tabela de heap.If your 100-partition table does not have 6 billion rows, either reduce the number of partitions or consider using a heap table instead.

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.Once your tables have been loaded with some data, follow the below steps to identify and rebuild tables with sub-optimal clustered columnstore indexes.

Recriando índices para melhorar a qualidade de segmentoRebuilding indexes to improve segment quality

Etapa 1: identificar ou criar o usuário que usa a classe de recurso corretaStep 1: Identify or create user which uses the right resource class

Uma maneira rápida de melhorar a qualidade do segmento imediatamente é recriar o índice.One quick way to immediately improve segment quality is to rebuild the index. O SQL retornado pela exibição acima retorna uma instrução ALTER INDEX REBUILD, que pode ser usada para recriar os índices.The SQL returned by the above view returns an ALTER INDEX REBUILD statement which can be used to rebuild your indexes. Ao recriar os índices, não deixe de alocar memória suficiente para a sessão que recria o índice.When rebuilding your indexes, be sure that you allocate enough memory to the session that rebuilds your index. 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.To do this, increase the resource class of a user which has permissions to rebuild the index on this table to the recommended minimum.

Abaixo está um exemplo de como alocar mais memória para um usuário aumentando sua classe de recurso.Below is an example of how to allocate more memory to a user by increasing their resource class. Para trabalhar com classes de recurso, consulte Classes de recurso para gerenciamento de carga de trabalho.To work with resource classes, see Resource classes for workload management.

EXEC sp_addrolemember 'xlargerc', 'LoadUser'

Etapa 2: recriar índices columnstore clusterizados com usuário de classe de recurso superiorStep 2: Rebuild clustered columnstore indexes with higher resource class user

Entre como o usuário da etapa 1 (por exemplo, loaduser), que agora está usando uma classe de recurso mais alta e execute as instruções ALTER INDEX.Sign in as the user from step 1 (e.g. LoadUser), which is now using a higher resource class, and execute the ALTER INDEX statements. Verifique se esse usuário tem a permissão ALTER para as tabelas em que o índice está sendo recriado.Be sure that this user has ALTER permission to the tables where the index is being rebuilt. Estes exemplos mostram como recriar todo o índice columnstore e como recriar uma partição única.These examples show how to rebuild the entire columnstore index or how to rebuild a single partition. Em tabelas grandes, é mais prático recriar índices, uma partição por vez.On large tables, it is more practical to rebuild indexes a single partition at a time.

Como alternativa, em vez de recriar o índice, é possível copiar a tabela para uma nova tabela usando CTAS.Alternatively, instead of rebuilding the index, you could copy the table to a new table using CTAS. Qual é a melhor opção?Which way is best? Para grandes volumes de dados, CTAS é geralmente mais rápido do que ALTER INDEX.For large volumes of data, CTAS is usually faster than ALTER INDEX. Para volumes menores de dados, ALTER INDEX é mais fácil de usar e não exige a troca da tabela.For smaller volumes of data, ALTER INDEX is easier to use and won't require you to swap out the table.

-- 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 recriação de um índice no SQL Data Warehouse é uma operação offline.Rebuilding an index in SQL Data Warehouse is an offline operation. 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.For more information about rebuilding indexes, see the ALTER INDEX REBUILD section in Columnstore Indexes Defragmentation, and ALTER INDEX.

Etapa 3: verificar se melhorou a qualidade do segmento columnstore clusterizadoStep 3: Verify clustered columnstore segment quality has improved

Execute novamente a consulta que identificou a tabela com segmentos de má qualidade e verifique se a qualidade melhorou.Rerun the query which identified table with poor segment quality and verify segment quality has improved. Se a qualidade do segmento não melhorou, é possível que as linhas da tabela sejam muito amplas.If segment quality did not improve, it could be that the rows in your table are extra wide. Considere usar uma classe de recurso maior ou mais DWU durante a recriação de índices.Consider using a higher resource class or DWU when rebuilding your indexes.

Recriando índices com CTAS e alternância de partiçãoRebuilding indexes with CTAS and partition switching

Este exemplo usa a instrução CRIAR TABELAS COMO SELECT (CTAS) e alternância de partição para recriar uma partição de tabela.This example uses the CREATE TABLE AS SELECT (CTAS) statement and partition switching to rebuild a table partition.

-- 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 detalhes sobre como recriar partições usando CTAS, consulte Usando partições no SQL Data Warehouse.For more details about re-creating partitions using CTAS, see Using partitions in SQL Data Warehouse.

Próximas etapasNext steps

Para obter mais informações sobre como desenvolver tabelas, consulte Desenvolvendo tabelas.For more information about developing tables, see Developing tables.