Partilhar via

Orientações do Repositório de Consultas

  • Artigo

Aplica-se a: SQL Server 2022 (16.x) Banco de Dados SQL do AzureInstância Gerenciada 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 moldar 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 SQL do Azure. As Sugestões do Query Store também são um recurso introduzido no SQL Server no SQL Server 2022 (16.x).

Atenção

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.

Visão geral

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 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 conseguem fazer alterações diretamente no código Transact-SQL para injetar 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.

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) 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 SQL do Azure e no Banco de Dados SQL do Azure.

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 da meta de 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:
    • Consultando as exibições de catálogo do Query Store.
    • Usando os relatórios integrados do Repositório de Consultas do SQL Server Management Studio.
    • Usando o Insight de Desempenho de Consulta do portal do Azure para o Banco de Dados SQL do Azure.
  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 indicação de consulta contradiz o que é possível para a otimização de consultas, a execução da consulta não é bloqueada e a indicaçã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 visualizados 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 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.

Observação

Para obter uma lista completa das dicas suportadas, consulte 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 elemento StmtSimple do Plano de Execução em formato XML :

Atributo Descrição
QueryStoreStatementHintText Sugestão(ões) atual(is) do Repositório de Consultas aplicadas à consulta
QueryStoreStatementHintId Identificador único de uma sugestão de consulta
QueryStoreStatementHintSource Fonte da sugestão do Query Store (ex.: "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 indicações do Repositório de Consultas substituem outras indicações codificadas no nível da instrução e guias de plano.
  • As consultas são sempre 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 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 são suportadas 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 no Repositório de Consultas para uma consulta, o SQL Server ignorará a dica RECOMPILE e aplicará quaisquer outras dicas se elas forem aplicadas.
    • Além disso, o SQL Server emite um aviso (código de erro 12461) informando que a dica RECOMPILE foi ignorada.
    • Para obter mais informações sobre considerações de casos de uso de parametrização forçada, consulte Diretrizes para usar parametrização forçada.
  • As sugestões do Repositório de Consultas criadas manualmente estão isentas de eliminação. 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 associada do Repositório de Consultas.
    • 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 retenção automática da política de captura, os dados de feedback de DOP e de concessão de memória também são limpos.
    • Você pode criar manualmente a mesma dica do Query Store que o feedback do CE implementou e, em seguida, a consulta com a dica não estará mais sujeita à limpeza pela retenção automática da política de captura.

Sugestões do Query Store e grupos de disponibilidade

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 Query Store para réplicas secundárias.

  • Antes do SQL Server 2022 (16.x), as dicas do Query Store 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 reconhecem réplicas secundárias em grupos de disponibilidade.
  • Você pode adicionar uma dica do Query Store a uma réplica específica ou a um conjunto de réplicas quando tiver o Query Store para réplicas secundárias habilitado. No 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.

Práticas recomendadas de dicas do Repositório de Consultas

  • Conclua a manutenção de índices e estatísticas antes de avaliar consultas para possíveis novas dicas do Repositório de Consultas.
  • Teste o seu banco de dados de aplicações no nível de compatibilidade mais recente antes de usar as dicas do Query Store.
    • Por exemplo, a otimização PSP (Parameter Sensitive Plan) foi introduzida no SQL Server 2022 (16.x) (nível de compatibilidade 160), que 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, poderão ser usadas dicas do Repositório de Consultas, como a dica RECOMPILE, em qualquer nível de compatibilidade suportado.
  • As dicas do Repositório de Consultas substituem o comportamento do plano de consulta do SQL Server. Recomenda-se usar apenas dicas do Repositório de Consultas quando for necessário resolver problemas relacionados ao desempenho.
  • É recomendável reavaliar as dicas do Repositório de Consultas, as dicas de nível de instrução, os guias de plano e os planos forçados do Repositório de Consultas sempre que as distribuições de dados forem alteradas e durante os projetos de migração 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

Um. Demonstração de dicas da Loja 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, consulte Guia de 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. 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