Bonnes pratiques relatives aux indicateurs du Magasin des requêtes

Article
05/30/2023

S’applique à : SQL Server 2022 (16.x) base de données Azure SQL Azure SQL Managed Instance

Cet article détaille les meilleures 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 Supervision du niveau de performance avec le Magasin des requêtes.
Pour plus d’informations sur la découverte d’informations actionnables et le réglage des performances avec le Magasin des requêtes, consultez Réglage des performances 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.

Attention

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 les indicateurs du Magasin des requêtes, par exemple, pour bénéficier des fonctionnalités ETL 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 pensez régulièrement qu’il est nécessaire d’appliquer des indicateurs du Magasin des requêtes à une requête, envisagez une réécriture plus globale 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 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 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 des 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 quand un niveau de compatibilité de la base de données plus récent n’est pas disponible en raison de la spécification du fournisseur ou de 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 requête individuelle pour tirer partie 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 des requêtes pour exécuter des requêtes individuelles au niveau de compatibilité 160. Vous pouvez utiliser l’indicateur suivant :

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

Pour obtenir un didacticiel complet, consultez Exemples d’indicateurs du Magasin des 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 qu’elle puisse être réécrite ou traitée pour s’exécuter correctement dans le dernier niveau de compatibilité. Identifiez les requêtes hors norme qui ont régressé dans un niveau de compatibilité supérieur à l’aide du rapport de requêtes régressées du Magasin des requêtes, à l’aide de l’outil Assistant Paramétrage des requêtes pendant une migration ou d’autres données de télémétrie d’application au niveau de la requête. 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 de cette façon, vous pouvez mettre à niveau le niveau de compatibilité de la base de données tout en conservant les requêtes problématiques clés au niveau de compatibilité antérieur, sans aucune modification du code.

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, elle peut être contre-productive si des opérations DML à grande échelle changent les données. Une nouvelle distribution des 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éévaluer régulièrement votre stratégie d’indicateurs du Magasin des requêtes

Réévaluez votre stratégie d’indicateurs du Magasin des requêtes existante dans les cas suivants :

Après des modifications connues de la distribution de données volumineuses.
Quand l’objectif de niveau de service (SLO) de votre base de données Azure SQL Database, instance Managed Instance ou machine virtuelle a changé.
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 affecte 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 de l’indicateur de requête RECOMPILE avec les indicateurs du Magasin des requêtes n’est pas prise en charge quand 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 la paramétrisation forcée définie au niveau de la base de données. Si la paramétrisation forcée est définie pour la base de données 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 SQL Server ignore l’indicateur RECOMPILE et applique les autres indicateurs s’ils sont exploités. De plus, à compter de juillet 2022 dans la base de données Azure SQL, un avertissement (code d’erreur 12461) doit être émis indiquant que l’indicateur RECOMPILE a été ignoré.