Práticas recomendadas para pools de SQL dedicados no Azure Synapse Analytics

  • Artigo

Este artigo fornece uma coleção de práticas recomendadas para ajudar você a obter o desempenho ideal para pools de SQL dedicados no Azure Synapse Analytics. Se você estiver trabalhando com um pool de SQL sem servidor, confira Práticas recomendadas para pools de SQL sem servidor para obter orientação específica. Abaixo você encontrará orientações básicas e áreas de enfoque importantes ao criar sua solução. Cada seção apresenta um conceito e sugere artigos mais detalhados que abordam o conceito com maior profundidade.

Carregamento de pools de SQL dedicados

Para obter diretrizes sobre como carregar pools de SQL dedicados, confira Diretrizes para carregar dados.

Reduzir custos com pausa e dimensionamento

Para obter mais informações sobre como reduzir os custos por meio de pausa e escalonamento, confira Gerenciar computação.

Manter as estatísticas

O pool de SQL dedicado pode ser configurado para detectar e criar estatísticas automaticamente em colunas. Os planos de consulta criados pelo otimizador são tão bons quanto as estatísticas disponíveis.

Recomendamos que você habilite AUTO_CREATE_STATISTICS para os bancos de dados e mantenha as estatísticas atualizadas diariamente, ou após cada carregamento, para garantir que as estatísticas nas colunas usadas em suas consultas estejam sempre atualizadas.

Para reduzir o tempo de manutenção das estatísticas, seja seletivo quanto a quais colunas terão estatísticas ou precisarão de atualizações mais frequentes. Por exemplo, convém atualizar as colunas de data, onde novos valores podem ser adicionados diariamente. Concentre-se em gerar estatísticas para colunas envolvidas em junções, colunas usadas na cláusula WHERE e colunas encontradas em GROUP BY.

Para ver informações adicionais sobre estatísticas, leia os artigos Gerenciar estatísticas de tabela, CREATE STATISTICS e UPDATE STATISTICS.

Desempenho de consulta de ajuste

Agrupar instruções INSERT em lotes

Um carregamento único para uma tabela pequena com uma instrução INSERT como INSERT INTO MyLookup VALUES (1, 'Type 1') pode ser a melhor abordagem, dependendo de suas necessidades. No entanto, se você precisar carregar milhares ou milhões de linhas em um dia, é provável que instruções INSERTS singleton não sejam ideais.

Para resolver esse problema, você pode desenvolver um processo em que seja feita a gravação em um arquivo e outro que carregue esse arquivo periodicamente. Leia o artigo INSERT para obter mais informações.

Use o PolyBase para carregar e exportar dados rapidamente

O pool de SQL dedicado é compatível com o carregamento e exportação dos dados por meio de várias ferramentas, incluindo o Azure Data Factory, PolyBase e BCP. Para pequenas quantidades de dados em que o desempenho não é essencial, qualquer ferramenta poderá ser suficiente para satisfazer suas necessidades.

Observação

O PolyBase é a melhor opção de carregamento ou exportação de grandes volumes de dados ou há a necessidade de um desempenho mais rápido.

As cargas do PolyBase podem ser executadas usando CTAS ou INSERT INTO. Nesses casos, o CTAS minimiza o registro em log das transações e é o modo mais rápido de carregar os dados. O Azure Data Factory também dá suporte a cargas de PolyBase e pode alcançar um desempenho semelhante ao do CTAS. O PolyBase dá suporte a vários formatos de arquivo, incluindo Gzip.

Para maximizar a taxa de transferência ao usar os arquivos de texto Gzip, divida os arquivos em 60 ou mais arquivos para maximizar o paralelismo de sua carga. Para ter uma taxa de transferência total mais rápida, considere carregar os dados simultaneamente. Para ver informações adicionais relevantes desta seção, leia os seguintes artigos:

Carregar e consultar tabelas externas

