Compartilhar via

Dicas do Repositório de Consultas

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores Banco de dados SQL do AzureInstância Gerenciada de SQL do AzureBanco de dados SQL no Microsoft Fabric

Este artigo descreve como aplicar dicas de consulta usando o 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.

Caution

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 obter mais informações, consulte Dicas de consulta.

Assista a este vídeo para ter uma visão geral das dicas do Repositório de Consultas:

Overview

O desejável é que o Otimizador de Consulta selecione um plano de execução ideal para uma consulta.

Se um plano ideal não estiver selecionado, um DBA (desenvolvedor ou administrador de banco de dados) poderá querer otimizar manualmente para condições específicas. As dicas de consulta são especificadas por meio da cláusula OPTION e podem ser usadas para afetar o comportamento de execução da consulta. Embora as dicas de consulta ajudem a fornecer soluções localizadas a vários problemas relacionados ao desempenho, elas exigem uma reescrita do texto da consulta original. Os administradores e desenvolvedores de banco de dados podem nem sempre ser capazes de fazer alterações diretamente no código Transact-SQL para adicionar uma dica de consulta. O Transact-SQL pode ser embutido em código em um aplicativo ou gerado automaticamente pelo aplicativo. De maneira prévia, um desenvolvedor pode precisar contar com guias de plano, que podem ser complexos de usar.

As dicas do Repositório de Consultas resolvem esse problema, permitindo que você insira uma dica de consulta sem modificar diretamente o texto Transact-SQL da consulta. Para obter informações sobre quais dicas de consulta podem ser aplicadas, confira Dicas de consulta com suporte.

Quando usar dicas de Repositório de Consultas

Como o nome sugere, esse recurso se estende e depende do Repositório de Consultas. O Repositório de Consultas habilita a captura de consultas, planos de execução e estatísticas de runtime associadas. O Repositório de Consultas simplifica bastante a experiência geral do cliente com o ajuste de desempenho. O SQL Server 2016 (13.x) introduziu pela primeira vez o Repositório de Consultas e agora está habilitado por padrão no SQL Server 2022 (16.x), na Instância Gerenciada de SQL do Azure, no Banco de Dados SQL do Azure e no Banco de Dados SQL no Microsoft Fabric.

O fluxo de trabalho para as Dicas do Repositório de Consultas.

Exemplos em que as dicas do Repositório de Consultas podem ajudar com problemas de desempenho no nível da consulta:

  • Recompilar uma consulta em cada execução.
  • Limitar o tamanho de concessão de memória para uma operação BULK INSERT.
  • Limitar o grau máximo de paralelismo ao atualizar estatísticas.
  • Use uma junção Hash em vez de uma junção de Loops Aninhados.
  • Usar o nível de compatibilidade 110 para uma consulta específica, mantendo todo o restante no banco de dados no nível de compatibilidade 150.
  • Desabilitar a otimização de meta de linha para uma consulta SELECT TOP.

Para usar as dicas do Repositório de Consultas:

  1. Identifique o Repositório de Consultas query_id da instrução de consulta que você deseja modificar. Você pode fazer isso de várias maneiras:

    • Consultando as visões do catálogo do Query Store (Transact-SQL).
    • Usando os relatórios Repositório de Consultas embutidos no SQL Server Management Studio.
    • Usando Análise de Desempenho de Consultas do portal do Azure para o Banco de Dados SQL do Azure.

  2. Execute sys.sp_query_store_set_hints com query_id e a cadeia de caracteres de dica de consulta que você quer aplicar à consulta. Essa cadeia de caracteres pode conter uma ou mais dicas de consulta. Para saber mais, confira sys.sp_query_store_set_hints.

Depois de criadas, as dicas do Repositório de Consultas são mantidas e sobrevivem a reinicializações e failovers. As dicas do Repositório de Consultas substituem as dicas de nível de instrução embutidas em código e as dicas de guia de plano existentes.

Se uma sugestão de consulta contradiz o que é possível para otimização de consulta, a execução da consulta não é bloqueada e a sugestão não é aplicada. Nos casos em que uma dica faria com que uma consulta falhasse, a dica é ignorada e os detalhes mais recentes da falha podem ser exibidos em sys.query_store_query_hints.

Antes de usar dicas do Query Store

