Suchen und Optimieren von ähnlichen Abfragen mit Abfrage und Abfrageplanhashes
Bei der Suche nach ressourcenintensiven Abfragen sollten Sie berücksichtigen, wie Sie ähnliche Abfragen finden und optimieren können, die zusammen eine große Menge an Systemressourcen beanspruchen. Die dynamischen Verwaltungssichten sys.dm_exec_query_stats und sys.dm_exec_requests stellen Abfrage- und Abfrageplan-Hashwerte bereit, anhand derer Sie die Gesamtressourcenverwendung für ähnliche Abfragen und Abfrageausführungspläne ermitteln können.
Dieses Thema beinhaltet eine Beschreibung der Abfrage- und Abfrageplanhashes, Beispiele zum Suchen der kumulierten Kosten ähnlicher Abfragen und Abfrageausführungspläne mithilfe der Hashwerte sowie Methoden zum Optimieren der Leistung für ähnliche Abfragen und Abfrageausführungspläne.
Grundlegendes zu Abfrage- und Abfrageplanhashes
Der Abfragehash ist ein binärer Hashwert, der in der Abfrage berechnet wird und zum Identifizieren von Abfragen mit ähnlicher Logik verwendet wird. Der Abfrageoptimierer berechnet den Abfragehash während der Abfragekompilierung. Abfragen, die sich nur durch Literalwerte unterscheiden, verfügen über den gleichen Abfragehash. Die beiden folgenden Abfragen haben beispielsweise den gleichen Abfragehash, da sie sich nur durch die Literalwerte unterscheiden, die FirstName und LastName zugeordnet sind.
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
Die Abfragehashes der beiden folgenden Abfragen unterscheiden sich, da die Abfragelogik eine andere ist (AND statt OR) und sich die Abweichungen nicht nur auf die Literalwerte beschränken.
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
Der Abfrageplanhash ist ein binärer Hashwert, der im Abfrageausführungsplan berechnet wird und zum Identifizieren ähnlicher Abfrageausführungspläne verwendet wird. Der Abfrageoptimierer berechnet den Abfrageplanhash während der Abfragekompilierung anhand der Ausführungsplanwerte, z. B. der logischen und physischen Operatoren, und einer Teilmenge wichtiger Operatorattribute. Abfrageausführungspläne mit der gleichen physischen und logischen Operatorstruktur sowie identischen Werten für die Teilmenge der wichtigen Operatorattribute weisen den gleichen Abfrageplanhash auf.
Wenn Abfragen mit identischen Abfragehashes für unterschiedliche Daten ausgeführt werden, können die Unterschiede in Bezug auf die Kardinalität der Abfrageergebnisse dazu führen, dass der Abfrageoptimierer verschiedene Abfrageausführungspläne auswählt, sodass sich die Abfrageplanhashes unterscheiden.
Das folgende Beispiel veranschaulicht, dass zwei ähnliche Abfragen zwar den gleichen Abfragehash, jedoch unterschiedliche Abfrageausführungspläne aufweisen können. Die Hashwerte können auf zwei Arten angezeigt werden: mit der abschließenden SELECT-Anweisung oder mit Showplan XML, wo sie im Element StmtSimple als Attributwerte für QueryHash und QueryPlanHash aufgeführt werden.
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
Bei einer hohen Kardinalitätsschätzung für ProductID = 3 kann der Abfrageoptimierer den Index Scan-Operator im Abfrageplan verwenden. Bei einer niedrigen Kardinalitätsschätzung für ProductID = 1 kann der Abfrageoptimierer den Index Seek-Operator verwenden.
Nicht eindeutige Hashwerte
Es besteht die Möglichkeit eines Hashkonflikts, bei dem unterschiedliche Abfragen und Abfragepläne den gleichen Hashwert aufweisen. Die Wahrscheinlichkeit eines Hashkonflikts ist zwar relativ gering, Anwendungen, die einen eindeutigen Abfrage- und Abfrageplanhash voraussetzen, können jedoch bei doppelten Hashwerten Fehler auslösen. Der Abfragehash und der Abfrageplanhash sollten daher beispielsweise nicht als Primärschlüssel oder in einer eindeutigen Spalte verwendet werden.
Suchen der kumulierten Kosten von Abfragen
Das folgende Beispiel gibt Informationen zu den fünf Abfragen mit der höchsten durchschnittlichen CPU-Zeit zurück. Die Abfragen werden in diesem Beispiel anhand des Abfragehashes aggregiert, sodass logisch identische Abfragen basierend auf dem kumulierten Ressourcenverbrauch gruppiert werden.
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
Das folgende Beispiel gibt Informationen zu den fünf Abfrageplänen mit der höchsten durchschnittlichen CPU-Zeit zurück. Die Abfragen werden in diesem Beispiel anhand des Abfrageplanhashes aggregiert, sodass Abfragen mit dem gleichen Abfrageplanhash basierend auf dem kumulierten Ressourcenverbrauch gruppiert werden.
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
Optimieren der Abfrageleistung mithilfe des Abfragehashes und des Abfrageplanhashes
Nachverfolgen und Analysieren von Ausführungsplanänderungen
Wenn eine Abfrage neu kompiliert wird und der Abfrageoptimierer einen anderen Abfrageausführungsplan erzeugt, können Leistungseinbußen oder auch Leistungsverbesserungen auftreten. Mithilfe des Abfrageplanhashes können Sie die Abfrageausführungspläne erfassen, speichern und vergleichen. Indem Sie ermitteln, welche Ausführungspläne geändert wurden, können Sie die Auswirkungen von Daten- und Konfigurationsänderungen auf die Leistung diagnostizieren.
Nach einer Änderung der Systemkonfiguration können Sie beispielsweise die Abfrageplan-Hashwerte für unternehmenswichtige Abfragen mit den ursprünglichen Abfrageplan-Hashwerten vergleichen. Abweichungen zwischen den Abfrageplan-Hashwerten geben Aufschluss darüber, ob durch die Änderung der Systemkonfiguration die Abfrageausführungspläne für wichtige Abfragen aktualisiert wurden. Darüber hinaus können Sie eine aktuelle Abfrage mit länger Ausführungszeit anhalten, wenn der Abfrageplan-Hashwert in sys.dm_exec_requests vom Basishashwert des Abfrageplans abweicht und letzterer eine gute Leistung gezeigt hat.
Optimieren der Wiederverwendung von zwischengespeicherten Plänen durch Parametrisierung ähnlicher Abfragen
Wenn mehrere Abfragen den gleichen Abfrage- und Abfrageplanhash aufweisen, können Sie durch Erstellen einer parametrisierten Abfrage die Leistung optimieren. Durch das Aufrufen einer Abfrage mit Parametern anstelle mehrerer Abfragen mit Literalwerten kann der zwischengespeicherte Abfrageausführungsplan wiederverwendet werden. Weitere Informationen über die Vorteile, die sich durch eine Wiederverwendung zwischengespeicherter Abfragepläne ergeben, finden Sie unter Zwischenspeichern und Wiederverwenden von Ausführungsplänen.
Kann die Anwendung nicht geändert werden, können Sie mithilfe von TEMPLATE-Planhinweislisten mit erzwungener Parametrisierung ein ähnliches Ergebnis erzielen. Weitere Informationen finden Sie unter Angeben des Abfrageparametrisierungsverhaltens mithilfe von Planhinweislisten.