Compreender as dicas de consulta

Concluído

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

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 poderá 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 cada vez que é 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 fosse aplicável.
• Você não pode se beneficiar de recursos novos e aprimorados em versões subsequentes do SQL Server se vincular 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 finalidades diferentes. Vamos discutir alguns deles abaixo:

• FAST <integer_value>—recupera o primeiro <integer_value> número de linhas enquanto continua a execução da consulta. Ele funciona melhor com pequenos conjuntos de dados e baixo valor para dica de consulta rápida. À medida que a contagem de linhas é aumentada, o custo da consulta torna-se maior.

• OPTIMIZE FOR—fornece instruções ao otimizador de consulta de que um determinado valor 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 descarta-o 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 JOINou HASH JOIN em toda a consulta. O otimizador escolhe a estratégia de junção mais barata entre 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 Administrador de Recursos.

Você também pode aplicar várias dicas de consulta na mesma consulta. O exemplo a seguir usa as HASH GROUP dicas de consulta 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, consulte Dicas (Transact-SQL).

Dicas do Repositório de Consultas (na visualização)

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 pode 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 das exibições de catálogo do Repositório de Consultas, relatórios internos do Repositório de Consultas ou do Insight de Desempenho de Consulta para o Banco de Dados SQL do Azure. Em seguida, execute sp_query_store_set_hints com a cadeia de caracteres de dica de query_id e consulta que deseja aplicar à consulta.

O exemplo abaixo mostra como obter o query_id para uma consulta específica e, em seguida, usá-lo para aplicar as RECOMPILE dicas 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

Há alguns cenários em que as dicas do Repositório de Consultas podem ajudar com problemas de desempenho no nível da consulta.

• Recompile uma consulta em cada execução.
• Limite o grau máximo de paralelismo para uma operação de atualização estatística.
• Use uma associação de hash em vez de uma associação de loops aninhados.
• Use o nível de compatibilidade 110 para uma consulta específica, mantendo o banco de dados na compatibilidade atual.

Nota

As dicas do Repositório de Consultas também são suportadas pela Instância Gerenciada do SQL.

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