Dicas do Repositório de Consultas

  • Artigo

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

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.

As dicas do Repositório de Consultas estão disponíveis no Banco de Dados SQL do Azure e na Instância Gerenciada de SQL do Azure. As dicas do Repositório de Consultas também são um recurso introduzido ao SQL Server no SQL Server 2022 (16.x).

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.

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

Visão geral

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

Se um plano ideal não for selecionado, um desenvolvedor ou DBA talvez queira 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 de banco de dados e desenvolvedores nem sempre podem fazer alterações diretamente no código Transact-SQL para injetar 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.

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, que agora está habilitado por padrão no SQL Server 2022 (16.x), na Instância Gerenciada de SQL do Azure e no Banco de Dados SQL do Azure.

O fluxo de trabalho de 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.
  • Usar 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:
  2. Execute sys.sp_query_store_set_hints com query_id e a cadeia de caracteres de dica de consulta que você deseja 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 dica de consulta contrariar o que é possível para a otimização de consulta, a execução da consulta não será bloqueada e a dica não será aplicada. Nos casos em que uma dica faria uma consulta falhar, a dica é ignorada e os detalhes mais recentes da falha podem ser vistos em sys.query_store_query_hints.

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 existir nenhuma dica do Repositório de Consultas para um query_id específico, uma dica do Repositório de Consultas 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.

Observação

Para ver uma lista completa de dicas com suporte, confira sys.sp_query_store_set_hints.

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

Atributos XML do plano de execução

Quando as dicas são aplicadas, o seguinte conjunto de resultados é exibido no elemento StmtSimple do Plano de Execução no formato XML:

Atributo Descrição
QueryStoreStatementHintText Dica(s) do Repositório de Consultas real aplicados à consulta
QueryStoreStatementHintId Identificador exclusivo de uma dica de consulta
QueryStoreStatementHintSource Fonte da dica do Repositório de Consultas (por exemplo: "Usuário")

Observação

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.
  • As consultas sempre são executadas. As dicas divergentes do Repositório de Consultas, que de outra forma causariam um erro, serão ignoradas.
  • Se as dicas do Repositório de Consultas forem contraditórias, o SQL Server não bloqueará a execução da consulta e a dica do Repositório de Consultas não será aplicada.
  • Parametrização simples – as dicas do Repositório de Consultas não têm suporte para instruções que se qualificam para parametrização simples.
  • 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 sequência de caracteres de dicas definida no Repositório de Consultas para uma consulta, o SQL Server ignorará a dica RECOMPILE e aplicará outras dicas se elas forem aplicadas.
    • Além disso, o SQL Server emitirá 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 a parametrização forçada.
  • As dicas do Repositório de Consultas criadas manualmente estão isentas de limpeza. A dica e a consulta não serão limpas do Repositório de Consultas pela retenção automática da política de captura.
    • As consultas podem ser removidas manualmente pelos usuários, o que também removeria a dica do Repositório de Consultas associada.
    • As dicas do Repositório de Consultas geradas automaticamente pelo Feedback de CE estão sujeitas à limpeza pela retenção automática da política de captura.
    • O Feedback de DOP e o feedback de concessão de memória formam 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 de concessão de memória também são limpos.
    • Você pode criar manualmente a mesma dica do Repositório de Consultas que o feedback de CE implementou. Assim, a consulta com a dica não estará mais sujeita à limpeza pela retenção automática da política de captura.

Dicas do Repositório de Consultas e grupos de disponibilidade

Para obter mais informações, consulte Repositório de Consultas para réplicas secundárias.

  • Nas versões anteriores do SQL Server 2022 (16.x), as dicas do Repositório de Consultas podem ser aplicadas à réplica primária de um grupo de disponibilidade.
  • A partir do SQL Server 2022 (16.x), quando o Repositório de Consultas para réplicas secundárias está habilitado, as dicas do Repositório de Consultas também têm reconhecimento para réplicas secundárias em grupos de disponibilidade.
  • Você pode adicionar uma dica do Repositório de Consultas a uma réplica ou conjunto de réplicas específico quando tiver o Repositório de Consultas para réplicas secundárias habilitado. Em sys.sp_query_store_set_query_hints, isso é definido pelo parâmetro @query_hint_scope, que foi introduzido no SQL Server 2022 (16.x).
  • Encontre os conjuntos de réplicas disponíveis consultando sys.query_store_replicas.
  • Encontre planos forçados em réplicas secundárias com sys.query_store_plan_forcing_locations.

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

  • Conclua a manutenção de índice e estatísticas antes de avaliar consultas para possíveis novas dicas de Repositório de Consultas.
  • Teste o banco de dados de aplicativo no nível de compatibilidade mais recente antes de usar as dicas do Repositório de Consultas.
    • Por exemplo, a otimização de PSP (Plano Sensível a Parâmetros) foi introduzida no SQL Server 2022 (16.x) (nível de compatibilidade 160), que usa vários planejamentos de atividade 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 dicas do Repositório de Consultas substituem o comportamento do plano de consulta do Microsoft SQL Server. É recomendável usar apenas dicas do Repositório de Consultas quando elas forem necessárias para resolver problemas relacionados ao desempenho.
  • É recomendável reavaliar as dicas do Repositório de Consultas, dicas de nível de instrução, guias de plano e planos forçados do Repositório de Consultas sempre que as distribuições de dados forem alteradas e durante projetos de migrações de banco de dados. Alterações na distribuição de dados podem fazer com que as dicas do Repositório de Consultas gerem planos de execução abaixo do ideal.

Exemplos

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

O passo a passo a seguir das dicas do Repositório de Consultas 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, confira Início Rápido: Importar um arquivo BACPAC para um banco de dados.

-- ************************************************************************ --
-- 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_tex 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''))';

Análise 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