Melhores práticas para conjuntos de SQL dedicados no Azure Synapse Analytics

  • Artigo

Este artigo fornece uma coleção de práticas recomendadas para ajudá-lo a obter o desempenho ideal para pools SQL dedicados no Azure Synapse Analytics. Se você estiver trabalhando com pool SQL sem servidor, consulte Práticas recomendadas para pools SQL sem servidor para obter orientações específicas. Abaixo, você encontrará orientações básicas e áreas importantes nas quais se concentrar ao criar sua solução. Cada seção apresenta um conceito e, em seguida, aponta para artigos mais detalhados que abordam o conceito com mais profundidade.

Carregamento de pools SQL dedicados

Para obter diretrizes de carregamento de pools SQL dedicados, consulte Orientação para carregamento de dados.

Reduzir os custos com a colocação em pausa e o dimensionamento

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

Manter as estatísticas

O pool SQL dedicado pode ser configurado para detetar 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 seus 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 de estatísticas, seja seletivo sobre quais colunas têm estatísticas ou precise da atualização mais frequente. Por exemplo, talvez você queira atualizar colunas de data onde novos valores podem ser adicionados diariamente. Concentre-se em ter estatísticas para colunas envolvidas em junções, colunas usadas na cláusula WHERE e colunas encontradas em GROUP BY.

Informações adicionais sobre estatísticas podem ser encontradas nos artigos Gerenciar estatísticas de tabela, CRIAR ESTATÍSTICAS e ATUALIZAR ESTATÍSTICAS.

Otimizar o desempenho de consultas

Agrupar instruções INSERT em lotes

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

Uma maneira de resolver esse problema é desenvolver um processo que grava em um arquivo e, em seguida, outro processo para carregar periodicamente esse arquivo. Consulte o artigo INSERT para obter mais informações.

Utilize o PolyBase para carregar e exportar dados rapidamente

O pool SQL dedicado dá suporte ao carregamento e exportação de dados por meio de várias ferramentas, incluindo Azure Data Factory, PolyBase e BCP. Para pequenas quantidades de dados em que o desempenho não é essencial, qualquer ferramenta pode ser suficiente para as suas necessidades.

Nota

O PolyBase é a melhor opção quando você está carregando ou exportando grandes volumes de dados ou precisa de um desempenho mais rápido.

Os carregamentos do PolyBase podem ser executados com CTAS ou INSERT INTO. O CTAS minimizará o registro de transações e é a maneira mais rápida de carregar seus dados. O Azure Data Factory também suporta cargas do PolyBase e pode alcançar um desempenho semelhante ao CTAS. O PolyBase suporta vários formatos de arquivo, incluindo arquivos Gzip.

Para maximizar a taxa de transferência ao usar arquivos de texto Gzip, divida os arquivos em 60 ou mais arquivos para maximizar o paralelismo de sua carga. Para um débito total mais rápido, considere carregar dados em simultâneo. Informações adicionais relevantes para esta seção estão incluídas nos seguintes artigos:

Carregar e consultar tabelas externas

O PolyBase não é ideal para consultas. Atualmente, as tabelas PolyBase para pools SQL dedicados oferecem suporte apenas a arquivos de blob do Azure e ao armazenamento do Azure Data Lake. Esses arquivos não têm nenhum recurso de computação para apoiá-los. Como resultado, pools SQL dedicados não podem descarregar esse trabalho e devem ler o arquivo inteiro carregando-o para tempdb que ele possa ler os dados.

Se você tiver várias consultas para consultar esses dados, é melhor carregá-los uma vez e fazer com que as consultas usem a tabela local. Outras diretrizes do PolyBase estão incluídas no artigo Guia para uso do PolyBase .

Distribuir tabelas grandes por hash

Por predefinição, as tabelas são distribuídas por Round Robin. Esse padrão torna mais fácil para os usuários começarem a criar tabelas sem ter que decidir como suas tabelas devem ser distribuídas. As mesas Round Robin podem ter um desempenho suficiente para algumas cargas de trabalho. Mas, na maioria dos casos, uma coluna de distribuição fornece melhor desempenho.

O exemplo mais comum de uma tabela distribuída por uma coluna superando uma tabela round robin é quando duas grandes tabelas de fatos são unidas.

