• Article

Recherche et paramétrage de requêtes semblables à l'aide de requête et de hachages de plan de requête

Lorsque vous recherchez des requêtes gourmandes en ressources, vous devez considérer comment rechercher et paramétrer des requêtes semblables qui collectivement consomment des ressources système significatives. Les vues de gestion dynamique sys.dm_exec_query_stats et sys.dm_exec_requests fournissent des valeurs de hachage de requête et des valeurs de hachage de plan de requête que vous pouvez utiliser pour mieux déterminer l'utilisation globale des ressources pour les requêtes semblables et les plans d'exécution de requêtes semblables.

Cette rubrique définit le hachage de requête et le hachage de plan de requête, donne des exemples d'utilisation des valeurs de hachage pour rechercher le coût cumulatif de requêtes et plans d'exécution semblables, et propose des solutions permettant d'améliorer la performance de requêtes et de plans d'exécution semblables.

Fonctionnement du hachage de requête et du hachage de plan de requête

Le hachage de requête est une valeur de hachage binaire calculée sur la requête et utilisée pour identifier des requêtes avec une logique similaire. L'optimiseur de requête calcule le hachage de requête lors de la compilation de la requête. Les requêtes qui diffèrent uniquement par les valeurs littérales ont le même hachage de requête. Par exemple, les deux requêtes suivantes ont le même hachage de requête car elles diffèrent uniquement par les valeurs littérales affectées à FirstName and 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

Les deux requêtes suivantes ont des hachages de requête différents parce que leurs différences sont d'ordre logique (AND et OR) et ne se limitent pas aux valeurs littérales.

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

Le hachage de plan de requête est une valeur de hachage binaire calculée sur le plan d'exécution de requête et utilisée pour identifier des plans d'exécution de requête semblables. L'optimiseur de requête calcule le hachage de plan de requête pendant le compilation de la requête, à l'aide de valeurs de plan de l'exécution telles que les opérateurs logiques et physiques, et un sous-ensemble d'attributs d'opérateur importants. Les plans d'exécution de requête qui ont la même arborescence d'opérateurs logiques et physiques, ainsi que des valeurs d'attribut identiques pour le sous-ensemble d'attributs d'opérateur importants, auront le même hachage de plan de requête.

Lorsque des hachages de requête identiques sont exécutés sur des données différentes, les différences au niveau de le cardinalité des résultats de la requête peuvent amener l'optimiseur de requête à choisir des plans d'exécution de requête différents, d'où des hachages de plan de requête différents.

L'exemple suivant montre comment deux requêtes semblables peuvent avoir le même hachage de requête, mais ne pas avoir le même plan d'exécution de requête. Il y a deux façons d'afficher les valeurs de hachage : la dernière instruction SELECT et le Showplan XML, où elles sont répertoriées dans l'élément StmtSimple comme valeurs d'attribut pour QueryHash et 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

Si les estimations de cardinalité pour ProductID = 3 sont élevées, l'optimiseur de requête peut utiliser l'opérateur d'analyse d'index dans le plan de requête. Si les estimations de cardinalité pour ProductID = 1 sont basses, l'optimiseur de requête peut utiliser l'opérateur de recherche d'index.

Non-unicité des valeurs de hachage

Il est possible d'avoir des collisions de hachage dans lesquelles des requêtes et plans de requête dissemblables ont la même valeur de hachage. Bien que la probabilité de collisions de hachage soit faible et peu probable, les applications qui dépendent de l'unicité du hachage de requête et du hachage de plan de requête peuvent avoir des erreurs en raison de valeurs de hachage en double. Par exemple, le hachage de requête et le hachage de plan de requête ne doivent pas être utilisés comme clé primaire ou dans une colonne unique.

Recherche du coût cumulatif des requêtes

L'exemple suivant retourne des informations sur les cinq principales requêtes d'après le temps processeur moyen. Cet exemple regroupe les requêtes d'après leur hachage de requête afin que les requêtes logiquement équivalentes soient groupées par leur consommation de ressources cumulative.

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

L'exemple suivant retourne des informations sur les cinq principaux plans de requête d'après le temps processeur moyen. Cet exemple regroupe les requêtes d'après leur hachage de plan de requête afin que les requêtes avec le même hachage de plan de requête soient groupées par leur consommation de ressources cumulative.

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

Utilisation du hachage de requête et du hachage de plan de requête pour améliorer la performance des requêtes

Suivre et examiner les modifications du plan de l'exécution

Des dégradations ou des améliorations de performances peuvent se produire lorsqu'une requête est recompilée et que l'optimiseur de requête génère un plan d'exécution de requête différent. À l'aide du hachage de plan de requête, vous pouvez capturer, stocker et comparer des plans d'exécution de requêtes sur la durée. Le fait de connaître les plans d'exécution qui ont changé peut vous aider à diagnostiquer l'impact sur les performances après les modifications dans la configuration et les données.

Par exemple, après avoir modifié la configuration du système, vous pouvez comparer des valeurs de hachage de plan de requête pour les requêtes critiques à leurs valeurs de hachage du plan de requête d'origine. Les différences dans les valeurs de hachage de plan de requête peuvent vous indiquer si la modification de configuration du système s'est traduite par des plans d'exécution de requêtes mis à jour pour les requêtes importantes. Vous pouvez également décider d'arrêter l'exécution d'une requête longue actuelle si son hachage de plan de requête dans sys.dm_exec_requests diffère de son hachage du plan de requête de la ligne de base afin de bénéficier de bonnes performances.

Paramétrer des requêtes semblables afin d'améliorer la réutilisation des plans mis en cache

Si un jeu de requêtes a les mêmes hachage de requête et hachage de plan de requête, vous pouvez améliorer les performances en créant une requête paramétrable. Le fait d'appeler une seule requête avec des paramètres au lieu de requêtes multiples avec des valeurs littérales autorise la réutilisation du plan d'exécution de requêtes mis en cache. Pour plus d'informations sur les avantages de la réutilisation des plans de requêtes mis en cache, consultez Mise en mémoire cache et réutilisation du plan d'exécution.

Si vous ne pouvez pas modifier l'application, vous pouvez utiliser des guides de plan de modèle avec paramétrage forcé pour obtenir un résultat semblable. Pour plus d'informations, consultez Indication du comportement du paramétrage de requêtes grâce aux repères de plan.