Partilhar via

Orientações do Repositório de Consultas

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores Azure SQL DatabaseAzure SQL Managed InstanceBase 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 moldar planos de consulta sem alterar o código do aplicativo.

Caution

Como o Otimizador de Consultas do SQL Server normalmente seleciona o melhor plano de execução para uma consulta, recomendamos usar apenas dicas 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 uma visão geral sobre as sugestões do Query Store.

Overview

Idealmente, o Otimizador de Consulta seleciona um plano de execução ideal para uma consulta.

Se um plano ideal não for selecionado, um desenvolvedor ou administrador de banco de dados (DBA) pode desejar 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 para vários problemas relacionados ao desempenho, elas exigem uma reescrita do texto original da consulta. Os administradores e desenvolvedores de banco de dados nem sempre podem fazer alterações diretamente no código Transact-SQL para adicionar uma dica de consulta. O Transact-SQL pode ser codificado em um aplicativo ou gerado automaticamente pelo aplicativo. Anteriormente, um desenvolvedor pode ter que confiar em guias de plano, que podem ser complexos de usar.

As dicas do Query Store resolvem este problema ao permitir que se injete uma dica de consulta em uma consulta sem modificar diretamente o texto da Transact-SQL. Para obter informações sobre quais dicas de consulta podem ser aplicadas, consulte Dicas de consulta suportadas.

Quando usar as dicas do Repositório de Consultas

Como o nome sugere, esse recurso se estende e depende do Query Store. O Repositório de Consultas permite a captura de consultas, planos de execução e estatísticas de tempo de execução associadas. O Query Store simplifica significativamente a experiência de afinação do desempenho para o cliente. O SQL Server 2016 (13.x) foi o primeiro a introduzir o Query Store, e agora está ativado por padrão no SQL Server 2022 (16.x), Azure SQL Managed Instance, Azure SQL Database e SQL Database no Microsoft Fabric.

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

Exemplos em que as dicas do Query Store podem ajudar a melhorar o desempenho ao nível da consulta:

  • Recompile uma consulta em cada execução.
  • Limite o tamanho da concessão de memória para uma operação de inserção em massa.
  • Limite o grau máximo de paralelismo ao atualizar estatísticas.
  • Use uma associação de hash em vez de uma associação de loops aninhados.
  • Use nível de compatibilidade 110 para uma consulta específica, mantendo todo o resto no banco de dados no nível de compatibilidade 150.
  • Desative a otimização de objetivos por linha para uma consulta SELECT TOP.

Para usar sugestões 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:

  2. Execute sys.sp_query_store_set_hints com a cadeia de caracteres de dica de consulta query_id que deseja aplicar à consulta. Essa cadeia de caracteres pode conter uma ou mais dicas de consulta. Para obter informações completas, consulte sys.sp_query_store_set_hints.

Uma vez criadas, as dicas do Repositório de Consultas são mantidas e sobrevivem a reinicializações e failovers. As sugestões do Repositório de Consultas substituem as sugestões codificadas ao nível da instrução e as sugestões dos guias de plano existentes.

Se uma dica de consulta contradiz o que é possível para otimização de consulta, a execução da consulta não é bloqueada e a dica 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 visualizados em sys.query_store_query_hints.

Antes de usar as dicas do Repositório de Consultas

Considere o seguinte antes de começar a usar as sugestões do Query Store.

  • Conclua a manutenção de estatísticas e de índices (se necessário) antes de avaliar consultas para possíveis novas dicas do Repositório de Consultas. A manutenção de estatísticas e, em menor grau, a manutenção do índice podem resolver o problema que, caso contrário, requer uma dica de consulta.
  • Antes de usar as 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 PSP (Parameter Sensitive Plan) foi introduzida no SQL Server 2022 (16.x) no nível de compatibilidade 160. Ele usa vários planos ativos por consulta para abordar distribuições de dados não uniformes. Se o seu ambiente não puder usar o nível de compatibilidade mais recente, as dicas do Repositório de Consultas com a dica RECOMPILE podem ser usadas em qualquer nível de compatibilidade suportado.
  • As dicas do Repositório de Consultas substituem o comportamento padrão do plano de consulta do Mecanismo de Banco de Dados. Você só deve usar as dicas do Repositório de Consultas quando for necessário resolver problemas relacionados ao desempenho.
  • Você deve reavaliar as dicas do Query Store, as dicas a nível de instruções, os guias de plano e os planos forçados do Query Store sempre que ocorrerem alterações no volume e na distribuição dos dados, bem como durante os projetos de migração de bases 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 sugestões são especificadas num formato válido de string N'OPTION (...)'.

  • Quando se cria uma pista do Repositório de Consultas, se não existir nenhuma pista do Repositório de Consultas para uma query_idespecífica, uma nova pista do Repositório de Consultas será criada.
  • Quando você cria ou atualiza uma dica do Repositório de Consultas, se já existir uma dica do Repositório de Consultas para um query_idespecífico, o último valor fornecido substitui os valores especificados anteriormente para a consulta associada.
  • Se um query_id não existir, um erro será gerado.