O PolyBase não é a melhor opção para consultas. No momento, as tabelas PolyBase de pools de SQL dedicados são compatíveis apenas com os arquivos de blob do Azure e o Azure Data Lake Storage. Esses arquivos não têm quaisquer recursos de computação de backup. Como resultado, os pools de SQL dedicados não podem descarregar esse trabalho e devem ler o arquivo inteiro carregando-o em tempdb para realizar a leitura dos dados.

Caso você tenha muitas consultas desses dados, será melhor carregá-los uma vez e fazer com que as consultas usem a tabela local. Para ver mais orientações sobre Polybase, leia o artigo Guia de uso do PolyBase.

Tabelas grandes com distribuição Hash

Por padrão, as tabelas são distribuídas pelo método Round Robin. Esse padrão facilita que os usuários comecem a criar tabelas sem precisar decidir sobre como as tabelas deverão ser distribuídas. As tabelas Round Robin podem ser suficientes para algumas cargas de trabalho. Mas, na maioria dos casos, uma coluna de distribuição tem um melhor desempenho.

O exemplo mais comum de uma tabela distribuída por uma coluna suplantando uma tabela de Round Robin acontece quando duas tabelas de fatos grandes são unidas.

Ou seja, quando há tabela de pedidos distribuída por order_id, e uma tabela de transações também distribuída por order_id, unir essas duas tabelas no order_id faz com que essa consulta se torne uma consulta passagem. As operações de movimentação de dados então são eliminadas. Menos etapas significam uma consulta mais rápida. Menos movimento de dados também resulta em consultas mais rápidas.

Dica

Quando uma tabela distribuída é carregada, seus dados de entrada não devem ser classificados na chave de distribuição. Pois isso retardará as cargas.

Os links de artigo fornecidos abaixo fornecerão detalhes adicionais sobre como melhorar o desempenho por meio da seleção de uma coluna de distribuição. Você também pode encontrar informações sobre como definir uma tabela distribuída na cláusula WITH da instrução CREATE TABLE:

Não estender a partição

Embora o particionamento dos dados seja eficiente para manter seus dados na troca de partições ou otimizar as digitalizações eliminando partições, ter muitas partições pode reduzir a velocidade de suas consultas. Uma estratégia de particionamento de alta granularidade que funciona bem no SQL Server, com frequência não funciona tão bem no pool de SQL dedicado.

Ter muitas partições poderá reduzir a eficiência dos índices columnstore clusterizados se cada partição tiver menos de 1 milhão de linhas. Os pools de SQL dedicados particionam automaticamente seus dados em 60 bancos de dados. Portanto, se você criar uma tabela com 100 partições, o resultado será de 6.000 partições. Cada carga de trabalho é diferente. Assim, o melhor conselho é fazer experiências com o particionamento para ver o que funciona melhor para sua carga de trabalho.

Uma opção a ser considerada é usar uma granularidade menor do que a implementada usando SQL Server. Por exemplo, considere usar partições semanais ou mensais, em vez de partições diárias.

Mais informações sobre particionamento são detalhadas no artigo Particionamento de tabela.

Minimizar os tamanhos das transações

As instruções INSERT, UPDATE e DELETE são executadas em uma transação. Em caso de falha, elas devem ser revertidas. Para reduzir possibilidade de uma reversão longa, minimize os tamanhos das transações sempre que possível. Isso pode ser feito dividindo as instruções INSERT, UPDATE e DELETE em partes. Por exemplo, se você prevê que uma instrução INSERT seja executada em uma hora, divida-a em quatro partes. Assim, cada execução levará apenas 15 minutos.

Dica

Aproveite os casos especiais de Registro em Log Mínimo, como CTAS, TRUNCATE, DROP TABLE ou INSERT, para esvaziar as tabelas, reduzindo o risco de reversão.

Outra maneira de eliminar as reversões é usar as operações de Metadados Somente, como a troca de partição para o gerenciamento de dados. Por exemplo, em vez de executar uma instrução DELETE para excluir todas as linhas de uma tabela em que a order_date constava em outubro de 2001, você pode particionar esses dados por mês. Em seguida, você pode alternar a partição com os dados de uma partição vazia de outra tabela (veja os exemplos de ALTER TABLE).

