Ricerca e ottimizzazione di query analoghe tramite valori hash della query e del piano di query
Quando si esegue la ricerca di query che utilizzano un numero elevato di risorse, è necessario considerare il modo in cui ricercare e ottimizzare query analoghe che collettivamente utilizzano risorse di sistema significative. Le viste a gestione dinamica sys.dm_exec_query_stats e sys.dm_exec_requests forniscono valori hash della query e del piano di query che è possibile utilizzare per determinare l'utilizzo delle risorse aggregate per query e piani di esecuzione di query analoghi.
In questo argomento viene definito il valore hash della query e del piano di query, vengono forniti esempi dell'utilizzo dei valori hash per individuare il costo cumulativo di query e di piani di esecuzione analoghi e vengono descritte le modalità di ottimizzazione delle prestazioni per query e piani di esecuzione analoghi.
Informazioni sul valore hash della query e del piano di query
Per valore hash della query si intende un valore hash binario calcolato sulla query che consente di identificare query con logica analoga. Il valore hash della query viene calcolato in Query Optimizer durante la compilazione della query stessa. Alle query che differiscono solo per valori letterali è associato lo stesso valore hash. Il valore hash delle due query seguenti, ad esempio, è lo stesso poiché le query differiscono solo per i valori letterali assegnati a FirstName e 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
Alle due query seguenti sono associati invece valori hash diversi poiché le differenze sono logiche (utilizzo dell'operatore AND anziché OR) e non limitate ai soli valori letterali.
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
Per valore hash del piano di query si intende un valore hash binario calcolato sul piano di esecuzione di query che consente di identificare piani di esecuzioni analoghi. Il valore hash del piano di esecuzione di query viene calcolato in Query Optimizer durante la compilazione della query stessa tramite valori del piano di esecuzione diversi, ad esempio operatori logici e fisici e un subset di importanti attributi relativi. Ai piani di esecuzione di query con la stessa struttura ad albero degli operatori logici e fisici e con valori identici per il subset degli importanti attributi relativi è associato lo stesso valore hash.
Quando si eseguono query con valori hash identici su dati diversi, le differenze nella cardinalità dei risultati delle query possono provocare la scelta di piani di esecuzione di query differenti da parte di Query Optimizer, con la conseguente restituzione di valori hash del piano di query diversi.
Nell'esempio seguente viene illustrato il modo in cui a due query analoghe può essere associato lo stesso valore hash, ma un piano di esecuzione diverso. È possibile visualizzare i valori hash in due modi diversi, ovvero tramite l'istruzione SELECT finale e Showplan XML, nel punto in cui vengono elencati nell'elemento StmtSimple come valori di attributo per 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 la stima relativa alla cardinalità per ProductID = 3 è elevata, Query Optimizer potrebbe utilizzare l'operatore Index Scan nel piano di query. Se la stima relativa alla cardinalità per ProductID = 1 non è elevata, Query Optimizer potrebbe utilizzare l'operatore Index Seek.
Valori hash non univoci
In query e piani di query non analoghi con lo stesso valore hash è possibile che si verifichino collisioni hash. Sebbene la probabilità di questo tipo di collisioni non sia elevata, nelle applicazioni che dipendono dall'univocità del valore hash della query e del piano di query potrebbero verificarsi errori a causa di valori hash duplicati. Il valore hash della query e del piano di query, ad esempio, non deve essere utilizzato come una chiave primaria oppure in una colonna univoca.
Ricerca del costo cumulativo delle query
Nell'esempio seguente vengono restituite informazioni sulle prime cinque query in base al tempo medio della CPU. Nell'esempio le query vengono aggregate in base al relativo valore hash, in modo da raggruppare le query logicamente equivalenti in base all'utilizzo di risorse cumulativo.
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
Nell'esempio seguente vengono restituite informazioni sui primi cinque piani di query in base al tempo medio della CPU. Nell'esempio le query vengono aggregate in base al relativo valore hash del piano, in modo da raggruppare le query con lo stesso valore hash del piano in base all'utilizzo di risorse cumulativo.
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
Utilizzo del valore hash della query e del piano di query per migliorare le prestazioni
Rilevamento e analisi delle modifiche del piano di esecuzione
Quando una query viene ricompilata e in Query Optimizer viene creato un piano di esecuzione di query diverso, è possibile che si verifichino riduzioni o miglioramenti delle prestazioni. Utilizzando il valore hash del piano di query, è possibile acquisire, archiviare e confrontare piani di esecuzione di query nel tempo. Se si conoscono i piani di esecuzione modificati, è possibile diagnosticare l'impatto sulle prestazioni delle modifiche apportate a dati e configurazione.
Dopo avere modificato la configurazione di sistema, è possibile confrontare valori hash del piano di query per le query di importanza critica con i valori hash originali. Le differenze nei valori hash del piano di query possono indicare se la modifica della configurazione di sistema ha provocato l'aggiornamento di piani di esecuzione per query importanti. È inoltre possibile decidere di interrompere l'esecuzione di una query corrente con esecuzione prolungata se il valore hash del piano di query in sys.dm_exec_requests differisce da quello del piano di base, nel caso in cui sia noto che le prestazioni di quest'ultimo sono elevate.
Parametrizzazione di query analoghe per migliorare il riutilizzo del piano memorizzato nella cache
Se a un set di query è associato lo stesso valore hash della query e del piano di query, per migliorare le prestazioni è possibile creare una query con parametri. La chiamata di una query con parametri anziché di più query con valori letterali consente di riutilizzare il piano di esecuzione di query memorizzato nella cache. Per ulteriori informazioni sui vantaggi relativi al riutilizzo dei piani della query memorizzati nella cache, vedere Caching e riutilizzo del piano di esecuzione.
Se non è possibile modificare l'applicazione, utilizzare guide di piano del modello con parametrizzazione forzata per ottenere un risultato analogo. Per ulteriori informazioni, vedere Definizione delle funzionalità di parametrizzazione delle query tramite guide di piano.