Considere o seguinte antes de começar a usar dicas do Repositório de Consultas.

  • Conclua a manutenção de estatísticas e a manutenção do índice (se necessário) antes de avaliar as consultas para possíveis novas dicas do Repositório de Consultas. A manutenção de estatísticas e, em menor escala, a manutenção do índice podem resolver o problema que, caso contrário, exigiria uma indicação de consulta.
  • Antes de usar dicas do Repositório de Consultas, teste o banco de dados do aplicativo no nível de compatibilidade mais recente para ver se isso resolve o problema que requer uma dica de consulta.
    • Por exemplo, a otimização do PSP (Plano Sensível a Parâmetros) foi introduzida no SQL Server 2022 (16.x) no nível de compatibilidade 160. Ele utiliza vários planos ativos por consulta para lidar com distribuições de dados não uniformes. Se o ambiente não puder usar o nível de compatibilidade mais recente, as dicas do Repositório de Consultas usando a dica RECOMPILE poderão ser usadas em qualquer nível de compatibilidade de suporte.
  • As sugestões do Query Store substituem o comportamento padrão do plano de consulta do Mecanismo de Banco de Dados. Você só deve usar dicas do Repositório de Consultas quando for necessário resolver problemas relacionados ao desempenho.
  • Você deve reavaliar as dicas do Repositório de Consultas, as dicas no nível de instrução, os guias de plano e os planos forçados do Repositório de Consultas sempre que o volume e a distribuição de dados forem alterados e durante projetos de migração de banco de dados. Alterações no volume e na distribuição de dados podem fazer com que as dicas do Repositório de Consultas gerem planos de execução abaixo do ideal.

Procedimentos armazenados do sistema de dicas do Repositório de Consultas

Para criar ou atualizar dicas, use sys.sp_query_store_set_hints. As dicas são especificadas em um formato de sequência de caracteres válido N'OPTION (...)'.

  • Ao criar uma dica do Repositório de Consultas, se não houver uma dica do Repositório de Consultas para um query_id específico, uma nova dica será criada.
  • Ao criar ou atualizar uma dica do Repositório de Consultas, se uma dica do Repositório de Consultas já existir para um query_id específico, o último valor fornecido substituirá os valores especificados anteriormente para a consulta associada.
  • Se um query_id não existir, um erro será gerado.

Para ver uma lista completa de dicas que têm suporte como dica do Repositório de Consultas, consulte sys.sp_query_store_set_hints.

Para remover dicas associadas a um query_id, use sys.sp_query_store_clear_hints.

Tip

Talvez seja necessário configurar ou remover indicações para todos os valores de query_id que correspondem a um hash de consulta.

dbo.sp_query_store_modify_hints_by_query_hash é um procedimento armazenado de exemplo que chama o procedimento armazenado do sistema sys.sp_query_store_set_hints ou sys.sp_query_store_clear_hints em um loop para fazer isso.

Atributos XML do plano de execução

Quando as dicas são aplicadas, o seguinte conjunto de resultados aparece no StmtSimple elemento do plano de execução no formato XML:

Attribute Description
QueryStoreStatementHintText Dicas do Repositório de Consultas real aplicados à consulta
QueryStoreStatementHintId Identificador exclusivo de uma dica de consulta
QueryStoreStatementHintSource Origem da dica do Repositório de Consultas (por exemplo, User)

Note

Esses elementos XML estão disponíveis por meio da saída dos comandos Transact-SQL SET STATISTICS XML e SET SHOWPLAN_XML.

Dicas do Repositório de Consultas e interoperabilidade de recursos

  • As dicas do Repositório de Consultas substituem outras dicas de nível de instrução embutidas em código e guias de plano.
  • Exceto pela ABORT_QUERY_EXECUTION indicação, as consultas com sugestões do Query Store sempre são executadas. As dicas conflitantes do Repositório de Consultas que de outra forma causariam um erro são ignoradas.
  • Se as dicas do Repositório de Consultas contradizem, o Mecanismo de Banco de Dados não bloqueia a execução da consulta e a dica do Repositório de Consultas não é aplicada.
  • As dicas do Repositório de COnsultas não são compatíveis para instruções que se qualificam para parametrização simples.
  • A dica RECOMPILE não é compatível com a parametrização forçada definida no nível do banco de dados. Se um banco de dados tiver um conjunto de parametrização forçada e a RECOMPILE dica fizer parte das dicas do Repositório de Consultas para uma consulta, o Mecanismo de Banco de Dados ignorará a RECOMPILE dica e aplicará outras dicas se elas forem especificadas.
    • O Mecanismo de Banco de Dados emite um aviso (código de erro 12461) informando que a RECOMPILE dica foi ignorada.
    • Para obter mais informações sobre considerações de caso de uso de parametrização forçada, consulte Diretrizes para usar a parametrização forçada.
  • As dicas do Repositório de Consultas criadas manualmente são isentas da limpeza do Repositório de Consultas. A dica e a consulta não são removidas pela política de captura automática de retenção de dados.
    • As consultas podem ser removidas manualmente pelos usuários. Isso também remove a indicação associada do Query Store.
    • As dicas do Repositório de Consultas geradas automaticamente pelo Feedback do CE estão sujeitas à limpeza pela retenção automática da política de captura.
    • O Feedback do DOP e o Feedback da concessão de memória moldam o comportamento da consulta sem usar dicas do Repositório de Consultas. Quando as consultas são limpas pela retenção automática da política de captura, os dados de feedback do DOP e da concessão de memória também são limpos.
    • Se você criar a mesma dica do Repositório de Consultas e que o feedback do CE implementou manualmente, a consulta com a dica não estará mais sujeita à limpeza pela política de captura de retenção automática.

Dicas do Repositório de Consultas e réplicas secundárias

