Localizando e ajustando consultas semelhantes usando consulta e hashes de plano de consulta
Quando você procura consultas que utilizam muitos recursos, deve levar em consideração como localizar e ajustar consultas semelhantes que juntas consomem uma quantidade considerável de recursos do sistema. As exibições de gerenciamento dinâmico sys.dm_exec_query_stats e sys.dm_exec_requests fornecem valores de hash de consulta e de hash de plano de consulta, que você pode usar para determinar o uso agregado de recursos por consultas semelhantes e planos de execução de consultas semelhantes.
Este tópico define o hash de consulta e o hash de plano de consulta, dá exemplos de como usar os valores de hash para encontrar o custo acumulado de consultas e planos de execução semelhantes e mostra formas de melhorar o desempenho de consultas e planos de execução semelhantes.
Noções básicas sobre hash de consulta e de plano de consulta
O hash de consulta é um valor de hash binário calculado na consulta e usado para identificar consultas com lógica semelhante. O otimizador de consulta calcula o hash de consulta durante a compilação de uma consulta. As consultas que diferem apenas em termos de valores literais têm o mesmo hash de consulta. Por exemplo, as duas consultas a seguir têm o mesmo hash de consulta porque são diferentes apenas quanto aos valores literais atribuídos a FirstName e a LastName.
USE AdventureWorks;
GO
SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A ON A.AddressID = CA.AddressID
WHERE C.FirstName = 'Amanda' AND C.LastName = 'Allen';
GO
SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A ON A.AddressID = CA.AddressID
WHERE C.FirstName = 'Logan' AND C.LastName = 'Jones';
GO
--Show the query hash for both queries.
SELECT st.text AS "Query Text", qs.query_hash AS "Query Hash"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st
WHERE st.text = 'SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A ON A.AddressID = CA.AddressID
WHERE C.FirstName = ''Amanda'' AND C.LastName = ''Allen'';
' OR st.text = 'SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A ON A.AddressID = CA.AddressID
WHERE C.FirstName = ''Logan'' AND C.LastName = ''Jones'';
';
GO
As duas consultas a seguir têm hashes de consulta diferentes porque suas diferenças são lógicas (AND versus OR) e não se limitam a literais.
USE AdventureWorks;
GO
SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A ON A.AddressID = CA.AddressID
WHERE C.FirstName = 'Amanda' AND C.LastName = 'Allen';
GO
SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A ON A.AddressID = CA.AddressID
WHERE C.FirstName = 'Logan' OR C.LastName = 'Jones';
GO
--Show the query hash for both queries.
SELECT st.text AS "Query Text", qs.query_hash AS "Query Hash"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st
WHERE st.text = 'SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A ON A.AddressID = CA.AddressID
WHERE C.FirstName = ''Amanda'' AND C.LastName = ''Allen'';
' OR st.text = 'SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
JOIN Person.Address AS A ON A.AddressID = CA.AddressID
WHERE C.FirstName = ''Logan'' OR C.LastName = ''Jones'';
';
GO
O hash do plano de consulta é um valor de hash binário calculado no plano de execução de consulta e usado para identificar planos de execução de consulta semelhantes. O otimizador de consulta calcula o hash de plano de consulta durante a compilação da consulta usando valores de plano de consulta, como os operadores lógicos e físicos e um subconjunto de atributos de operador importantes. Os planos de execução de consulta que têm a mesma estrutura de árvore de operadores lógicos e físicos, bem como valores de atributo idênticos para o subconjunto de atributos de operador importantes, terão o mesmo hash de plano de consulta.
Quando consultas com hashes de consulta idênticos são executadas em dados diferentes, as diferenças na cardinalidade dos resultados da consulta podem fazer com que o otimizador de consulta escolha planos de execução de consulta diferentes, o que gera hashes de plano de consulta diferentes.
O exemplo a seguir mostra como duas consultas semelhantes podem ter o mesmo hash de consulta, mas podem não ter o mesmo plano de execução de consulta. Há duas maneiras de exibir os valores de hash: a instrução SELECT final e Showplan XML, onde eles são listados no elemento StmtSimple como valores de atributo de QueryHash e QueryPlanHash.
USE AdventureWorks;
GO
SET STATISTICS XML ON;
GO
SELECT T.TransactionID, T.TransactionDate, P.ProductID, P.Name FROM Production.TransactionHistory T
JOIN Production.Product P
ON T.ProductID = P.ProductID
WHERE P.ProductID = 1;
GO
SELECT T.TransactionID, T.TransactionDate, P.ProductID, P.Name FROM Production.TransactionHistory T
JOIN Production.Product P
ON T.ProductID = P.ProductID
WHERE P.ProductID = 3;
GO
SET STATISTICS XML OFF;
GO
--Show the query_hash and query plan hash
SELECT ST.text AS "Query Text", QS.query_hash AS "Query Hash",
QS.query_plan_hash AS "Query Plan Hash"
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text (QS.sql_handle) ST
WHERE ST.text = 'SELECT T.TransactionID, T.TransactionDate, P.ProductID, P.Name FROM Production.TransactionHistory T
JOIN Production.Product P
ON T.ProductID = P.ProductID
WHERE P.ProductID = 1;
' OR ST.text = 'SELECT T.TransactionID, T.TransactionDate, P.ProductID, P.Name FROM Production.TransactionHistory T
JOIN Production.Product P
ON T.ProductID = P.ProductID
WHERE P.ProductID = 3;
';
GO
Se as estimativas de cardinalidade para ProductID = 3 forem altas, o otimizador de consulta poderá usar o operador de verificação de índice no plano de consulta. Se as estimativas de cardinalidade para ProductID = 1 forem baixas, o otimizador de consulta poderá usar o operador de busca de índice.
Não-exclusividade de valores de hash
É possível ter colisões de hash em que consultas e planos de consulta diferentes tenham o mesmo valor de hash. Embora a possibilidade de haver colisões de hash seja muito pequena e de ser pouco provável que elas ocorram, os aplicativos que dependem da exclusividade do hash de consulta e do hash de plano de consulta podem ter erros, devido a valores de hash duplicados. Por exemplo, os hashes de consulta e de plano de consulta não devem ser usados como chave primária ou em uma coluna exclusiva.
Descobrindo o custo acumulado de consultas
O exemplo a seguir retorna informações sobre as cinco principais consultas de acordo com o tempo médio de CPU. Este exemplo agrega as consultas de acordo com seu hash de consulta, portanto as consultas com lógica equivalente são agrupadas pelo consumo acumulado de recursos.
USE AdventureWorks;
GO
SELECT TOP 5 query_stats.query_hash AS "Query Hash",
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
MIN(query_stats.statement_text) AS "Statement Text"
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
GO
O exemplo a seguir retorna informações sobre os cinco principais planos de consulta de acordo com o tempo médio de CPU. Este exemplo agrega as consultas de acordo com seu hash de plano de consulta, portanto as consultas que têm o mesmo hash de plano de consulta são agrupadas pelo consumo acumulado de recursos.
USE AdventureWorks;
GO
SELECT TOP 5 query_plan_hash AS "Query Plan Hash",
SUM(total_worker_time)/SUM(execution_count) AS "Avg CPU Time",
MIN(CAST(query_plan as varchar(max))) AS "ShowPlan XML"
FROM sys.dm_exec_query_stats AS QS CROSS APPLY
sys.dm_exec_query_plan(QS.plan_handle)
GROUP BY query_plan_hash
ORDER BY 2 DESC;
GO
Usando os hashes de consulta e de plano de consulta para melhorar o desempenho de consulta
Controlar e investigar alterações nos planos de execução
Reduções ou melhorias de desempenho podem ocorrer quando uma consulta é recompilada e o otimizador de consulta gera um plano de execução de consulta diferente. Usando o hash de plano de consulta, você pode capturar, armazenar e comparar planos de execução de consulta ao longo do tempo. Saber quais planos de execução foram alterados pode lhe ajudar a diagnosticar o impacto das alterações de dados e de configuração sobre o desempenho.
Por exemplo, depois de alterar a configuração do sistema, é possível comparar os valores de hash de plano de consulta relativos a consultas essenciais com os valores de hash de plano de consulta originais. As diferenças nos valores de hash de plano de consulta podem indicar se a alteração na configuração do sistema resultou em planos de execução de consulta atualizados para consultas importantes. Você também poderá optar por interromper a execução de uma consulta atual que está sendo executada há bastante tempo caso o hash do respectivo plano de consulta em sys.dm_exec_requests seja diferente do hash do plano de consulta de linha de base, conhecido por ter bom desempenho.
Parametrizar consultas semelhantes para melhorar a reutilização de planos armazenados em cache
Se um conjunto de consultas tiver os mesmos hashes de consulta e de plano de consulta, você poderá melhorar o desempenho criando uma consulta parametrizada. Chamar uma consulta com parâmetros em vez de várias consultas com valores literais permite a reutilização do plano de execução de consulta armazenado em cache. Para obter mais informações sobre os benefícios da reutilização de planos de consulta armazenados em cache, consulte Reutilização e armazenamento em cache do plano de execução.
Se não for possível modificar o aplicativo, você poderá usar guias de plano de modelo com parametrização forçada para obter um resultado semelhante. Para obter mais informações, consulte Especificando comportamento de parametrização de consulta usando guias de plano.