Entender dicas de consulta

• 5 minutos

As dicas de consulta são opções ou estratégias que podem ser aplicadas para forçar o processador de consulta a usar um operador específico no plano de execução para as instruções SELECT, INSERT, UPDATE ou DELETE. As dicas de consulta substituem qualquer plano de execução que o processador de consulta possa selecionar para uma determinada consulta com a cláusula OPTION.

Na maioria dos casos, o otimizador de consulta seleciona um plano de execução eficiente com base nos índices, estatísticas e distribuição de dados. Os administradores de banco de dados raramente precisam intervir manualmente.

Você pode alterar o plano de execução da consulta adicionando dicas de consulta ao final da consulta. Por exemplo, se você adicionar OPTION (MAXDOP <integer_value>) ao final de uma consulta que usa uma única CPU, a consulta pode usar várias CPUs (paralelismo) dependendo do valor escolhido. Ou você pode usar OPTION (RECOMPILE) para garantir que a consulta gere um novo plano temporário toda vez que for executada.

--With maxdop hint
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM Sales.SalesOrderDetail  
WHERE UnitPrice < $5.00  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (MAXDOP 2)
GO

--With recompile hint
SELECT City
FROM Person.Address
WHERE StateProvinceID=15 OPTION (RECOMPILE)
GO

Embora as dicas de consulta possam fornecer uma solução localizada para vários problemas relacionados ao desempenho, você deve evitar usá-las no ambiente de produção pelos seguintes motivos.

• Ter uma dica de consulta permanente em sua consulta pode resultar em alterações estruturais do banco de dados que seriam benéficas para que essa consulta não seja aplicável.
• Você não poderá se beneficiar de recursos novos e aprimorados em versões subsequentes do SQL Server se associar uma consulta a um plano de execução específico.

No entanto, há várias dicas de consulta disponíveis no SQL Server, que são usadas para diferentes propósitos. Vamos discutir alguns deles abaixo:

• FAST <integer_value>– recupera o primeiro número de linhas <integer_value> enquanto continua a execução da consulta. Funciona melhor com conjuntos de dados pequenos e baixo valor para dica de consulta rápida. À medida que a contagem de linhas aumenta, o custo da consulta aumenta.

• OPTIMIZE FOR– fornece instruções para o otimizador de consulta de que um valor específico para uma variável local deve ser usado quando uma consulta é compilada e otimizada.

• USE PLAN– o otimizador de consulta usará um plano de consulta especificado pelo atributo xml_plan.

• RECOMPILE– cria um novo plano temporário para a consulta e o descarta imediatamente após a execução da consulta.

• { LOOP | MERGE | HASH } JOIN– especifica que todas as operações de junção são executadas por LOOP JOIN, MERGE JOIN ou HASH JOIN em toda a consulta. O otimizador escolhe a estratégia de junção mais barata dentre as opções se você especificar mais de uma dica de junção.

• MAXDOP <integer_value>– substitui o grau máximo de valor de paralelismo de sp_configure. A consulta que especifica essa opção também substitui o Resource Governor.

Você também pode aplicar várias dicas de consulta na mesma consulta. O seguinte exemplo usa as dicas de consulta HASH GROUP e FAST <integer_value> na mesma consulta.

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM Sales.SalesOrderDetail  
WHERE UnitPrice < $5.00  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (HASH GROUP, FAST 10);  
GO    

Para saber mais sobre dicas de consulta, confira Dicas (Transact-SQL).

Dicas do Repositório de Consultas (em versão prévia)

O recurso de dicas do Repositório de Consultas no Banco de Dados SQL do Azure fornece um método simples para moldar planos de consulta sem modificar o código do aplicativo.

As dicas do Repositório de Consultas são úteis quando o otimizador de consulta não gera um plano de execução eficiente e quando o desenvolvedor ou o DBA não podem modificar o texto da consulta original. Em alguns aplicativos, o texto da consulta pode ser codificado ou gerado automaticamente.

Para usar as dicas do Repositório de Consultas, você precisa identificar o query_id do Repositório de Consultas da instrução de consulta que deseja modificar por meio de exibições de catálogo do Repositório de Consultas, relatórios internos do Repositório de Consultas ou Análise de Desempenho de Consultas para o Banco de Dados SQL do Azure. Em seguida, execute o sp_query_store_set_hints com o query_id e a cadeia de caracteres de dica de consulta que você deseja aplicar à consulta.

O exemplo abaixo mostra como obter o query_id para uma consulta específica e usá-lo para aplicar as dicas RECOMPILE e MAXDOP à consulta.

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'%ORDER BY CustomerName DESC%'  
  AND query_sql_text not like N'%query_store%'
GO

--Assuming the query_id returned by the previous query is 42
EXEC sys.sp_query_store_set_hints @query_id= 42, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1)'
GO

Existem alguns cenários 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 grau máximo de paralelismo para uma operação de atualização de estatísticas.
• Usar uma junção Hash em vez de uma junção de Loops Aninhados.
• Use o nível de compatibilidade 110 para uma consulta específica, mantendo o banco de dados na compatibilidade atual.

Para obter mais informações sobre dicas de Repositório de Consultas, confira Dicas do Repositório de Consultas.