Recomendar configurações de banco de dados

Concluído

Antes de ajustar consultas ou solucionar problemas de simultaneidade, é necessário ter a infraestrutura adequada. O Banco de Dados SQL do Azure oferece dois modelos de recursos e várias camadas de serviço. A combinação escolhida define o teto em computação, memória, E/S e armazenamento para sua carga de trabalho. Escolha muito pouco e o desempenho sofre. Escolha demais e desperdice o orçamento.

Comparar modelos de recursos

O Banco de Dados SQL do Azure dá suporte a dois modelos de recursos: vCore e DTU. Eles medem e cobram recursos de forma diferente, portanto, entender a distinção ajuda você a fazer a escolha certa desde o início.

O modelo vCore fornece controle direto sobre núcleos virtuais, memória e armazenamento. Você escolhe a geração de hardware, a camada de serviço e a camada de computação de forma independente. Se você estiver migrando do SQL Server local, esse modelo será mapeado de forma limpa para a CPU física e a memória, o que torna o planejamento de capacidade mais simples. Ele também dá suporte a preços de instância reservada e ao Benefício Híbrido do Azure para economia de custos.

O modelo de DTU agrupa CPU, memória e E/S em uma única unidade chamada DTU ( Unidade de Transação de Banco de Dados ). As camadas baseadas em DTU (Basic, Standard e Premium) oferecem pacotes de recursos pré-configurados. Esse modelo funciona quando você não precisa de controle refinado sobre dimensões de recursos individuais.

Para a maioria das novas implantações, a Microsoft recomenda o modelo vCore. Ele fornece limites de recursos mais altos, maior granularidade de dimensionamento e mais flexibilidade de preços.

Entender as camadas de serviço no modelo vCore

O modelo vCore tem três camadas de serviço: Uso Geral, Comercialmente Crítico e Hiperescala. Cada camada usa uma arquitetura diferente, que afeta o tipo de armazenamento, o desempenho de E/S e a disponibilidade.

Uso Geral separa a computação e o armazenamento. O mecanismo de banco de dados é executado em um nó de computação enquanto os arquivos de dados residem no Armazenamento de Blobs do Azure. A latência de armazenamento normalmente está entre 5 milissegundos e 10 milissegundos. Essa arquitetura fornece preços compatíveis com o orçamento e funciona bem para a maioria das cargas de trabalho de negócios. Se o nó de computação falhar, o Azure Service Fabric moverá o processo para um nó sobressalente e re-anexará os arquivos de armazenamento remoto.

Considere o aplicativo de comércio eletrônico da introdução. Durante o horário comercial normal, o Propósito Geral lida com o volume de pedidos sem dificuldades. Mas durante uma promoção-relâmpago de feriado, a latência de entrada/saída de 5 milissegundos a 10 milissegundos pode não ser rápida o suficiente para o fluxo de finalização de compra.

Business Critical integra computação e armazenamento em cada nó. O mecanismo de banco de dados e os arquivos de dados usam SSDs anexados localmente em um grupo de disponibilidade Always On com três réplicas secundárias. Esse design oferece a menor latência de E/S (de 1 milissegundo a 2 milissegundos em média), a IOPS mais alta (operações de entrada/saída por segundo) e uma réplica somente leitura gratuita que você pode usar para consultas de relatório. A compensação é de custo, cerca de 2,7 vezes mais do que Uso Geral para a mesma contagem de vCore. Para a equipe de comércio eletrônico, o Business Critical fará sentido se suas transações de check-out precisarem de latência consistente de sub-2 milissegundos.

A hiperescala usa uma arquitetura de armazenamento desacoplado com servidores de página independentes e um cache de várias camadas. Ele dá suporte a bancos de dados de até 128 TB, permite zero a quatro réplicas de alta disponibilidade e dimensiona a computação para cima ou para baixo sem copiar dados. Você é cobrado apenas pelo armazenamento alocado, não pelo armazenamento máximo. A hiperescala remove os limites práticos de armazenamento e dimensionamento das outras camadas e é adequada para a maior variedade de cargas de trabalho.

A tabela a seguir resume as principais diferenças:

Característica	Uso Geral	Comercialmente Crítico	Hiperescala
Tipo de armazenamento	Remoto (Armazenamento de Blobs do Azure)	SSD Local	Desacoplado com cache SSD local
Armazenamento máximo	4 TB	4 TB	128 TB
IOPS máximo por vCore	320	4.000	5.500 (SSD local)
Réplicas de disponibilidade	1 (sem réplicas de leitura)	3 + 1 réplica de leitura	0 a 4 (configurável)
Mais adequado para	Cargas de trabalho orientadas ao orçamento	Baixa latência, E/S alta	Bancos de dados grandes, dimensionamento flexível

Escolher uma camada de computação

No modelo vCore, você também escolhe entre duas camadas de computação: provisionada e sem servidor.

A computação provisionada aloca um número fixo de vCores que permanecem disponíveis independentemente da atividade de carga de trabalho. Você paga uma taxa fixa por hora. Essa camada atende cargas de trabalho com consumo de recursos consistente ou previsível, como o aplicativo de comércio eletrônico que processa pedidos ao longo do dia.

