Comprendre les indicateurs de requête

  • 5 minutes

Les indicateurs de requête sont des options ou des stratégies qui peuvent être appliquées pour que le processeur de requêtes utilise un opérateur particulier dans le plan d’exécution pour les instructions SELECT, INSERT, UPDATE ou DELETE. Les indicateurs de requête remplacent tout plan d’exécution que le processeur de requêtes peut sélectionner pour une requête donnée avec la clause OPTION.

Dans la plupart des cas, l’optimiseur de requête sélectionne un plan d’exécution efficace en fonction des index, des statistiques et de la distribution des données. Les administrateurs de base de données doivent rarement intervenir manuellement.

Vous pouvez modifier le plan d’exécution de la requête en ajoutant des indicateurs de requête à la fin de la requête. Par exemple, si vous ajoutez OPTION (MAXDOP <integer_value>) à la fin d’une requête qui utilise un processeur unique, la requête peut utiliser plusieurs processeurs (parallélisme) en fonction de la valeur que vous choisissez. Vous pouvez également utiliser OPTION (RECOMPILE) pour vous assurer que la requête génère un nouveau plan temporaire chaque fois qu’il est exécuté.

--With maxdop hint
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM Sales.SalesOrderDetail  
WHERE UnitPrice < $5.00  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (MAXDOP 2)
GO

--With recompile hint
SELECT City
FROM Person.Address
WHERE StateProvinceID=15 OPTION (RECOMPILE)
GO

Bien que les indicateurs de requête puissent fournir une solution localisée à divers problèmes liés aux performances, vous devez éviter de les utiliser dans l’environnement de production pour les raisons suivantes.

  • La présence d’un indicateur de requête permanent sur votre requête peut entraîner la non application de modifications de base de données structurelles qui seraient utiles à cette requête.
  • Vous ne pouvez pas bénéficier de fonctionnalités nouvelles et améliorées dans les versions ultérieures de SQL Server si vous liez une requête à un plan d’exécution spécifique.

Toutefois, plusieurs indicateurs de requête sont disponibles sur SQL Server et sont utilisés à des fins différentes. Examinons quelques-uns d’entre eux ci-dessous :

  • FAST <integer_value> : récupère les <integer_value> (nombre) premières lignes lors de la poursuite de l’exécution des requêtes. Il fonctionne mieux avec les petits jeux de données et une faible valeur pour l’indicateur de requête rapide. À mesure que le nombre de lignes augmente, le coût des requêtes devient plus élevé.

  • OPTIMIZE FOR : fournit des instructions à l’optimiseur de requête sur une valeur particulière pour une variable locale à utiliser lorsqu’une requête est compilée et optimisée.

  • USE PLAN : l’optimiseur de requête utilise un plan de requête spécifié par l’attribut xml_plan.

  • RECOMPILE : crée un nouveau plan temporaire pour la requête et l’ignore immédiatement après l’exécution de la requête.

  • { LOOP | MERGE | HASH } JOIN : indique que toutes les opérations de jointure sont effectuées par LOOP JOIN, MERGE JOIN ou HASH JOIN dans la requête. L’optimiseur choisit la stratégie de jointure la moins coûteuse parmi les options si vous spécifiez plusieurs indicateurs de jointure.

  • MAXDOP <integer_value> : remplace la valeur de degré maximal de parallélisme de sp_configure. La requête spécifiant cette option remplace également le Resource Governor.

Vous pouvez également appliquer plusieurs indicateurs de requête dans la même requête. L’exemple suivant utilise les indicateurs de requête et HASH GROUP les FAST <integer_value> dans la même requête.

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM Sales.SalesOrderDetail  
WHERE UnitPrice < $5.00  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (HASH GROUP, FAST 10);  
GO

Pour en savoir plus sur les indicateurs de requête, consultez Indicateurs (Transact-SQL).

Indicateurs du Magasin des requêtes (en préversion)

La fonctionnalité Indicateurs du Magasin des requêtes dans Azure SQL Database fournit une méthode simple pour mettre en forme les plans de requête sans modifier le code de l’application.

Les indicateurs du Magasin des requêtes sont utiles quand l’optimiseur de requête ne génère pas de plan d’exécution efficace, et quand le développeur ou l’administrateur de base de données ne peut pas modifier le texte d’origine de la requête. Dans certaines applications, le texte de requête peut être codé en dur ou généré automatiquement.

Screenshot of how Query Store hints work.

Pour utiliser des indicateurs du Magasin des requêtes, vous devez identifier le query_id du Magasin des requêtes de l’instruction de requête que vous souhaitez modifier via des vues du catalogue Magasin des requêtes, des rapports du Magasin des requêtes intégrés ou Query Performance Insight pour Azure SQL Database. Exécutez ensuite sp_query_store_set_hints avec le query_id et la chaîne d’indicateur de requête que vous souhaitez appliquer à la requête.

L’exemple ci-dessous montre comment obtenir le query_id d’une requête spécifique, puis l’utiliser pour appliquer les indicateurs RECOMPILE et MAXDOP à la requête.

SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt 
    INNER JOIN sys.query_store_query q 
        ON qt.query_text_id = q.query_text_id 
WHERE query_sql_text like N'%ORDER BY CustomerName DESC%'  
  AND query_sql_text not like N'%query_store%'
GO

--Assuming the query_id returned by the previous query is 42
EXEC sys.sp_query_store_set_hints @query_id= 42, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1)'
GO

Les indicateurs du Magasin des requêtes peuvent vous aider à résoudre les problèmes de performances au niveau des requêtes dans quelques scénarios.

  • Recompilation d’une requête à chaque exécution.
  • Limitation du degré maximal de parallélisme pour une opération de mise à jour de statistiques.
  • Utilisation d’une jointure hachée au lieu d’une jointure de boucles imbriquées.
  • Utilisation du niveau de compatibilité 110 pour une requête précise, tout en gardant la base de données au niveau de compatibilité actuel.

Notes

Les indicateurs du Magasin des requêtes sont également pris en charge par SQL Managed Instance.

Pour plus d’informations sur les indicateurs du Magasin des requêtes, consultez Indicateurs du Magasin des requêtes.