Por exemplo, se você tiver uma tabela de pedidos distribuída por order_id e uma tabela de transações também distribuída por order_id, quando você unir sua tabela de pedidos à sua tabela de transações em order_id, essa consulta se tornará uma consulta de passagem. As operações de movimentação de dados são então eliminadas. Menos passos significam uma consulta mais rápida. Menos movimento de dados também torna as consultas mais rápidas.

Gorjeta

Ao carregar uma tabela distribuída, os dados de entrada não devem ser classificados na chave de distribuição. Fazê-lo irá abrandar as suas cargas.

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

Não crie partições em demasia

Embora o particionamento de dados possa ser eficaz para manter seus dados por meio da troca de partições ou otimizar varreduras com a eliminação de partições, ter muitas partições pode tornar suas consultas mais lentas. Muitas vezes, uma estratégia de particionamento de alta granularidade que pode funcionar bem no SQL Server pode não funcionar bem no pool SQL dedicado.

Ter muitas partições pode reduzir a eficácia dos índices columnstore clusterizados se cada partição tiver menos de 1 milhão de linhas. Pools SQL dedicados particionam automaticamente seus dados em 60 bancos de dados. Assim, se você criar uma tabela com 100 partições, o resultado será 6000 partições. Cada carga de trabalho é diferente, então o melhor conselho é experimentar o particionamento para ver o que funciona melhor para sua carga de trabalho.

Uma opção a considerar é usar uma granularidade menor do que a que você implementou usando o SQL Server. Por exemplo, considere o uso de 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 tamanhos de transação

As instruções INSERT, UPDATE e DELETE são executadas em uma transação. Quando falham, têm de ser revertidos. Para reduzir o potencial de uma longa reversão, minimize o tamanho das transações sempre que possível. A minimização dos tamanhos das transações pode ser feita dividindo as instruções INSERT, UPDATE e DELETE em partes. Por exemplo, se você tiver um INSERT que espera levar 1 hora, você pode dividir o INSERT em quatro partes. Cada corrida será então encurtada para 15 minutos.

Gorjeta

Tire partido dos casos especiais de Registo Mínimo, como CTAS, TRUNCATE, DROP TABLE ou INSERT para esvaziar tabelas, de forma a reduzir o risco de reversão.

Outra forma de eliminar reversões consiste em utilizar Operações Apenas de Metadados, como a mudança de partições para a gestão de dados. Por exemplo, em vez de executar uma instrução DELETE para excluir todas as linhas em uma tabela onde o order_date estava em outubro de 2001, você pode particionar seus dados mensalmente. Em seguida, você pode alternar a partição com dados para uma partição vazia de outra tabela (veja exemplos ALTER TABLE).

Para tabelas que não são particionadas, considere usar um CTAS para gravar os dados que você deseja manter em uma tabela em vez de usar DELETE. Se um CTAS demorar a mesma quantidade de tempo, é muito mais seguro de executar, uma vez que tem um registo mínimo de transações e pode ser cancelado rapidamente, se necessário.

Mais informações sobre o conteúdo relacionado a esta seção estão incluídas nos artigos abaixo:

Reduzir o tamanho dos resultados da consulta

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

Utilizar o tamanho mais pequeno possível da coluna

Ao definir sua DDL, use o menor tipo de dados que suportará seus dados, pois isso melhorará o desempenho da consulta. Esta recomendação é particularmente importante para as colunas CHAR e VARCHAR. Se o maior valor numa coluna for de 25 carateres, defina a coluna como VARCHAR(25). Evite definir todas as colunas de carateres com um comprimento predefinido grande. Além disso, defina colunas como VARCHAR quando isso for tudo o que for necessário, em vez de usar NVARCHAR.

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

Utilize tabelas temporárias de área dinâmica para dados em dados transitórios

Quando você está temporariamente aterrissando dados em pools SQL dedicados, as tabelas de heap geralmente tornam o processo geral mais rápido. Se você estiver carregando dados apenas para prepará-los antes de executar mais transformações, carregar a tabela em uma tabela de pilha será mais rápido do que carregar os dados em uma tabela columnstore clusterizada.