Para obter uma lista completa das dicas suportadas como dicas do Query Store, 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 definir ou limpar sugestões para todos os valores query_id correspondentes a um hash de consulta.

dbo.sp_query_store_modify_hints_by_query_hash é um exemplo de procedimento armazenado que chama o procedimento armazenado do sistema sys.sp_query_store_set_hints ou sys.sp_query_store_clear_hints repetidamente em um loop para realizar esta tarefa.

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 reais do Repositório de Consultas aplicadas à consulta
QueryStoreStatementHintId Identificador único de uma sugestão de consulta
QueryStoreStatementHintSource Fonte da dica do Repositório de Consultas (por exemplo, User)

Note

Esses elementos XML estão disponíveis através 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 indicações do Repositório de Consultas substituem outras indicações codificadas no nível da instrução e guias de plano.
  • Com exceção da dica ABORT_QUERY_EXECUTION, as consultas com dicas do Query Store sempre são executadas. As dicas opostas do Repositório de Consultas, que de outra forma causariam um erro, são ignoradas.
  • Se as dicas do Repositório de Consultas contradizerem, o Mecanismo de Banco de Dados não bloqueará a execução da consulta e a dica do Repositório de Consultas não será aplicada.
  • Não há suporte para pistas do Query Store para instruções que se qualificam para parametrização simples.
  • A RECOMPILE sugestão não é compatível com a parametrização obrigatória definida ao nível do banco de dados. Se um banco de dados tiver um conjunto de parametrização forçada e a dica RECOMPILE fizer parte das dicas do Repositório de Consultas para uma consulta, o Mecanismo de Banco de Dados ignorará a dica RECOMPILE e aplicará quaisquer outras dicas se elas forem especificadas.
    • O Mecanismo de Banco de Dados emite um aviso (código de erro 12461) informando que a dica RECOMPILE foi ignorada.
    • Para obter mais informações sobre considerações de caso de uso de parametrização forçada, consulte Diretrizes para usar parametrização forçada.
  • As indicações do Repositório de Consultas criadas manualmente estão excluídas da limpeza do Repositório de Consultas. A dica e a consulta não são limpas pela política de captura de retenção automática.
    • As consultas podem ser removidas manualmente pelos usuários. Isso também remove a dica associada do Query Store.
    • As sugestões do Query Store geradas automaticamente pelo Feedback de CE estão sujeitas a limpeza devido à retenção automática estipulada pela política de captura.
    • feedback de DOP e feedback de concessão de memória ajustam o comportamento da consulta sem recorrer a sugestões do Query Store. Quando as consultas são limpas pela política de captura automática de retenção, os dados de feedback de DOP e de concessão de memória também são limpos.
    • Se você criar a mesma dica do Repositório de Consultas que o feedback do CE implementou manualmente, a consulta com a dica não estará mais sujeita à limpeza pela política de captura automática de retenção.

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

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

  • No SQL Server 2022 (16.x) e 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 a Loja de Consultas para réplicas secundárias está ativada, as dicas da Loja de Consultas podem ser aplicadas em réplicas secundárias em grupos de disponibilidade. Para suporte completo à plataforma, consulte a Loja de Consultas para secundários legíveis.

Quando a Query Store é suportada em réplicas secundárias:

Examples

A. Demonstração de dicas da Loja de Consultas

O seguinte passo a passo das sugestões do Query Store no Banco de Dados SQL do Azure usa 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, consulte Guia de 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 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. Identificar uma consulta no Repositório de Consultas

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

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 em suas exibições do sistema.

Identifique a consulta nas exibições do catálogo do sistema 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 identificado, aplique a dica para impor um tamanho máximo de concessão de memória em porcentagem 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 sugestões de consulta com a seguinte sintaxe, por exemplo, a opção para forçar o estimador de cardinalidade herdado:

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írgula:

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

Revise a sugestão do Repositório de Consultas em uso para o 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;

Finalmente, remova a pista do 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