A computação sem servidor dimensiona automaticamente os vCores com base na demanda e cobra por segundo para a computação utilizada. Quando o banco de dados está ocioso, ele pode entrar em modo de pausa automática e eliminar totalmente os custos de computação, embora a pausa automática tenha suporte apenas no Propósito Geral. A computação sem servidor em si está disponível para camadas de Uso Geral e Hiperescala. Ele funciona bem para ambientes de desenvolvimento, ferramentas internas ou aplicativos com tráfego intermitente.

Corresponder a configuração à sua carga de trabalho

Agora que você entende as opções, como você decide? Avalie sua carga de trabalho em relação a esses fatores:

• Requisitos de latência: se o aplicativo precisar de latência de E/S em menos de 2 milissegundos consistentemente, escolha Comercialmente Crítico. Para tolerância de latência moderada, Uso Geral (General Purpose) é suficiente.
• Tamanho do armazenamento: se o banco de dados exceder 4 TB ou você espera um crescimento rápido, a Hiperescala é a única opção que acomoda até 128 TB.
• Trabalho intensivo de leitura: crítico para os negócios, inclui uma réplica somente leitura gratuita. A hiperescala dá suporte a réplicas nomeadas para expansão de leitura flexível.
• Sensibilidade de custo: o uso geral com computação provisionada oferece preços previsíveis. A computação sem servidor em Uso Geral ou Hiperescala reduz os custos de cargas de trabalho intermitentes.
• Requisitos de disponibilidade: o Business Critical fornece a resiliência mais alta com três réplicas síncronas e o failover mais rápido. A hiperescala permite configurar o número de réplicas para equilibrar a resiliência com o custo.

Dica

Ao migrar do SQL Server local, use o modelo vCore porque ele é mapeado diretamente para a CPU física e a memória. O modelo de DTU não expõe dimensões de recursos individuais, o que dificulta o planejamento de capacidade para migrações.

Definir configurações no nível do banco de dados

Você escolheu a camada e o modelo de computação. Agora, olhe dentro do banco de dados em si. Várias configurações afetam como o Banco de Dados SQL do Azure lida com paralelismo, otimização de consulta e recuperação. Ajuste essas configurações sem alterar a camada de serviço.

Controlar paralelismo com MAXDOP

Max degree of parallelism (MAXDOP) controla quantos threads de processamento o mecanismo atribui a uma única consulta. O Banco de Dados SQL do Azure usa como padrão 8, que funciona para a maior variedade de cargas de trabalho. Antes de setembro de 2020, novos bancos de dados tinham como padrão 0 e paralelismo ilimitado, o que causou problemas. Uma única consulta analítica pode consumir cada thread disponível, sufocando o fluxo de checkout da CPU.

Você define MAXDOP no nível do banco de dados com ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP. Você também pode definir um valor diferente para réplicas secundárias quando suas cargas de trabalho de leitura e de leitura/gravação tiverem necessidades de concorrência diferentes. Para uma consulta específica, use o OPTION (MAXDOP) indicador. A única instrução: não utilize MAXDOP 0 em produção. O paralelismo ilimitado leva ao esgotamento de recursos, tempo limite de consulta e interrupções de aplicativo.

Permitir que o ajuste automático capture regressões

O otimizador de consulta nem sempre escolhe o melhor plano. As estatísticas ficam obsoletas, as distribuições de dados mudam e um plano que foi rápido ontem torna-se lento hoje. O ajuste automático monitora o desempenho da consulta e aplica correções sem esperar que você observe.

O Banco de Dados SQL do Azure dá suporte a três opções:

• FORCE_LAST_GOOD_PLAN detecta regressões de plano e força o plano rápido anterior. Habilitado por padrão.
• CREATE_INDEX identifica índices ausentes, os cria e verifica a melhoria. Desabilitado por padrão.
• DROP_INDEX remove índices não utilizados e duplicados. Desabilitado por padrão. Índices exclusivos, incluindo índices que dão suporte a chave primária e restrições exclusivas, nunca são descartados.

Cada alteração passa por uma janela de validação, de 30 minutos a 72 horas, dependendo da frequência de consulta. Se o desempenho piorar, a alteração será revertida automaticamente.

ALTER DATABASE CURRENT
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON,
                      CREATE_INDEX = ON,
                      DROP_INDEX = OFF);

Pense no aplicativo de comércio eletrônico durante uma promoção de feriado. Os padrões de consulta mudam à medida que diferentes páginas de produtos aumentam em popularidade. FORCE_LAST_GOOD_PLAN captura essas regressões automaticamente, de modo que uma alteração de plano ruim às 2 da manhã não diminua a velocidade do checkout até que alguém observe na manhã de segunda-feira. Você provavelmente quer deixar CREATE_INDEX e DROP_INDEX fora até que o que eles propõem seja revisto.

Desbloqueie recursos do otimizador com o nível de compatibilidade