Carregar dados em uma tabela temporária também será carregado muito mais rápido do que carregar uma tabela para armazenamento permanente. As tabelas temporárias começam com um "#" e só são acessíveis pela sessão que as criou. Consequentemente, só podem funcionar em cenários limitados. As tabelas de área dinâmica para dados são definidas na cláusula WITH de uma CREATE TABLE. Se utilizar uma tabela temporária, não se esqueça de também criar estatísticas nessa tabela temporária.

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

Otimizar tabelas columnstore em cluster

Os índices columnstore clusterizados são uma das maneiras mais eficientes de armazenar seus dados no pool SQL dedicado. Por padrão, as tabelas no pool SQL dedicado são criadas como Clustered ColumnStore. Para obter o melhor desempenho das consultas em tabelas columnstore, ter uma boa qualidade de segmento é importante. 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.

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

Como os segmentos columnstore de alta qualidade são importantes, é uma boa ideia usar IDs de usuários que estejam na classe de recursos média ou grande para carregar dados. Usar unidades de armazém de dados mais baixas significa que você deseja atribuir uma classe de recurso maior ao seu usuário de carregamento.

As tabelas columnstore geralmente não enviam dados para um segmento columnstore compactado até que haja mais de 1 milhão de linhas por tabela. Cada tabela de pool SQL dedicada é distribuída em 60 distribuições diferentes. Como tal, as tabelas columnstore não beneficiarão uma consulta, a menos que a tabela tenha mais de 60 milhões de linhas.

Gorjeta

Para tabelas com menos de 60 milhões de 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 de linhas para se beneficiar de um índice columnstore clusterizado. Para uma tabela com 100 partições, ela precisa ter pelo menos 6 bilhões de linhas para se beneficiar de um armazenamento de colunas clusterizadas (60 distribuições 100 partições 1 milhão de linhas).

Se a sua tabela não tiver 6 mil milhões de linhas, tem duas opções principais. Reduza o número de partições ou considere o uso de uma tabela de pilha. Também pode valer a pena experimentar para ver se um melhor desempenho pode ser obtido usando uma tabela de pilha com índices secundários em vez de uma tabela columnstore.

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

Utilize a classe de recursos maior para melhorar o desempenho da consulta

Os pools SQL usam grupos de recursos como uma maneira de alocar memória para consultas. Inicialmente, todos os usuários são atribuídos à pequena classe de recursos, que concede 100 MB de memória por distribuição. Há sempre 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 menos de 6 GB.

Algumas consultas, como associações grandes ou carregamentos para tabelas columnstore em cluster, irão beneficiar de alocações de memória superiores. Algumas consultas, como exames puros, não verão nenhum benefício. A utilização de classes de recursos maiores afeta a simultaneidade. Portanto, convém ter esses fatos em mente antes de mover todos os seus usuários para uma grande classe de recursos.

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

Use uma classe de recurso menor para aumentar a simultaneidade

Se você notar um longo atraso nas consultas do usuário, os usuários podem 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 fiquem na fila. Para determinar se as consultas dos usuários estão na fila, execute SELECT * FROM sys.dm_pdw_waits para ver se alguma linha é retornada.

Os artigos Classes de recursos para gerenciamento de carga de trabalho e sys.dm_pdw_waits fornecerão mais informações.

Utilize DMVs para monitorizar e otimizar as suas consultas

Os pools SQL dedicados têm vários DMVs que podem ser usados para monitorar a execução da consulta. O artigo de monitoramento abaixo orienta você através de instruções passo a passo sobre como exibir detalhes de uma consulta em execução. Para encontrar rapidamente as consultas nestas DMVs, pode ajudar se utilizar a opção LABEL com as suas consultas. Para obter informações adicionais detalhadas, consulte os artigos incluídos na lista abaixo:

Próximos passos

Consulte também o artigo Solução de problemas para problemas e soluções comuns.

Se você precisar de informações não fornecidas neste artigo, pesquise na página de perguntas e respostas da Microsoft para que o Azure Synapse seja um local para fazer perguntas a outros usuários e ao Grupo de Produtos do Azure Synapse Analytics.

Monitorizamos ativamente este fórum para nos certificarmos de que as suas perguntas são respondidas por outro utilizador ou um de nós. Se você preferir fazer suas perguntas sobre o estouro de pilha, também temos um Fórum de estouro de pilha do Azure Synapse Analytics.