Cheat sheet for dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics (Folha de truques para conjuntos de SQL dedicados (anteriormente SQL DW) no Azure Synapse Analytics
Esta folha de truques fraudulentos fornece sugestões úteis e melhores práticas para a criação de soluções dedicadas do conjunto de SQL (anteriormente SQL DW).
O gráfico seguinte mostra o processo de conceção de um armazém de dados com um conjunto de SQL dedicado (anteriormente SQL DW):
Consultas e operações em tabelas
Se já souber de antemão as principais operações e consultas que vão ser executadas no seu armazém de dados, pode priorizar a arquitetura do mesmo para essas operações. Estas operações e consultas poderão incluir:
- Associar uma ou duas tabelas de factos com tabelas de dimensão, filtrar a tabela combinada e, em seguida, anexar os resultados a um data mart.
- Fazer atualizações grandes ou pequenas às tabelas de factos.
- Anexar apenas dados às tabelas.
Saber os tipos de operações antecipadamente ajuda-o a otimizar o design das tabelas.
Migração de dados
Primeiro, carregue os seus dados para Azure Data Lake Storage ou Armazenamento de Blobs do Azure. Em seguida, utilize a instrução COPY para carregar os seus dados para tabelas de teste. Utilize a seguinte configuração:
| Design | Recomendação |
|---|---|
| Distribuição | Round Robin |
| Indexação | Área dinâmica para dados |
| Criação de partições | Nenhuma |
| Classe de Recursos | largerc ou xlargerc |
Saiba mais sobre a migração de dados, o carregamento de dados e o processo de Extração, Carregamento e Transformação (ELT).
Tabelas distribuídas ou replicadas
Utilize as seguintes estratégias, consoante as propriedades da tabela:
| Tipo | Excelente opção para... | Tenha atenção se... |
|---|---|---|
| Replicada | * Tabelas de dimensão pequenas num esquema de estrela com menos de 2 GB de armazenamento após a compressão (compressão~5x) | * Muitas transações de escrita estão em cima da tabela (por exemplo, inserir, upsert, eliminar, atualizar) * Altera frequentemente o aprovisionamento de Unidades de Data Warehouse (DWU) * Utiliza apenas 2 a 3 colunas, mas a tabela tem muitas colunas * Indexa uma tabela replicada |
| Round Robin (predefinição) | * Tabela temporária/de teste * Nenhuma chave de associação óbvia ou uma boa coluna candidata |
* O desempenho está lento devido ao movimento de dados |
| Hash | * Tabelas de factos * Tabelas de dimensão grandes |
* Não é possível atualizar a chave de distribuição |
Sugestões:
- Comece com Round Robin, mas aspire a chegar à estratégia de distribuição de hash para tirar partido de uma arquitetura paralela em escala.
- Certifique-se de que as chaves de hash comuns têm o mesmo formato de dados.
- Não distribua no formato varchar.
- As tabelas de dimensões com chaves de hash comuns para tabelas de factos com operações de associação frequentes podem ser distribuídas com hash.
- Utilize sys.dm_pdw_nodes_db_partition_stats para analisar eventuais assimetrias nos dados.
- Utilize sys.dm_pdw_request_steps para analisar os movimentos de dados por trás das consultas, monitorizar a transmissão de tempo e as operações de baralhar demoram. Esta opção é útil para rever a estratégia de distribuição.
Saiba mais sobre as tabelas replicadas e as tabelas distribuídas.
Indexar as tabelas
A indexação é útil para ler as tabelas rapidamente. Com base nas suas necessidades, pode utilizar um conjunto único de tecnologias:
| Tipo | Excelente opção para... | Tenha atenção se... |
|---|---|---|
| Área dinâmica para dados | * Teste/tabela temporária * Tabelas pequenas com pequenas pesquisas |
* Qualquer pesquisa analisa a tabela completa |
| Índice em cluster | * Tabelas com até 100 milhões de linhas * Tabelas grandes (mais de 100 milhões de linhas) com apenas 1-2 colunas fortemente utilizadas |
* Utilizado numa tabela replicada * Tem consultas complexas que envolvem várias operações de associação e Agrupar Por * Efetua atualizações nas colunas indexadas: é necessária memória |
| Índice columnstore em cluster (CCI) (predefinição) | * Tabelas grandes (mais de 100 milhões de linhas) | * Utilizado numa tabela replicada * Efetua operações de atualização massivas na sua tabela * Sobrepartition your table: row groups do not span across different distribution nodes and partitions (Sobrepartition your table: row groups do not span across different distribution nodes and partitions) |
Sugestões:
- A partir de um índice em cluster, poderá ser útil adicionar um índice não em cluster a uma coluna utilizada muitas vezes para filtragem.
- Tenha cuidado com a forma como gere a memória em tabelas com CCI. Quando carrega dados, é importante que o utilizador (ou a consulta) beneficie de uma grande classe de recursos. Certifique-se de que evita cortar e criar vários grupos pequenos de linhas comprimidas.
- No Gen2, as tabelas CCI são colocadas em cache localmente nos nós de computação para maximizar o desempenho.
- Em CCI, a compressão incorreta dos grupos de linhas pode provocar um desempenho lento. Se tal ocorrer, reconstrua ou reorganize o CCI. Recomenda-se, pelo menos, 100 000 linhas por grupo de linhas comprimido. O ideal é 1 milhão de linhas num grupo de linhas.
- Com base no tamanho e na frequência de carga incremental, é útil automatizar a reorganização ou a recriação dos índices. A limpeza minuciosa é sempre útil.
- Corte os grupos de linhas de forma estratégica. Até que ponto os grupos de linhas abertos são grandes? Quantos dados espera carregar nos próximos dias?
Saiba mais sobre os índices.
Criação de partições
Pode criar partições da tabela se tiver uma tabela de factos grande (mais de mil milhões de linhas). Em 99 por cento dos casos, a chave de partição deve basear-se numa data.
Com as tabelas de teste que requerem ELT, pode tirar partido das partições. Facilitam a gestão do ciclo de vida dos dados. Tenha cuidado para não sobreparticionar o facto ou a tabela de teste, especialmente num índice columnstore agrupado.
Saiba mais sobre as partições.
Carregamento incremental
Se pretender carregar os seus dados de forma incremental, comece por garantir que aloca mais classes de recursos ao carregamento. Isto é particularmente importante ao carregar em tabelas com índices columnstore em cluster. Veja classes de recursos para obter mais detalhes.
Recomendamos que utilize o PolyBase e o ADF V2 para automatizar os pipelines ELT no seu armazém de dados.
Para um grande lote de atualizações nos seus dados históricos, considere utilizar um CTAS para escrever os dados que pretende manter numa tabela em vez de utilizar INSERT, UPDATE e DELETE.
Manter as estatísticas
É importante atualizar as estatísticas à medida que ocorrem alterações significativas nos seus dados. Veja atualizar as estatísticas para determinar se ocorreram alterações significativas . As estatísticas atualizadas otimizam os seus planos de consulta. Se achar que manter todas as estatísticas demora muito tempo, seja mais seletivo quanto às colunas que as têm.
Também pode definir a frequência das atualizações. Por exemplo, poderá querer atualizar as colunas de data, onde podem ser adicionados novos valores diariamente. Vai beneficiar mais com estatísticas em colunas envolvidas em associações, colunas utilizadas na cláusula WHERE e colunas que se encontram em GROUP BY.
Saiba mais sobre as estatísticas.
Classe de recursos
Os grupos de recursos são utilizados como forma de alocar memória a consultas. Se precisar de mais memória para melhorar a velocidade das consultas ou do carregamento, deve alocar mais classes de recursos. Como contrapartida, a utilização de classes de recursos maiores afeta a simultaneidade. É preciso ter este facto em conta antes de mudar todos os utilizadores para uma classe de recursos grande.
Se reparar em que as consultas demoram demasiado tempo, confirme se os utilizadores não são executados em classes de recursos grandes. Estas classes consomem muitos blocos de simultaneidade e podem fazer com que outras consultas sejam colocadas em fila.
Por fim, ao utilizar o Gen2 do conjunto de SQL dedicado (anteriormente SQL DW), cada classe de recursos obtém 2,5 vezes mais memória do que a Gen1.
Saiba mais como trabalhar com classes de recursos e a simultaneidade.
Reduza os custos
Uma das principais funcionalidades do Azure Synapse é a capacidade de gerir recursos de computação. Pode colocar em pausa o conjunto de SQL dedicado (anteriormente SQL DW) quando não estiver a utilizá-lo, o que interrompe a faturação dos recursos de computação. Pode dimensionar os recursos para satisfazer as suas necessidades em termos de desempenho. Par pôr em pausa, utilize o portal do Azure ou o PowerShell. Para dimensionar, utilize o portal do Azure, o PowerShell, o T-SQL ou uma API REST.
Agora, dimensione automaticamente sempre que quiser com as Funções do Azure:
Otimizar a arquitetura para desempenho
Recomendamos olhar para a Base de Dados SQL e o Azure Analysis Services numa arquitetura hub-and-spoke. Esta solução pode proporcionar isolamento de cargas de trabalho entre diferentes grupos de utilizadores e utilizar, ao mesmo tempo, funcionalidades avançadas de segurança da Base de Dados SQL e do Azure Analysis Services. Também é uma forma de dar simultaneidade ilimitada aos seus utilizadores.
Saiba mais sobre arquiteturas típicas que tiram partido do conjunto de SQL dedicado (anteriormente SQL DW) no Azure Synapse Analytics.
Implemente com um clique nos spokes nas bases de dados SQL a partir do conjunto de SQL dedicado (anteriormente SQL DW):