Cada banco de dados tem um nível de compatibilidade que determina quais comportamentos de otimizador de consulta estão disponíveis. Novos bancos de dados no Banco de Dados SQL do Azure adotam por padrão o nível 170 ou o nível mais alto disponível. Cada nível desbloqueia um conjunto de recursos de IQP (processamento de consulta inteligente ):

• Nível 150: modo de lote no rowstore, compilação adiada de variável de tabela, sublinhação de função escalar definida pelo usuário (UDF).
• Nível 160: PSP (otimização de plano sensível a parâmetros), feedback de estimativa de cardinalidade.
• Nível 170: otimização opcional do plano de parâmetro.

Os bancos de dados existentes podem ser executados em um nível de compatibilidade inferior porque a Microsoft nunca atualiza automaticamente essa configuração. Um banco de dados criado quando um valor padrão inferior estava em vigor mantém seu nível original. Por exemplo, se você criou um Banco de Dados SQL do Azure em 2024, o banco de dados ainda estará no nível 160 se o nível não for atualizado manualmente. Da mesma forma, se você importou um banco de dados por meio de um arquivo BACPAC, o nível de compatibilidade do banco de dados importado é baseado no nível de compatibilidade do banco de dados de origem. Para subir:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 170;

Não altere essa configuração cegamente na produção. Use o Query Store para capturar uma linha de base de desempenho no nível atual, fazer a atualização em um ambiente de teste e comparar. Se uma consulta regredir, você poderá forçar o plano antigo enquanto você investiga.

Reduza a sobrecarga do cache de planos com OPTIMIZE_FOR_AD_HOC_WORKLOADS

O aplicativo de comércio eletrônico gera consultas de pesquisa de produtos com dezenas de combinações de filtro. Cada texto de consulta exclusivo obtém seu próprio plano compilado no cache, mesmo que essa consulta nunca mais seja executada. Com o tempo, o cache de planos é preenchido com milhares de planos de uso único, empurrando para fora os planos executados com frequência que realmente importam.

OPTIMIZE_FOR_AD_HOC_WORKLOADS resolve esse problema. Quando habilitado, o mecanismo armazena um pequeno stub de plano compilado na primeira execução em vez do plano completo. Somente quando a mesma consulta é executada uma segunda vez o mecanismo compila e armazena em cache o plano completo.

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;

Essa configuração mantém o cache enxuto e garante que os planos para suas consultas mais importantes permaneçam residentes na memória.

Entender a recuperação acelerada do banco de dados

A recuperação acelerada do banco de dados está sempre habilitada no Banco de Dados SQL do Azure. Você não pode desligá-lo, e você não precisa. A ADR (recuperação acelerada de banco de dados) redesenha o processo de recuperação para que o tempo de recuperação permaneça constante, independentemente de quantas transações ativas estavam em execução quando ocorreu uma falha. Ele também fornece reversão de transações instantâneas e truncamento agressivo de log.

ADR armazena versões de linha em um repositório de versões persistentes (PVS) dentro do banco de dados em vez de em tempdb. Dependendo do tamanho da linha que está sendo modificada, as versões são armazenadas em linha em páginas de dados ou fora de linha em uma tabela interna separada. Cargas de trabalho com uso intensivo de gravação podem resultar em aumentos nas divisões de páginas e maior geração de logs, porque cada versão de linha é registrada em logs. Para minimizar essa sobrecarga, mantenha as transações curtas e reduza transações anuladas desnecessárias.

A PVS compartilha o armazenamento alocado do seu banco de dados, então, quando a PVS cresce, o espaço disponível para seus dados diminui. Para monitorar a sobrecarga de PVS fora de linha, consulte sys.dm_tran_persistent_version_store_stats e verifique a coluna persistent_version_store_size_kb, que indica apenas o tamanho das versões fora de linha, mas não inclui as versões em linha que são armazenadas em páginas de dados. Para estabelecer uma linha de base durante cargas de trabalho típicas, compare esse valor com o tamanho total do banco de dados. Se a PVS crescer significativamente além dessa referência, procure por transações de execução longa ou altas taxas de anulação que atrasem a limpeza das versões.

Principais conclusões

O Banco de Dados SQL do Azure fornece dois modelos de recursos, três camadas de serviço e duas camadas de computação. O modelo vCore com Uso Geral abrange a maioria das cargas de trabalho. O Business Critical adiciona latência de sub-2 milissegundos. A hiperescala remove os limites de armazenamento e dimensionamento. Dentro do banco de dados, MAXDOP 8 é o padrão seguro, o ajuste automático captura regressões de plano e a atualização do nível de compatibilidade desbloqueia os recursos de IQP (processamento de consulta inteligente) mais recentes. Habilite OPTIMIZE_FOR_AD_HOC_WORKLOADS para manter o cache do plano limpo e monitorar o uso do armazenamento de PVS da ADR usando sys.dm_tran_persistent_version_store_stats, especialmente em cenários com alta carga de gravação. Em seguida, você explorará como os níveis de isolamento e o controle de simultaneidade afetam as consultas em execução dentro dessa infraestrutura.