As dicas do Repositório de Consultas não têm efeito sobre as réplicas secundárias, a menos que o Repositório de Consultas para réplicas secundárias esteja habilitado. Para obter mais informações, consulte Repositório de Consultas para secundários legíveis.

  • No SQL Server 2022 (16.x) e em versões anteriores, as dicas do Repositório de Consultas podem ser aplicadas somente na réplica primária.
  • No SQL Server 2025 (17.x) e versões posteriores, quando o Repositório de Consultas para réplicas secundárias está habilitado, as dicas do Repositório de Consultas podem ser aplicadas em réplicas secundárias em grupos de disponibilidade. Para obter suporte completo à plataforma, consulte Repositório de Consultas para secundários legíveis.

Onde o Repositório de Consultas tem suporte em réplicas secundárias:

Examples

A. Demonstração das dicas do Repositório de Consultas

O passo a passo a seguir sobre as dicas do Query Store no Banco de Dados SQL do Azure utiliza um banco de dados importado por meio de um arquivo BACPAC (.bacpac). Saiba como importar um novo banco de dados para um servidor do Banco de Dados SQL do Azure, confira Início Rápido: Importar um arquivo bacpac para um banco de dados no Banco de Dados SQL do Azure ou na Instância Gerenciada de SQL do Azure.

-- ************************************************************************ --
-- Query Store hints demo

-- Demo uses "PropertyMLS" database which can be imported from BACPAC here:
-- https://github.com/microsoft/sql-server-samples/tree/master/samples/features/query-store

-- Email QSHintsFeedback@microsoft.com for questions\feedback
-- ************************************************************************ --

/*
    Demo prep, connect to the PropertyMLS database
*/

ALTER DATABASE [PropertyMLS] SET QUERY_STORE CLEAR;
ALTER DATABASE CURRENT SET QUERY_STORE = ON;
ALTER DATABASE CURRENT SET QUERY_STORE  (QUERY_CAPTURE_MODE = ALL);
GO

-- Should be READ_WRITE
SELECT actual_state_desc
FROM sys.database_query_store_options;
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints.
    Checking if any already exist (should be none).
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
     The PropertySearchByAgent stored procedure has a parameter
     used to filter AgentId.  Looking at the statistics for AgentId,
     you will see that there is a big skew for AgentId 101.
*/
SELECT	hist.range_high_key AS [AgentId],
        hist.equal_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE s.[name] = N'NCI_Property_AgentId';


-- Show actual query execution plan to see plan compiled.
-- Agent with many properties will have a scan with parallelism.
EXEC [dbo].[PropertySearchByAgent] 101;

-- Agents with few properties still re-use this plan (assuming no recent plan eviction).
EXEC [dbo].[PropertySearchByAgent] 4;


/*
    Now let's find the query_id associated with this query.
*/
SELECT query_sql_text, q.query_id
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
    qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%' and query_sql_text not like N'%query_store%';
GO

/*
     We can set the hint associated with the query_id returned in the previous result set, as below.
     Note, we can designate one or more query hints
*/
EXEC sp_query_store_set_hints @query_id=5, @value = N'OPTION(RECOMPILE)';
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see two different plans, one for AgentId 101 and one for AgentId 4.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
    We can remove the hint using sp_query_store_clear_query_hints
*/
EXEC sp_query_store_clear_hints @query_id = 10;
GO

/*
    That Query Store Hint is now removed
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see one plan again.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

B. Identifique uma consulta no Repositório de Consultas

O exemplo a seguir consulta sys.query_store_query_text e sys.query_store_query para retornar o query_id de um fragmento de texto de consulta executada.

Nesta demonstração, a consulta que estamos tentando ajustar está no banco de dados de exemplo SalesLT:

SELECT * FROM SalesLT.Address as A
INNER JOIN SalesLT.CustomerAddress as CA
on A.AddressID = CA.AddressID
WHERE PostalCode = '98052' ORDER BY A.ModifiedDate DESC;

O Repositório de Consultas não reflete imediatamente os dados de consulta para suas exibições do sistema.

Identifique a consulta nas exibições do catálogo do sistema do Repositório de Consultas:

SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
    qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%PostalCode =%'
  AND query_sql_text not like N'%query_store%';
GO

Nos exemplos a seguir, o exemplo de consulta anterior no banco de dados SalesLT foi identificado como query_id 39.

Uma vez identificada, aplique a dica para impor um tamanho máximo de concessão de memória em percentual do limite de memória configurado para o query_id:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(MAX_GRANT_PERCENT=10)';

Você também pode aplicar dicas de consulta com a seguinte sintaxe, por exemplo, a opção para forçar o avaliador de cardinalidade herdada:

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

Você pode aplicar várias dicas de consulta com uma lista separada por vírgulas:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';

Examine a dica do Repositório de Consultas em vigor para query_id 39:

SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc
FROM sys.query_store_query_hints
WHERE query_id = 39;

Por fim, remova a dica de query_id 39, usando sp_query_store_clear_hints.

EXEC sys.sp_query_store_clear_hints @query_id = 39;

Conteúdo relacionado

  • Last updated on