Obter insights de desempenho quando precisar e acelere o desempenho sem alterações de código
- 10 minutos
O SQL Server 2022 fornece recursos internos para reduzir o tempo de ajuste de consulta, incluindo o Repositório de Consultas e os recursos de Processamento de Consultas Inteligente (IQP) de próxima geração, para ajudá-lo a obter um desempenho mais rápido e consistente sem alterações de código.
Desafios para ajuste de consulta
Desenvolvedores e especialistas em SQL concordam que, embora algumas consultas apenas funcionem, algumas situações de desempenho podem surgir para consultas que exigem exercícios de solução de problemas de desempenho e ajuste de consulta. Ajustar o desempenho da consulta pode ser um processo caro e muitas vezes longo.
Soluções para ajuste de consulta usando o Repositório de Consultas
O Repositório de Consultas é um conjunto interno de estatísticas de desempenho de consulta armazenadas em um banco de dados de usuário. O Repositório de Dados de Consultas captura automaticamente um histórico das consultas, planos e estatísticas de runtime e mantém essas informações para sua análise. Ele separa os dados por janelas por hora, permitindo que você veja os padrões de uso do banco de dados e entenda quando as alterações aos planos de consulta ocorreram no servidor. Para obter mais informações, consulte Monitorar desempenho usando o Repositório de Consultas.
O SQL Server 2022 inclui aprimoramentos importantes para o Repositório de Consultas para reduzir a quantidade de tempo para ajuste de consulta.
Repositório de Consultas ativado por padrão
Antes do SQL Server 2022, o Repositório de Consultas deve ser habilitado usando a instrução ALTER DATABASE do T-SQL. Para o SQL Server 2022, qualquer novo banco de dados criado terá o Repositório de Consultas habilitado por padrão. Os bancos de dados que foram restaurados de versões anteriores do SQL Server manterão as configurações de Repositório de Consultas capturadas quando o banco de dados tiver sido copiado em backup. O Repositório de Consultas teve vários aprimoramentos desde sua criação no SQL Server 2016 que permitem que os usuários habilitem o Repositório de Consultas sem afetar significativamente o desempenho do aplicativo. Além disso, várias novas configurações são possíveis no Repositório de Consultas para permitir que os usuários controlem com mais facilidade como as informações de desempenho de consulta são capturadas e limpas. Os usuários podem desabilitar o Repositório de Consultas a qualquer momento usando a instrução T-SQL ALTER DATABASE.
Dicas do Repositório de Consultas
As dicas do Repositório de Consultas fornecem um método fácil de usar para formatar planos de consulta sem alterar o código do aplicativo. Você pode fazer qualquer consulta armazenada no Repositório de Consultas e usar procedimentos armazenados do sistema para aplicar uma dica de consulta. A dica de consulta afeta o plano de consulta com a intenção de melhorar o desempenho da consulta, sem alterar o código do aplicativo. Por exemplo, você pode aplicar uma dica de repositório de consultas para exigir que uma consulta use um valor específico MAXDOP sem alterar o texto da consulta.
As dicas do repositório de consultas não devem ser usadas como uma etapa normal para ajustar o desempenho da consulta, mas podem ser uma ferramenta útil para ajuste de consulta, especialmente se você não puder alterar o texto da consulta em um aplicativo. Além disso, alguns novos recursos de Processamento de Consultas Inteligentes usam uma dica de repositório de consultas. Você pode exibir quaisquer dicas de repositório de consultas persistentes na exibição de catálogo sys.query_store_query_hints. Para obter mais informações, confira as Dicas do Repositório de Consultas.
Repositório de Consultas para réplicas de leitura
Embora o Repositório de Consultas seja benéfico para reduzir o tempo necessário para ajustar consultas ou identificar facilmente problemas de desempenho de consulta, as informações de desempenho só estão disponíveis para consultas executadas na réplica primária em um grupo de disponibilidade Always On. No SQL Server 2022, uma nova opção está disponível usando a instrução ALTER DATABASE do T-SQL para habilitar o Repositório de Consultas para coletar informações de desempenho para consultas somente leitura executadas em réplicas secundárias. Todas as informações de desempenho para todas as réplicas são mantidas na réplica primária. Novas informações são capturadas no Repositório de Consultas para indicar qual réplica está associada a uma consulta ou plano de consulta.
Observação
O sinalizador de rastreamento 12606 é necessário para habilitar o Repositório de Consultas para réplicas secundárias.
Repositório de consultas para Processamento de Consultas Inteligentes
Embora o Repositório de Consultas colete as principais informações de desempenho para consultas, o processador de consultas no SQL Server 2022 também usará o Repositório de Consultas para manter informações para acelerar o desempenho da consulta. Esses recursos incluem força de plano otimizado, comentários de concessão de memória, comentários de modelo de CE (estimativa de cardinalidade) e comentários de grau de paralelismo (DOP).
Soluções para um desempenho mais rápido com a próxima geração de Processamento de Consultas Inteligentes
O Processamento de Consultas Inteligente (IQP) é uma família de recursos integrados ao processador de consultas no mecanismo de banco de dados, projetadas para acelerar o desempenho sem alterações de código. A próxima geração do Processamento de Consultas Inteligente é criada com base em recursos encontrados no SQL Server 2017 e 2019, conforme visto no diagrama a seguir:
Como você pode ver, houve vários recursos do IQP que faziam parte do SQL Server 2017 e do SQL Server 2019. O SQL Server 2022 adiciona vários novos recursos para IQP. Você pode se manter atualizado com todas as novidades sobre as funcionalidades do IQP no Processamento de consultas inteligentes em bancos de dados do SQL. Vamos examinar cada uma dessas funcionalidades.
O mecanismo de banco de dados usa dois princípios para tomar decisões sobre o Processamento de Consultas Inteligentes:
- Evite causar regressões de desempenho de consulta usando um novo método ou automação.
- Forneça um método no nível do banco de dados ou da consulta para desabilitar uma funcionalidade específica do IQP. Você pode escolher qual recurso IQP você deseja habilitar no nível de banco de dados ou consulta, usando outro recurso IQP, dependendo do nível de compatibilidade do banco de dados.
Funcionalidades após a atualização para o SQL Server 2022
Se você atualizar para o SQL Server 2022, haverá novos recursos para acelerar o desempenho independentemente do nível de compatibilidade do banco de dados para o seu banco de dados. O nível de compatibilidade permite que você aproveite os novos recursos mesmo se precisar usar um nível de compatibilidade de banco de dados de uma versão anterior do SQL Server. Para saber mais, confira Certificação de Compatibilidade.
Funções de percentil aproximado
O SQL Server inclui duas funções T-SQL (Transact-SQL) para ajudar a carga de trabalho analítica a calcular um percentil de um intervalo de valores:
- PERCENTILE_CONT
- PERCENTILE_DISC
O SQL Server 2022 fornece um equivalente aproximado a essas duas funções:
- APPROX_PERCENTILE_CONT
- APPROX_PERCENTILE_DISC
As funções percentil aproximadas podem ser úteis para cargas de trabalho analíticas com conjuntos de dados excepcionalmente grandes. Essas funções terão um desempenho mais rápido e a implementação garante uma taxa de erro de até 1,33% dentro de uma probabilidade de 99%.
Forçar o plano otimizado
A imposição de plano otimizado é uma nova funcionalidade no SQL Server 2022 destinada a reduzir o tempo necessário para compilar determinadas consultas se o plano de consulta for forçado no Repositório de Consultas.
Algumas consultas por sua natureza podem levar um tempo significativo para serem compiladas. A imposição de plano otimizado fornece um método para reduzir o tempo necessário para compilar uma consulta armazenando nas etapas de compilação do Repositório de Consultas para consultas qualificadas que têm planos de consulta forçados no Repositório de Consultas. A imposição de plano de consulta permite que você bloqueie um plano de consulta para uma consulta específica. Na próxima vez que uma consulta precisar ser compilada que tenha o plano otimizado habilitado, as etapas de compilação serão usadas para acelerar significativamente a fase de compilação para executar uma consulta.
Para obter mais informações, confira Imposição de plano otimizado com Repositório de Consultas.
Recursos de IQP do SQL Server 2022 usando o nível de compatibilidade do banco de dados 140 ou superior
Você pode obter mais recursos de Processamento de Consultas Inteligentes para aprimorar os comentários de concessão de memória no SQL Server 2022 se estiver usando um nível de compatibilidade de banco de dados 140 ou superior. Os comentários de concessão de memória foram introduzidos no SQL Server 2017 (modo de lote) e no SQL Server 2019 (modo de linha). Os comentários de concessão de memória são um mecanismo em que o processador de consultas aprenderá com os comentários de execução a ajustar uma concessão de memória para execuções adicionais, evitando ou reduzindo despejos de tempdb e esperas RESOURCE_SEMAPHORE.
Percentis de comentários de concessão de memória
Antes do SQL Server 2022, os comentários de concessão de memória eram baseados na execução mais recente de uma consulta específica. Isso pode resultar em alguns casos de diferentes ajustes de comentários, o que pode levar o processador de consultas a desabilitar comentários de concessão de memória para uma consulta específica. No SQL Server 2022, os comentários de concessão de memória usam um método percentil para examinar as concessões de memória em várias execuções antes de usar um feedback de concessão de memória.
Persistência de comentários de concessão de memória
Antes do SQL Server 2022, os comentários de concessão de memória só eram armazenados em um plano armazenado em cache na memória. Se o plano de cache tivesse sido removido, os comentários de concessão de memória teriam que ser recalculados em novas execuções de consulta. No SQL Server 2022 com o Repositório de Consultas habilitado, os comentários de concessão de memória serão mantidos no Repositório de Consultas. Você pode exibir a persistência de comentários de concessão de memória na exibição de catálogo sys.query_store_plan_feedback.
Para saber mais, confira Feedback de concessão de memória.
Recursos de IQP do SQL Server 2022 usando o nível de compatibilidade do banco de dados 160 ou superior
Você pode obter mais recursos de Processamento de Consulta Inteligente, como otimização de Plano Sensível a Parâmetros, aprimoramentos na Estimativa de Cardinalidade e grau de paralelismo se você usar um nível de compatibilidade de banco de dados 160 ou superior.
Otimização do plano confidencial de parâmetro
Quando uma consulta é compilada, o plano de execução criado leva em conta os valores de todos os parâmetros usados em consultas em um procedimento armazenado ou consulta parametrizada. Esse conceito é chamado de detecção de parâmetros. Somente um plano de consulta pode existir em cache para instruções em um procedimento armazenado ou consulta parametrizada. Na maioria dos casos, isso não resulta em problemas de desempenho para aplicativos. No entanto, há situações em que os dados recuperados para consultas com base em parâmetros podem ser distorcidos ou não distribuídos uniformemente. Nesses casos, o plano de cache único pode não ser ideal para valores de parâmetro diferentes. Esse problema é conhecido como um plano sensível a parâmetros.
No SQL Server 2022, o otimizador pode detectar cenários de planos sensíveis a parâmetros e armazenar em cache vários planos para o mesmo procedimento armazenado ou consulta parametrizada. O otimizador usa um conceito chamado variantes de consulta para agregar conjuntos de valores de parâmetro para corresponder a um plano de consulta mais adequado para esses valores de parâmetro.
Para obter mais informações, consulte Otimização do Plano de Sensibilidade de Parâmetros.
Comentários sobre CE (estimativa de cardinalidade)
No SQL Server 2014 com o nível de compatibilidade do banco de dados 120, a Microsoft começou a usar um novo modelo dentro do processador de consultas para fazer determinadas suposições sobre a estimativa de cardinalidade para determinados padrões de consulta. Em alguns casos, o novo modelo gerou um plano de consulta mais correto, mas pode resultar em um desempenho mais lento do que com o modelo CE herdado. Os cenários de modelo CE incluem correlação, contenção de junção e meta de linha. Desde o SQL Server 2014, várias opções foram incluídas para usar o modelo CE herdado ou controlar o comportamento de CE no nível do banco de dados ou no nível da consulta com sinalizadores de rastreamento ou dicas de consulta.
No SQL Server 2022 com o Repositório de Consultas habilitado, o otimizador avaliará consultas altamente repetitivas que correspondem a padrões para cenários de modelo de CE em que o modelo pode estar fazendo uma suposição incorreta. Em seguida, o otimizador tentará testar e verificar se uma dica de consulta pode ser usada para permitir que a consulta seja executada mais rapidamente. Após a verificação de um desempenho mais rápido, uma dica de consulta será mantida no Repositório de Consultas a ser usado para execuções de consulta futuras. Você pode ver as dicas de consulta aplicadas para comentários de CE na exibição de catálogo sys.query_store_query_hints e detalhes de comentários de CE na exibição de catálogo sys.query_store_plan_feedback. Os comentários de CE não serão usados se o modelo de CE herdado tiver sido habilitado, se um plano de consulta for forçado no repositório de consultas ou se uma consulta tiver dicas existentes do repositório de consultas.
Para obter mais informações, consulte Estimativa de cardinalidade.
Comentários de DOP (grau de paralelismo)
Em alguns casos, o otimizador no SQL Server executará partes do plano de consulta (chamados operadores) usando paralelismo com vários threads simultâneos. O número de threads usadas para um operador de plano de consulta é chamado de DPOP (grau de paralelismo). O SQL Server pode controlar o número máximo de threads por operador usando servidor, banco de dados, grupo de recursos ou configurações de consulta chamadas de MAXDOP (grau máximo de paralelismo). Definir o MAXDOP certo para uma implantação do SQL Server pode ser um exercício complexo e, às vezes, difícil.
No SQL Server 2022, o otimizador pode usar uma técnica chamada de comentários DOP para encontrar a eficiência paralela de uma consulta. A eficiência paralela é o DOP mínimo para uma consulta que pode resultar na mesma duração geral da consulta (removendo esperas comuns da equação). A redução do DOP para uma consulta pode fornecer mais threads e recursos de CPU para outras consultas ou aplicativos.
Os comentários do DOP exigem que o Repositório de Consultas seja habilitado, o nível de compatibilidade do banco de dados 160 e uma configuração de banco de dados chamada DOP_FEEDBACK para ser ativada. Com essas configurações, o otimizador trabalhará em coordenação com as tarefa em segundo plano do Repositório de Consultas para procurar consultas repetitivas e de execução longa que possam se beneficiar de um DOP inferior. Um ciclo de comentários será usado para validar uma duração de consulta ajustada (fatorando esperas) não regredir com um valor DOP mais baixo e que uma CPU geral mais baixa seja observada para a consulta. Após um período de validação, um DOP inferior é considerado estabilizado e mantido no Repositório de Consultas. O otimizador continuará validando valores do DOP mais baixos gradualmente para encontrar a melhor eficiência paralela ou um DOP mínimo, que é 2. Os comentários do DOP nunca aumentarão o DOP e respeitarão a configuração MAXDOP para uma consulta, dependendo de quaisquer servidores, bancos de dados, administradores de recursos ou dicas de consulta que tenham sido aplicados.
Os comentários do DOP não exigem recompilação, mas a validação será examinada em qualquer nova compilação de consulta. Você pode observar valores de comentários do DOP persistentes na exibição de catálogo sys.query_store_plan_feedback. Você pode ver qual é o DOP mais recente usado para uma consulta usando a coluna last_dop do modo de exibição de Gerenciamento Dinâmico sys.dm_exec_query_stats e a exibição de catálogo sys.query_store_runtime_stats.
Para obter mais informações, confira Feedback de DOP (graus de paralelismo).