Bonnes pratiques relatives aux indicateurs du Magasin des requêtes

S’applique à : SQL Server 2022 (16.x) et versions ultérieures Base de données SQL AzureInstance gérée SQL AzureBase de données SQL Azure dans Microsoft Fabric

Cet article détaille les bonnes pratiques pour l’utilisation des indicateurs du Magasin des requêtes. Les indicateurs du Magasin des requêtes permettent de mettre en forme les plans de requête sans modifier le code d’application.

Pour plus d’informations sur la configuration et l’administration avec le Magasin des requêtes, consultez Surveiller les performances à l’aide du Magasin des requêtes.
Pour plus d’informations sur la découverte d’informations exploitables et l’optimisation des performances avec le Magasin des requêtes, consultez Optimiser les performances avec le Magasin des requêtes.
Pour connaître les meilleures pratiques générales sur le Magasin des requêtes, consultez Les meilleures pratiques pour surveiller les charges de travail avec le Magasin des requêtes.

Cas d’usage pour les indicateurs du Magasin des requêtes

Considérez les cas d’usage suivants comme idéaux pour les indicateurs du Magasin des requêtes. Pour plus d’informations, consultez Quand utiliser les indicateurs du Magasin des requêtes.

Caution

Comme l’optimiseur de requête SQL Server sélectionne généralement le meilleur plan d’exécution pour une requête, nous recommandons de ne recourir aux indicateurs qu’en dernier ressort, et à condition d’être un développeur ou un administrateur de base de données expérimenté. Pour plus d’informations, consultez Indicateurs de requête.

Quand le code ne peut pas être changé

L’utilisation des indicateurs du Magasin des requêtes vous permet d’influer sur les plans d’exécution des requêtes sans changer le code d’application ou les objets de base de données. Aucune autre fonctionnalité ne vous permet d’appliquer rapidement et facilement des indicateurs de requête.

Vous pouvez utiliser des indicateurs du Magasin des requêtes, par exemple pour tirer parti des charges de travail ETL (extract-transform-load), sans redéployer du code. Découvrez comment améliorer le chargement en bloc au moyen des indicateurs du Magasin des requêtes avec cette vidéo de 14 minutes :

Les indicateurs du Magasin des requêtes sont des méthodes de réglage des requêtes légères, mais si une requête devient problématique, elle doit être traitée avec des modifications de code plus substantielles. Si vous trouvez régulièrement la nécessité d'appliquer des conseils du Query Store à une requête, envisagez une réécriture plus complète de la requête. L’optimiseur de requête SQL Server sélectionne généralement le meilleur plan d’exécution pour une requête. Nous vous recommandons uniquement d’utiliser des indicateurs comme dernier recours pour les développeurs expérimentés et les administrateurs de base de données.

Pour plus d’informations sur les indicateurs de requête qui peuvent être appliqués, consultez Indicateurs de requête pris en charge.

Sous une charge de transaction élevée ou avec du code critique

Si les modifications de code sont peu pratiques en raison des exigences de temps d’activité élevées ou de la charge transactionnelle, les indicateurs du Magasin des requêtes peuvent appliquer rapidement des indicateurs de requête aux charges de travail de requête existantes. L’ajout et la suppression d’indicateurs du Magasin des requêtes sont faciles.

Il est possible d’ajouter des indicateurs du Magasin des requêtes à des lots de requêtes, ou d’en supprimer, afin d’ajuster les performances au cours des fenêtres planifiées pour des rafales de charges de travail exceptionnelles.

En tant que substitution aux repères de plan

Avant de disposer des indicateurs du Magasin des requêtes, un développeur devait s’appuyer sur des repères de plan pour accomplir des tâches similaires, qui peuvent être complexes à utiliser. Les indicateurs du Magasin des requêtes sont intégrés aux fonctionnalités du Magasin des requêtes de SQL Server Management Studio (SSMS), pour l’exploration visuelle des requêtes.

Avec les repères de plan, il est nécessaire d’effectuer une recherche dans tous les plans en utilisant des extraits de requête. La fonctionnalité des indicateurs du Magasin de requêtes ne nécessite pas de requêtes avec correspondance exacte pour impacter le plan de requête résultant. Les indicateurs du Magasin des requêtes peuvent être appliqués à un query_id dans le jeu de données du Magasin des requêtes.

Les indicateurs du Magasin des requêtes remplacent les indicateurs codés en dur au niveau de l’instruction ainsi que les repères de plan existants.

Envisager un niveau de compatibilité plus récent

