使用查询和查询计划哈希值查找和优化类似查询

搜索占用大量资源的查询时，应考虑如何查找和优化共同消耗大量系统资源的类似查询。sys.dm_exec_query_stats 和 sys.dm_exec_requests 动态管理视图提供了查询哈希值和查询计划哈希值，这些值可用于帮助确定类似查询和类似查询执行计划的聚合资源使用情况。

本主题定义了查询哈希和查询计划哈希值，举例说明如何使用这些哈希值查找类似查询和执行计划的累积开销，并提供了一些可用来提高类似查询和执行计划性能的方法。

了解查询哈希和查询计划哈希

查询哈希是对查询进行计算得出的二进制哈希值，用于标识具有类似逻辑的查询。查询优化器会在查询编译期间计算查询哈希。仅文字值不同的查询具有相同的查询哈希。例如，以下两个查询具有相同的查询哈希，因为这两个查询只有分配给 FirstName 和 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

以下两个查询具有不同的查询哈希，因为其逻辑（AND 与 OR）存在差异且不仅限于文字不同。

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

查询计划哈希是对查询执行计划进行计算得出的二进制哈希值，用于标识类似查询执行计划。查询优化器会在查询编译期间使用执行计划值（如逻辑和物理运算符以及重要运算符属性的子集）计算查询计划哈希。具有相同物理和逻辑运算符树结构以及部分重要运算符属性的属性值相同的查询执行计划将具有相同的查询计划哈希。

当针对不同数据执行具有相同查询哈希的查询时，查询结果的基数差可能导致查询优化器选择不同的查询执行计划，从而产生不同的查询计划哈希。

下面的示例说明两个类似查询为何可以有相同的查询哈希值却可能没有相同的查询执行计划。查看哈希值有两种方法：最终的 SELECT 语句和 Showplan XML，哈希值在其中的 StmtSimple 元素中作为 QueryHash 和 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

如果 ProductID = 3 的基数估计较高，则查询优化器可能会在查询计划中使用索引扫描运算符。如果 ProductID = 1 的基数估计较低，则查询优化器可能会使用索引查找运算符。

哈希值的不唯一性

可能会出现不同查询和查询计划具有相同哈希值的哈希冲突。虽然哈希冲突的可能性很小且不太可能发生，但与查询哈希和查询计划哈希的唯一性有关的应用程序可能会由于重复的哈希值而出错。例如，查询哈希和查询计划哈希不应该作为主键使用或在唯一列中使用。

查找查询的累积开销

下面的示例根据平均 CPU 时间返回与前五个查询有关的信息。此示例根据查询的查询哈希将其聚合起来，这样，在逻辑上相等的查询便会按照其资源累积消耗量进行分组。

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

下面的示例根据平均 CPU 时间返回与前五个查询有关的信息。此示例根据查询的查询计划哈希将其聚合起来，这样，查询计划哈希相同的查询便会按照其资源累积消耗量进行分组。

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

使用查询哈希和查询计划哈希提高查询性能

跟踪并调查执行计划更改

重新编译查询和查询优化器生成不同查询执行计划时，可能发生性能下降或性能提高的情况。使用查询计划哈希可以捕获、存储和比较某段时间内的查询执行计划。了解哪个执行计划已发生改变会有助于对数据和配置更改所造成的性能影响进行诊断。

例如，更改系统配置之后，可以将任务关键查询的查询计划哈希值与其原始查询计划哈希值进行比较。通过查询计划哈希值的不同可以了解系统配置更改是否会导致更新重要查询的查询执行计划。如果 sys.dm_exec_requests 中当前长时间运行的查询的查询计划哈希与其基准查询计划哈希（通常认为该查询具有良好的性能）不同，则也可以决定停止执行该查询。

将类似查询参数化以提高缓存计划重用率

如果一组查询具有相同的查询哈希和查询计划哈希，则可以通过创建一个参数化查询来提高性能。如果调用具有参数的一个查询，而不是调用具有文字值的多个查询，则会允许重用缓存查询执行计划。有关重用缓存查询计划的优点的详细信息，请参阅执行计划的缓存和重新使用。

如果无法修改应用程序，则可以使用带有强制参数化的模板计划指南来获得类似结果。有关详细信息，请参阅使用计划指南指定查询参数化行为。

请参阅

参考

sys.dm_exec_query_stats (Transact-SQL)

sys.dm_exec_requests (Transact-SQL)

概念

查询优化建议

用于对运行慢的查询进行分析的清单