クエリおよびクエリ プラン ハッシュを使用した類似クエリの検索およびチューニング
リソースの消費が著しいクエリを見つけるには、類似する複数のクエリが総じて大量のシステム リソースを消費しているようなケースを検出し、このようなクエリをチューニングする方法を検討する必要があります。 sys.dm_exec_query_stats 動的管理ビューおよび sys.dm_exec_requests 動的管理ビューでは、類似する複数のクエリおよび類似する複数のクエリ実行プランを合計したリソース使用率を調べるのに役立つクエリ ハッシュ値およびクエリ プラン ハッシュ値を提供します。
このトピックでは、クエリ ハッシュおよびクエリ プラン ハッシュを定義し、ハッシュ値を使用して類似する複数のクエリおよび実行プランの累積コストを調べ、クエリおよび実行プランのパフォーマンスを向上させる方法を示します。
クエリ ハッシュおよびクエリ プラン ハッシュについて
クエリ ハッシュは、クエリで計算され、類似のロジックを持つクエリを識別するために使用されるバイナリのハッシュ値です。 クエリ オプティマイザーは、クエリのコンパイル時にクエリ ハッシュを計算します。 リテラル値のみが異なるクエリの場合、クエリ ハッシュは同じになります。 たとえば、次の 2 つのクエリの場合、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
たとえば、次の 2 つのクエリの場合、リテラルではなくロジック (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
クエリ プラン ハッシュは、クエリ実行プランで計算され、類似のクエリ実行プランを識別するために使用されるバイナリのハッシュ値です。 クエリ オプティマイザーは、実行プラン値 (論理演算子や物理演算子) と重要な演算子属性のサブセットを使用して、クエリのコンパイル時にクエリ プラン ハッシュを計算します。 クエリ プラン ハッシュが同じになるのは、重要な演算子属性のサブセットに対して属性値が同じであることに加え、物理および論理演算子ツリー構造が同じであるクエリ実行プランのみです。
同じクエリ ハッシュを持つ複数のクエリを異なるデータに対して実行した場合、クエリ結果の基数の差によってクエリ オプティマイザーが異なるクエリ実行プランを選択し、その結果としてクエリ プラン ハッシュが異なる場合があります。
次の例では、2 つの類似するクエリでクエリ ハッシュが同じであっても、クエリ実行プランが異なる場合を示します。 ハッシュ値を表示するには、最終的な SELECT ステートメントを使用する方法と Showplan XML を使用する方法の 2 つの方法があります。ハッシュ値は、QueryHash および QueryPlanHash の属性値の StmtSimple 要素として示されています。
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 時間に基づく上位 5 つのクエリに関する情報を返します。 この例では、クエリ ハッシュに従ってクエリを集計して、論理的に等価のクエリを累積リソース消費量別にグループ化しています。
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 時間に基づく上位 5 つのクエリ プランに関する情報を返します。 この例では、クエリ プラン ハッシュに従ってクエリを集計して、同じクエリ プラン ハッシュを持つクエリを累積リソース消費量別にグループ化しています。
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 のクエリ プラン ハッシュがベースライン クエリ プラン ハッシュと異なる場合は、現在実行中の実行時間の長いクエリを停止することもできます。これにより、パフォーマンスが向上します。
類似するクエリをパラメーター化することによるキャッシュされたプランの再利用性の向上
複数のクエリでクエリ ハッシュおよびクエリ プラン ハッシュが同じである場合、パラメーター化されたクエリを 1 つ作成することでパフォーマンスを向上できます。 リテラル値を指定して複数のクエリを呼び出す代わりに、パラメーターを指定して 1 つのクエリを呼び出すことで、キャッシュされたクエリ実行プランを再利用できます。 キャッシュされたクエリ プランを再利用する場合の利点の詳細については、「実行プランのキャッシュと再利用」を参照してください。
アプリケーションに変更を加えることができない場合は、強制的にパラメーター化を行うテンプレート プラン ガイドを使用することで、同様の結果を得ることができます。 詳細については、「プラン ガイドを使用したクエリのパラメータ化動作の指定」を参照してください。