Les indicateurs du Magasin des requêtes peuvent être une méthode précieuse lorsqu’un niveau de compatibilité de base de données plus récent n’est pas disponible pour vous en raison de la spécification du fournisseur ou des retards de test plus importants, par exemple. Quand un niveau de compatibilité plus élevé est disponible pour une base de données, envisagez de mettre à niveau le niveau de compatibilité de la base de données pour une requête individuelle afin de tirer parti des dernières optimisations et fonctionnalités de SQL Server en termes de performances.

Par exemple, si vous avez une instance SQL Server 2022 (16.x) avec une base de données au niveau de compatibilité 140, vous pouvez toujours utiliser les indicateurs du Magasin de requêtes pour exécuter des requêtes individuelles au niveau de compatibilité 160. Vous pouvez utiliser l'indicateur suivant :

EXECUTE sys.sp_query_store_set_hints
    @query_id = 39,
    @query_hints = N'OPTION(USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160''))';

Pour profiter d'un didacticiel complet, consultez Exemples d’indicateurs du Magasin de requêtes.

Prendre en compte un niveau de compatibilité plus ancien après la mise à niveau

Un autre cas où les indicateurs du Magasin des requêtes peuvent vous aider est que les requêtes ne peuvent pas être modifiées directement après la migration ou la mise à niveau d’une instance SQL Server. Utilisez les indicateurs du Magasin des requêtes pour appliquer un niveau de compatibilité antérieur pour une requête jusqu’à ce que celle-ci puisse être réécrite ou traitée en vue de s’exécuter correctement dans le dernier niveau de compatibilité. Identifiez les requêtes atypiques qui ont régressé avec un niveau de compatibilité supérieur à l'aide du rapport des requêtes régressées du Magasin de requêtes, qui utilise l'outil Assistant de réglage de requêtes lors d'une migration, ou d'autres données de télémétrie spécifiques aux requêtes. Pour plus d’informations sur les différences entre les niveaux de compatibilité, passez en revue les différences entre les niveaux de compatibilité.

Après avoir testé le nouveau niveau de compatibilité et déployé les indicateurs du Magasin des requêtes en conséquence, vous pouvez mettre à niveau le niveau de compatibilité de toute la base de données tout en conservant les requêtes problématiques clés au niveau de compatibilité antérieur, et ce, sans aucune modification du code.

Bloquer l’exécution future de requêtes problématiques

Vous pouvez utiliser l’indicateur de requête pour bloquer l’exécution ABORT_QUERY_EXECUTION future de requêtes problématiques connues, par exemple des requêtes nonessentiales provoquant une consommation élevée de ressources et affectant les charges de travail d’application critiques.

Note

L’indicateur de requête ABORT_QUERY_EXECUTION est disponible uniquement dans Azure SQL Database, Azure SQL Managed Instance^AUTD et SQL Server 2025 (17.x).

Par exemple, pour bloquer l’exécution future de query_id 39, exécutez sys.sp_query_store_set_hints comme suit :

EXECUTE sys.sp_query_store_set_hints
    @query_id = 39,
    @query_hints = N'OPTION (USE HINT (''ABORT_QUERY_EXECUTION''))';

Pour débloquer la même requête, exécutez sys.sp_query_store_clear_hints :

EXECUTE sys.sp_query_store_clear_hints @query_id = 39;

Pour plus d’informations, consultez des exemples d'indicateurs du Magasin des requêtes.

Les considérations suivantes s'appliquent :

Lorsque vous spécifiez cet indicateur pour une requête, une tentative d’exécution de la requête échoue avec l’erreur 8778, la gravité 16, l’exécution de la requête a été abandonnée, car l’indicateur de ABORT_QUERY_EXECUTION a été spécifié.
Pour débloquer une requête, vous pouvez effacer l’indicateur en transmettant la query_id valeur au @query_id paramètre dans la procédure stockée sys.sp_query_store_clear_hints .
- Cette procédure stockée efface tous les indicateurs d’une requête. Si vous souhaitez conserver les indicateurs existants lors du déblocage de la requête, utilisez sys.sp_query_store_set_hints, en supprimant l’indicateur ABORT_QUERY_EXECUTION , mais en conservant d’autres indicateurs.

Vous pouvez utiliser des vues système pour rechercher des requêtes dans le Query Store qui sont bloquées, comme dans l’exemple de requête suivant :

SELECT qsh.query_id,
       q.query_hash,
       qt.query_sql_text
FROM sys.query_store_query_hints AS qsh
     INNER JOIN sys.query_store_query AS q
         ON qsh.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS qt
         ON q.query_text_id = qt.query_text_id
