Melhores práticas para as dicas do Repositório de Consultas

Artigo
05/30/2023

Aplica-se a: SQL Server 2022 (16.x) Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Este artigo detalha as melhores práticas para usar as Dicas do Repositório de Consultas. As dicas do Repositório de Consultas permitem desenvolver formas de plano de consulta sem modificar o código do aplicativo.

Para obter mais informações configuração e administração com o Repositório de Consultas, confira Monitoramento do desempenho usando o Repositório de Consultas.
Para obter informações sobre como descobrir informações acionáveis e ajustar o desempenho com o Repositório de Consultas, confira Ajustar o desempenho com o Repositório de Consultas.

Casos de uso de dicas do Repositório de Consultas

Considere os casos de uso a seguir como ideias para usar as dicas do Repositório de Consultas. Para obter mais informações, confira Quando usar as dicas do Repositório de Consultas.

Cuidado

Como o otimizador de consulta do SQL Server seleciona, normalmente, o melhor plano de execução para uma consulta, recomendamos usar dicas apenas como último recurso para desenvolvedores e administradores de banco de dados experientes. Para saber mais, confira Dicas de consulta.

Quando o código não pode ser alterado

O uso das dicas do Repositório de Consultas permite influenciar os planos de execução de consultas sem alterar o código do aplicativo nem os objetos de banco de dados. Nenhum outro recurso permite que você aplique dicas de consulta com rapidez e facilidade.

Você pode usar as dicas do Repositório de Consultas, por exemplo, para beneficiar ETLs sem reimplantar o código. Saiba como aprimorar o carregamento em massa com as dicas do Repositório de Consultas assistindo a este vídeo de 14 minutos:

As dicas do Repositório de Consultas são métodos de ajuste de consulta leve, mas quando a consulta fica problemática, é necessário aplicar alterações de código mais significativas. Se você tem uma necessidade regular de aplicar dicas do Repositório de Consultas a uma consulta, considere reescrever uma consulta maior. Geralmente, o otimizador de consulta do SQL Server seleciona o melhor plano de execução para uma consulta. Recomendamos o uso de dicas apenas como último recurso para desenvolvedores e administradores de banco de dados experientes.

Para obter informações sobre quais dicas de consulta podem ser aplicadas, confira Dicas de consulta com suporte.

Em caso de carga de transação elevada ou código crítico

Se as alterações de código forem impraticáveis devido a requisitos elevados de tempo de atividade ou carga transacional, as dicas do Repositório de Consultas poderão aplicar dicas de consulta a cargas de trabalho de consulta existentes rapidamente. É fácil adicionar e remover dicas do Repositório de Consultas.

As dicas do Repositório de Consultas podem ser adicionadas e removidas em lotes de consultas para ajustar o desempenho em janelas cronometradas para intermitências de carga de trabalho excepcional.

Como uma substituição dos guias de plano

Antes das dicas do Repositório de Consultas, o desenvolvedor precisava contar com guias de plano para realizar tarefas semelhantes, cujo uso era complexo. As dicas do Repositório de Consultas são integradas aos recursos do Repositório de Consultas do SSMS (SQL Server Management Studio) para exploração visual de consultas.

Com os guias de plano, é necessário pesquisar todos os planos usando snippets de consulta. O recurso de dicas do Repositório de Consultas não requer consultas correspondentes exatas para afetar o plano de consulta resultante. As dicas do Repositório de Consultas podem ser aplicadas a uma query_id no conjunto de dados do Repositório de Consultas.

As dicas do Repositório de Consultas substituem as dicas no nível da instrução embutidas em código e os guias de plano existentes.

Considerar um nível de compatibilidade mais recente

As dicas do Repositório de Consultas podem ser um método excelente quando não há um nível de compatibilidade do banco de dados mais recente disponível para você devido à especificação do fornecedor ou a atrasos de teste mais extensos, por exemplo. Quando um nível de compatibilidade maior estiver disponível para um banco de dados, considere atualizar o nível de compatibilidade do banco de dados de uma consulta específica para usufruir das otimizações de desempenho e dos recursos mais recentes do SQL Server.