Para tabelas não particionadas, considere usar um CTAS para gravar os dados que quer manter em uma tabela, em vez de usar EXCLUIR. Se um CTAS demorar a mesma quantidade de tempo, será muito mais seguro executá-lo quando ele tiver um log de transação mínimo e puder ser cancelado rapidamente, se necessário.

Para ver informações adicionais sobre o conteúdo relacionado a esta seção, leia os artigos abaixo:

Reduzir tamanhos de resultados de consulta

Reduzir os tamanhos dos resultados da consulta ajuda a evitar problemas do lado do cliente causados por resultados de consulta grandes. É possível editar a consulta para reduzir o número de linhas retornadas. Algumas ferramentas de geração de consulta permitem que você adicione a sintaxe "primeiras N" a cada consulta. Você também pode executar CETAS no resultado da consulta para uma tabela temporária e, em seguida, usar a exportação do PolyBase para o processamento de nível inferior.

Use o menor tamanho de coluna possível

Para melhorar o desempenho da consulta, use o menor tipo de dados compatível com seus dados quando definir uma DDL. Essa recomendação é particularmente importante para colunas CHAR e VARCHAR. Se o maior valor em uma coluna for 25 caracteres, então, defina a coluna como VARCHAR(25). Evite definir todas as colunas de caractere para um tamanho grande padrão. Também é importante salientar que, em vez de usar colunas NVARCHAR, você deverá definir colunas como VARCHAR somente quando estritamente necessário.

Leia os artigos Visão geral da tabela, Tipos de dados de tabela e CREATE TABLE para obter uma visão mais detalhada dos conceitos essenciais relevantes às informações acima.

Usar tabelas de heap temporárias para dados transitórios

Quando os dados estiverem sendo temporariamente destinados a pools de SQL dedicados, as tabelas heap geralmente realizarão o processo de maneira mais rápida. Se você estiver carregando dados apenas para prepará-los antes de executar mais transformações, carregar a tabela na tabela heap será muito mais rápido do que carregar os dados em uma tabela columnstore clusterizada.

O carregamento de dados em uma tabela temporária também será muito mais rápido que o carregamento em um armazenamento permanente. As tabelas temporárias começam com um "#" e só podem ser acessadas por meio da sessão que a criou. Consequentemente, elas só podem funcionar em cenários limitados. As tabelas de heap são definidas na cláusula WITH de CREATE TABLE. Se você usar uma tabela temporária, lembre-se também de criar estatísticas nela.

Para mais informações, leia os artigos Tabelas temporárias, CREATE TABLE e CREATE TABLE AS SELECT.

Otimizar tabelas columnstore clusterizadas

Os índices columnstore clusterizados são uma das maneiras mais eficientes de armazenar os dados no pool de SQL dedicado. Por padrão, as tabelas no pool de SQL dedicado são criadas como ColumnStore Clusterizado. É importante ter um segmento de boa qualidade para obter o melhor desempenho para as consultas nas tabelas columnstore. Quando as linhas são gravadas nas tabelas columnstore sob pressão da memória, a qualidade do segmento columnstore pode ficar prejudicada.

A qualidade de segmento pode ser medida pelo número de linhas em um Grupo de Linhas compactado. Confira as Causas da má qualidade de índice columnstore no artigo sobre Índices de tabela para obter instruções passo a passo sobre como detectar e melhorar a qualidade do segmento para as tabelas columnstore clusterizadas.

Como os segmentos columnstore de alta qualidade são importantes, é uma boa ideia usar IDs de usuários que estão na classe de recursos de médio ou grande para carregamento de dados. Usar unidades de data warehouse menores significa que você deseja atribuir uma classe de recursos maior ao usuário que está carregando.

