• 發行項

使用查詢和查詢計畫雜湊來尋找並微調類似的查詢

搜尋需要大量資源的查詢時，您應該考量如何尋找並微調共同耗用大量系統資源的類似查詢。 sys.dm_exec_query_stats 和 sys.dm_exec_requests 動態管理檢視提供了查詢雜湊和查詢計畫雜湊值，可讓您用來協助判斷類似查詢與類似查詢執行計畫的彙總資源使用量。

本主題定義了查詢雜湊和查詢計畫雜湊、提供使用雜湊值來尋找類似查詢與執行計畫之累計成本的範例，以及提供針對類似查詢與執行計畫強化效能的方式。

了解查詢雜湊和查詢計畫雜湊

查詢雜湊是針對查詢所計算的二進位雜湊值，可用來識別含有類似邏輯的查詢。 查詢最佳化工具會在查詢編譯期間計算查詢雜湊。 只有常值不同的查詢會具有相同的查詢雜湊。 例如，下列兩個查詢會具有相同的查詢雜湊，因為它們只有指派給 FirstName 和 LastName 的常值不同。

USE AdventureWorks2008R2;
GO
SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City 
FROM Person.Person AS P
    JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
    JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
    JOIN Person.Address AS A ON A.AddressID = BA.AddressID
    WHERE P.FirstName = 'Amanda' AND P.LastName = 'Allen';
GO
SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City 
FROM Person.Person AS P
    JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
    JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
    JOIN Person.Address AS A ON A.AddressID = BA.AddressID
    WHERE P.FirstName = 'Logan' AND P.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, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City 
FROM Person.Person AS P
    JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
    JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
    JOIN Person.Address AS A ON A.AddressID = BA.AddressID
    WHERE P.FirstName = ''Amanda'' AND P.LastName = ''Allen'';
' OR st.text = 'SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City 
FROM Person.Person AS P
    JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
    JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
    JOIN Person.Address AS A ON A.AddressID = BA.AddressID
    WHERE P.FirstName = ''Logan'' AND P.LastName = ''Jones'';
';
GO

下列兩個查詢會具有不同的查詢雜湊，因為它們的差異是邏輯 (AND 與 OR) 而非僅受限於常值。

USE AdventureWorks2008R2;
GO
SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City 
FROM Person.Person AS P
    JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
    JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
    JOIN Person.Address AS A ON A.AddressID = BA.AddressID
    WHERE P.FirstName = 'Amanda' AND P.LastName = 'Allen';
GO
SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City 
FROM Person.Person AS P
    JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
    JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
    JOIN Person.Address AS A ON A.AddressID = BA.AddressID
    WHERE P.FirstName = 'Logan' OR P.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, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City 
FROM Person.Person AS P
    JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
    JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
    JOIN Person.Address AS A ON A.AddressID = BA.AddressID
    WHERE P.FirstName = ''Amanda'' AND P.LastName = ''Allen'';
' OR st.text = 'SELECT I.CustomerID, I.AccountNumber, P.FirstName, P.LastName, A.AddressLine1, A.City 
FROM Person.Person AS P
    JOIN Sales.Customer AS I ON P.BusinessEntityID = I.PersonID
    JOIN Person.BusinessEntityAddress AS BA ON BA.BusinessEntityID = I.PersonID
    JOIN Person.Address AS A ON A.AddressID = BA.AddressID
    WHERE P.FirstName = ''Logan'' OR P.LastName = ''Jones'';
';
GO

查詢計畫雜湊是針對查詢執行計畫所計算的二進位雜湊值，可用來識別類似的查詢執行計畫。 查詢最佳化工具會在查詢編譯期間使用執行計畫值 (例如邏輯和實體運算子) 以及重要運算子屬性的子集來計算查詢計畫雜湊。 具有相同實體和邏輯運算子樹狀結構而且重要運算子屬性子集具有相同屬性值的查詢執行計畫將會具有相同的查詢計畫雜湊。

針對不同的資料執行具有相同查詢雜湊的查詢時，查詢結果的基數差異可能會導致查詢最佳化工具選擇不同的查詢執行計畫，因而產生不同的查詢計畫雜湊。

下列範例將顯示兩個類似的查詢如何具有相同的查詢雜湊，但是可能沒有相同的查詢執行計畫。 有兩種方式可以檢視這些雜湊值：最終的 SELECT 陳述式和 Showplan XML，它們列於 StmtSimple 元素中，當做 QueryHash 和 QueryPlanHash 的屬性值。

USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 中的查詢計畫雜湊與其基準查詢計畫雜湊不同 (表示提升效能)，您可能也會決定停止執行目前長時間執行的查詢。

將類似的查詢參數化，以便改善快取計畫重複使用

如果有一組查詢具有相同的查詢雜湊和查詢計畫雜湊，您可能會透過建立一個參數化查詢，改善效能。 呼叫含有參數的單一查詢而非含有常值的多個查詢可以重複使用快取的查詢執行計畫。 如需有關重複使用快取查詢計畫之優點的詳細資訊，請參閱＜執行計畫快取與重複使用＞。

如果您無法修改應用程式，可以使用具有強制參數化的範本計畫指南來達成相同的結果。 如需詳細資訊，請參閱＜使用計畫指南指定查詢參數化行為＞。