Dicas do Repositório de Consultas

  • Artigo

Aplica-se a: SQL Server 2022 (16.x) Banco de Dados SQL do AzureInstâ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. Repositório de Consultas dicas também são um recurso introduzido para 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, consulte Dicas de consulta.

Visão geral

O desejável é que o Otimizador de Consulta selecione um plano de execução ideal para uma consulta. Quando isso não acontece, um desenvolvedor ou DBA pode 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 desenvolvedores e administradores de banco de dados nem sempre podem fazer alterações diretamente no código do 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. Previamente, 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, consulte 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. Introduzido no SQL Server 2016 (13.x) e ativado por padrão no Banco de Dados SQL do Azure, o Repositório de Consultas simplifica bastante a experiência geral do cliente com o ajuste de desempenho.

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.
  • Limite 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: 1.1. Consultando as exibições do catálogo do Repositório de Consultas. 1.2. Usando os relatórios Repositório de Consultas embutidos no SQL Server Management Studio. 1.3. Usando Análise de Desempenho de Consultas do portal do Azure para 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ê 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 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 uma consulta falhar, a dica é ignorada e os detalhes mais recentes da falha podem ser vistos em sys.query_store_query_hints.

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

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 cadeia 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 de Repositório de Consultas, se já existir uma dica de Repositório de Consultas para um específicoquery_id, 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 aparece no StmtSimple elemento 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 vão substituir outras dicas de nível de instrução embutidas em código e as dicas de guia de plano.
  • As consultas sempre serão executadas e serão opostas Repositório de Consultas dicas são ignoradas, o que, de outra forma, causaria um erro.
  • Se Repositório de Consultas dicas contradizem, SQL Server não bloqueia a execução da consulta e Repositório de Consultas dica não é 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 cadeia de caracteres de dicas definida em Repositório de Consultas para uma consulta, SQL Server ignorará a dica RECOMPILE e aplicará outras dicas se elas forem aplicadas.
    • Além disso, 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.
  • Dicas de Repositório de Consultas criadas manualmente estão isentas da limpeza. A dica e a consulta não serão limpas de 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 de Repositório de Consultas associada.
    • Repositório de Consultas dicas geradas automaticamente pelos Comentários da CE estão sujeitas a limpo pela retenção automática da política de captura.
    • Comentários do DOP e comportamento de consulta de forma de comentários de concessão de memória sem usar dicas de Repositório de Consultas. Quando as consultas são limpas pela retenção automática da política de captura, os comentários do DOP e os dados de comentários de concessão de memória também são limpos.
    • Você pode criar manualmente o mesmo Repositório de Consultas dica de que os comentários da CE foram implementados e, em seguida, a consulta com a dica não estaria mais sujeita a limpo pela retenção automática da política de captura.

Repositório de Consultas dicas e grupos de disponibilidade

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

  • Antes de SQL Server 2022 (16.x), Repositório de Consultas dicas podem ser aplicadas no réplica primário de um grupo de disponibilidade.
  • A partir do SQL Server 2022 (16.x), quando Repositório de Consultas para réplicas secundárias está habilitada, Repositório de Consultas dicas também têm reconhecimento de réplica para réplicas secundárias em grupos de disponibilidade.
  • Você pode adicionar uma dica de Repositório de Consultas a uma réplica específica ou réplica definida quando tiver Repositório de Consultas para réplicas secundárias habilitadas. Em sys.sp_query_store_set_query_hints, isso é definido pelo @query_hint_scope parâmetro , que foi introduzido no SQL Server 2022 (16.x).
  • Localize os conjuntos de réplica 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 do aplicativo no nível de compatibilidade mais recente antes de usar Repositório de Consultas dicas.
    • Por exemplo, a otimização do PSP (Plano Sensível a Parâmetros) foi introduzida no SQL Server 2022 (16.x) (nível de compatibilidade 160), que aproveita 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 aproveitadas 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 aproveitar 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

a. 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;

Repositório de Consultas não reflete imediatamente os dados de consulta para as 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;

Confira também

Próximas etapas