As tabelas columnstore geralmente não enviam dados para um segmento columnstore compactado até que haja mais de 1 milhão linhas por tabela. Cada tabela de pool de SQL dedicada é disseminada em 60 distribuições diferentes. Sendo assim, as tabelas de columstore não serão úteis para uma consulta a menos que a tabela tenha mais de 60 milhões de linhas.

Dica

No caso de tabelas com menos de 60 milhões linhas, ter um índice columnstore pode não ser a solução ideal.

Se você particionar seus dados, cada partição precisará ter 1 milhão linhas para se beneficiar de um índice columnstore clusterizado. No caso de uma tabela com 100 partições, ela precisa ter pelo menos 6 bilhões de linhas para se beneficiar de um columnstore clusterizado (60 distribuições 100 partições 1 milhão de linhas).

Se sua tabela não tem 6 bilhões de linhas, você tem duas opções principais. Reduzir o número de partições ou considerar usar uma tabela heap. Também pode valer a pena experimentar usar uma tabela heap com índices secundários em vez de uma tabela columnstore para ver se você consegue obter um melhor desempenho.

Ao consultar uma tabela columnstore, as consultas serão executadas mais rapidamente se você selecionar apenas as colunas necessárias. Mais informações sobre índices de tabela e columnstore podem ser encontradas nos artigos abaixo:

Usar uma classe maior de recursos para melhorar o desempenho da consulta

Os pools de SQL usam grupos de recursos como uma forma de alocar memória para as consultas. Inicialmente, todos os usuários são atribuídos à classe de recurso pequena, que concede 100 MB de memória por distribuição. Sempre haverá 60 distribuições. Cada distribuição recebe um mínimo de 100 MB. A alocação total de memória em todo o sistema é de 6.000 MB, ou pouco abaixo de 6 GB.

Determinadas consultas, como junções grandes ou cargas para as tabelas columnstore clusterizado, se beneficiarão das alocações de memória maiores. Algumas consultas não serão beneficiadas, como as de digitalização pura. A utilização de classes de recursos maiores afeta a simultaneidade. Portanto, convém lembrar disso antes de mover todos os seus usuários para uma classe de recursos grande.

Para obter informações adicionais sobre classes de recursos, leia o artigo Classes de recursos para gerenciamento de carga de trabalho.

Usar uma classe de recurso menor para aumentar a simultaneidade

Se você notar um longo atraso nas consultas de usuário, os usuários poderão estar sendo executados em classes de recursos maiores. Esse cenário promove o consumo de slots de simultaneidade, o que pode fazer com que outras consultas sejam colocadas em fila. Para determinar se as consultas dos usuários estão em fila, execute SELECT * FROM sys.dm_pdw_waits para ver se alguma linha é retornada.

Os artigos Classes de recursos para gerenciamento da carga de trabalho e sys.dm_pdw_waits têm mais informações sobre esse processo.

Usar DMVs para monitorar e otimizar suas consultas

Os pools de SQL dedicados têm várias DMVs que podem ser usadas para monitorar a execução da consulta. O artigo de acompanhamento abaixo apresenta instruções passo a passo sobre como visualizar os detalhes de uma consulta em execução. Para encontrar rapidamente as consultas nessas DMVs, usar a opção LABEL com suas consultas poderá ajudar. Para obter mais informações detalhadas, leia os artigos incluídos na lista abaixo:

Próximas etapas

Leia também artigo de Solução de problemas para conhecer os problemas e as soluções comuns.

Se você está buscando informações que não encontrou neste artigo, pesquise a Página de perguntas Microsoft Q&A sobre o Azure Synapse, onde você poderá publicar perguntas para outros usuários e para o Grupo de Produtos do Azure Synapse Analytics.

Monitoramos ativamente esse fórum para garantir que suas perguntas sejam respondidas por outro usuário ou um de nós. Caso você prefira fazer perguntas sobre o Stack Overflow, também temos um Fórum sobre o Stack Overflow do Azure Synapse Analytics.