Por exemplo, se você tiver uma instância do SQL Server 2022 (16.x) com um banco de dados no nível de compatibilidade 140, ainda poderá usar dicas do Repositório de Consultas para executar consultas individuais no nível de compatibilidade 160. Você poderia usar a seguinte dica:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160''))';

Para obter um tutorial completo, consulte Exemplos de dicas do Repositório de Consultas.

Considerar um nível de compatibilidade mais antigo após a atualização

Outro caso em que as dicas do Repositório de Consultas podem ajudar é quando as consultas não podem ser modificadas diretamente após uma migração ou atualização de instância do SQL Server. Use as dicas do Repositório de Consultas para aplicar um nível de compatibilidade anterior a uma consulta até que ela possa ser reescrita ou endereçada de outra forma para ter um bom desempenho no nível de compatibilidade mais recente. Identifique consultas atípicas que regrediram em um nível de compatibilidade mais alto usando o relatório de consultas regressadas do Repositório de Consultas, usando a ferramenta Orientador de Otimização de Consultas durante uma migração ou outra telemetria de aplicativo no nível de consulta. Para obter mais informações sobre as diferenças entre os níveis de compatibilidade, revise as Diferenças entre níveis de compatibilidade.

Depois de testar o desempenho do novo nível de compatibilidade e implantar as dicas do Repositório de Consultas dessa maneira, você pode atualizar todo o nível de compatibilidade do banco de dados, mantendo as principais consultas problemáticas no nível de compatibilidade anterior, sem alterações de código.

Considerações de dicas do Repositório de Consultas

Considere os cenários a seguir ao implantar dicas do Repositório de Consultas.

Alterações na distribuição de dados

Os guias de plano, planos forçados por meio do Repositório de Consultas e as dicas do Repositório de Consultas substituem a tomada de decisão do otimizador. A dica do Repositório de Consultas pode ajudar agora, mas não depois. Por exemplo, se uma dica do Repositório de Consultas ajudar uma consulta na distribuição de dados anterior, ela poderá ser contraprodutiva se as operações de DML em larga escala alterarem os dados. Uma nova distribuição de dados poderá fazer com que o otimizador tome uma decisão melhor do que a dica. Esse cenário é a consequência mais comum de forçar o comportamento do plano.

Reavaliar sempre a estratégia de dicas do Repositório de Consultas

Reavalie a estratégia de dicas do Repositório de Consultas existente nos seguintes casos:

Após grandes alterações de distribuição de dados conhecidas.
Quando o SLO (objetivo de nível de serviço) do Banco de Dados SQL do Azure, da Instância Gerenciada ou da máquina virtual for alterado.
Quando a correção de planos passou a durar muito. As dicas do Repositório de Consultas são mais usadas para correções de curto prazo.
Regressões de desempenho inesperadas.

Amplo potencial de impacto

As dicas do Repositório de Consultas afetarão todas as execuções da consulta, independentemente do conjunto de parâmetros, do aplicativo de origem, do usuário ou do conjunto de resultados. No caso de regressão acidental de desempenho, as dicas do Repositório de Consultas criadas com sys.sp_query_store_set_hints podem ser facilmente removidas com sys.sp_query_store_clear_hints.

Carregue com cuidado as alterações de teste para sistemas críticos ou confidenciais antes de aplicar as dicas do Repositório de Consultas na produção.

Não há suporte para a parametrização forçada e a dica RECOMPILE

Não há suporte para a aplicação da dica de consulta RECOMPILE com as dicas do Repositório de Consultas quando a opção de banco de dados PARAMETERIZATION está definida como FORCED. Para saber mais, veja Diretrizes para uso da parametrização forçada.

A dica RECOMPILE não é compatível com a parametrização forçada definida no nível do banco de dados. Se o banco de dados tiver um conjunto de parametrização forçada e a dica RECOMPILE fizer parte da cadeia de caracteres de dicas definida no Repositório de Consultas de uma consulta, o Mecanismo de Banco de Dados vai ignorar a dica RECOMPILE e aplicar outras dicas se necessário. Além disso, a partir de julho de 2022 no Banco de Dados SQL do Azure, um aviso (código de erro 12461) deve ser emitido informando que a dica RECOMPILE foi ignorada.