WHERE UPPER(qsh.query_hint_text) LIKE '%ABORT[_]QUERY[_]EXECUTION%';

Pour obtenir la valeur query_id, au moins une exécution de requête doit être enregistrée dans le Store des requêtes. Cette exécution n’a pas besoin d’être réussie. Cela signifie que l’exécution future de requêtes expirées ou annulées peut être bloquée.
Si vous devez bloquer ou débloquer toutes les requêtes avec un hachage de requête spécifique, pensez à utiliser un script d'automatisation. Par exemple, dbo.sp_query_store_modify_hints_by_query_hash est un exemple de procédure stockée qui appelle la procédure stockée système sys.sp_query_store_set_hints ou sys.sp_query_store_clear_hints pour toutes les valeurs query_id correspondant à un hachage de requête.
Si une requête est déjà en cours d’exécution lorsque vous la bloquez, son exécution se poursuit. Vous pouvez utiliser l’instruction KILL pour abandonner la requête.
- L’exécution de requêtes arrêtées n’est pas enregistrée dans le Magasin des requêtes. Si la requête n'est pas encore dans le Query Store, vous devez laisser la requête se terminer ou expirer pour obtenir un query_id que vous pouvez utiliser pour bloquer.
Lorsqu'une requête est bloquée par l'indice ABORT_QUERY_EXECUTION, les colonnes execution_type et execution_type_desc de la vue sys.query_store_runtime_stats sont définies respectivement sur 4 et Exception.
Comme avec tous les indicateurs du Magasin des requêtes, vous devez disposer de l’autorisation ALTER sur la base de données pour définir et effacer l’indicateur ABORT_QUERY_EXECUTION .

Considérations relatives aux indicateurs du Magasin des requêtes

Envisagez les scénarios suivants lors du déploiement d’indicateurs du Magasin des requêtes.

Changements de la distribution des données

Les repères de plan, les plans forcés par le biais du Magasin des requêtes et les indicateurs du Magasin des requêtes remplacent la prise de décision de l’optimiseur. L’indicateur du Magasin des requêtes peut être bénéfique maintenant, mais pas à l’avenir. Par exemple, si un indicateur du Magasin des requêtes aide une requête dans la distribution de données précédente, il peut être contre-productif si les opérations DML à grande échelle modifient les données. Une nouvelle distribution de données peut amener l’optimiseur à prendre une meilleure décision que l’indicateur. Ce scénario est la conséquence la plus courante du comportement du forçage de plan.

Réévaluez régulièrement votre stratégie de hints du Query Store

Réévaluez votre stratégie de recommandations du Magasin de requêtes existante dans les cas suivants :

Après des modifications connues de la distribution de données volumineuses.
Lorsque les ressources disponibles pour la base de données changent. Par exemple, lorsque la taille de calcul de votre machine virtuelle Azure SQL Database, SQL Managed Instance ou SQL Server change.
Dans les cas où la résolution des plans est devenue une approche à long terme. Les indicateurs du Magasin des requêtes sont plus efficaces pour les correctifs à court terme.
Régressions de performances inattendues.

Potentiel d’impact large

Les indicateurs du Magasin des requêtes affectent toutes les exécutions de la requête, quel que soit l’ensemble de paramètres, l’application source, l’utilisateur ou le jeu de résultats. Dans le cas d’une régression accidentelle des performances, les indicateurs du Magasin des requêtes créés avec sys.sp_query_store_set_hints peuvent être facilement supprimés avec sys.sp_query_store_clear_hints.

Veillez à effectuer des tests de charge pour les systèmes critiques ou sensibles avant d’appliquer des indicateurs du Magasin des requêtes en production.

La paramétrisation forcée et l’indicateur RECOMPILE ne sont pas pris en charge

L’application du "query hint" RECOMPILE avec les hints du Query Store n’est pas prise en charge lorsque l’option de base de données PARAMETERIZATION est définie sur FORCED. Pour plus d’informations, consultez Principes d’utilisation du paramétrage forcé.

L’indicateur RECOMPILE n’est pas compatible avec le paramétrage forcé défini au niveau de la base de données. Si la base de données utilise le paramétrage forcé et que l’indicateur RECOMPILE fait partie de la chaîne d’indicateurs définie dans le Magasin des requêtes pour une requête, le moteur de base de données ignore l’indicateur RECOMPILE et applique d’autres indicateurs s’il est spécifié. De plus, à compter de juillet 2022 dans Azure SQL Database, un avertissement (code d’erreur 12461) est émis indiquant que l’indicateur RECOMPILE a été ignoré.