Bien démarrer avec l’Assistant Paramétrage de requêtes (QTA)
Vous prévoyez d’effectuer une migration de deux bases de données de production s’exécutant sur SQL Server 2012 vers une nouvelle instance SQL Server 2022. Les bases de données étant en cours d’utilisation, vous souhaitez initialement laisser les niveaux de compatibilité à leurs paramètres d’origine et repointer les applications. L’estimateur de multiplicité a changé depuis SQL Server 2014 et vous souhaitez mesurer les performances des requêtes avant de remplacer le niveau de compatibilité des bases de données par SQL Server 2022 (160).
Vous devez rechercher et corriger toutes les requêtes qui régressent lorsque vous déplacez le niveau de compatibilité et appliquez le nouvel algorithme de l’estimateur de multiplicité. Cette approche mesure une ligne de base pour le niveau de performance à comparer avec les valeurs après la mise à niveau du niveau de compatibilité.
L’activation du Magasin des requêtes rassemble les métriques dont vous avez besoin à la recherche de requêtes en régression et l’utilisation de l’Assistant Paramétrage des requêtes (QTA) vous guide tout au long de la correction des requêtes en régression. Cette leçon fournit une vue d’ensemble du Magasin des requêtes et de l’assistant QTA.
Vue d’ensemble du magasin des requêtes
La fonctionnalité Magasin des requêtes a été introduite dans SQL Server 2016 dans le but de collecter en permanence des informations sur l’exécution et les performances des requêtes dans une base de données. Le Magasin des requêtes fonctionne comme un enregistreur de données de vol afin de recueillir des informations au moment de l’exécution sur les requêtes et les plans. Si vous enregistrez ces données d’exécution, vous pouvez faire le suivi des performances dans le temps. En cas de problème, vous disposez d’un historique d’informations pour identifier la cause du problème.
À compter de SQL Server 2022, dans Azure SQL Database et SQL Managed Instance, le Magasin des requêtes est activé par défaut pour les nouvelles bases de données. Dans SQL Server 2016, SQL Server 2017 et SQL Server 2019, le Magasin des requêtes n’est pas activé par défaut, mais toute base de données sur une instance de SQL Server 2016 ou version ultérieure peut l’activer, le désactiver et le configurer. Consultez la leçon suivante pour obtenir des instructions sur l’activation du Magasin des requêtes ou confirmer sa configuration sur une base de données.
Le Magasin des requêtes peut fonctionner sur des bases de données avec des niveaux de compatibilité antérieurs à l’instance SQL Server. Par exemple, si vous effectuez la migration d’une base de données de SQL Server 2012 vers SQL Server 2022 et laissez le niveau de compatibilité sur 110, le Magasin des requêtes peut toujours fonctionner sur la base de données.
De nombreuses fonctionnalités du traitement intelligent des requêtes, ainsi que d’autres améliorations automatiques des performances, ne sont toutefois activées que pour de plus récents niveaux de compatibilité de base de données. Par conséquent, vous devez essayer de tester le niveau de performance de votre application sur le dernier niveau de compatibilité de la base de données SQL Server. Le Magasin des requêtes et l’assistant QTA peuvent vous aider à effectuer ce test de performance.
Lorsqu’il est activé sur une base de données, le Magasin des requêtes collecte et signale les statistiques suivantes pour vos requêtes :
- Requêtes régressées
- Consommation globale des ressources
- Principales requêtes consommatrices de ressources
- Requêtes avec des plans forcés
- Requêtes avec variation forte
- Statistiques d’attente des requêtes
- Requêtes suivies
Une requête est en régression quand l’optimiseur de requête utilise un nouveau plan de requête qui entraîne une détérioration des performances. La régression peut se produire après des modifications importantes, notamment l’ajout, la suppression ou la modification d’un index, la mise à jour de statistiques ou la modification de la multiplicité des données.
Avant le Magasin des requêtes, SQL Server ne fournissait aucun aperçu et il était difficile pour les développeurs et administrateurs de bases de données d’identifier la cause d’un problème. Vous pouvez à présent utiliser le Magasin des requêtes pour rechercher des requêtes en régression et forcer l’optimisateur à utiliser un plan particulier issu de l’historique.
Il est courant que quelques requêtes sur des milliers consomment l’essentiel des ressources du système. Le Magasin des requêtes identifie les requêtes qui consomment le plus de ressources du fait d’une régression ou d’un paramétrage inadapté. Selon la configuration, vous pouvez filtrer les résultats par durée, processeur, mémoire, E/S ou nombre d’exécutions.
Vous pouvez utiliser le Magasin des requêtes pour surveiller le niveau de performance en cours et pour les tests A/B, afin de comparer le niveau de performance avant et après l’application d’une modification. Par exemple, vous pouvez optimiser le niveau de performance d’une requête en ajoutant un index à une table référencée par une requête pour accélérer la recherche de jointure. La comparaison des statistiques du Magasin des requêtes avant et après l’ajout de l’index vous indique si l’index affecte le niveau de performance. Vous pouvez également comparer les statistiques après l’ajout d’un nouveau matériel ou la mise à jour d’une application.
Vue d’ensemble de l’Assistant Paramétrage des requêtes
L’Assistant Paramétrage de requêtes (QTA) utilise les données du Magasin des requêtes pour trouver les requêtes qui commencent à régresser. L’assistant QTA mène automatiquement des expériences pour trouver une solution pouvant accélérer la requête, avant qu’elle ne sous-performe au point d’affecter les utilisateurs.
Vous pouvez utiliser le Magasin des requêtes et l’assistant QTA pour superviser et optimiser le niveau de performance de la base de données après la mise à niveau. Après la migration d’une base de données vers SQL Server 2016 ou ultérieur, vous conservez le niveau de compatibilité de la base de données et activez le Magasin des requêtes afin de collecter les statistiques de la ligne de base du niveau de performance des requêtes.
Vous modifiez ensuite le niveau de compatibilité et continuez à utiliser les données du Magasin des requêtes pour mesurer les statistiques de performances de vos requêtes. Vous pouvez comparer les statistiques pour déterminer si chaque requête fait mieux, pareil ou pire qu’avant la mise à niveau.
Quand vous modifiez le niveau de compatibilité pour mettre à niveau la base de données, SQL Server modifie la version de l’estimateur de cardinalité utilisée. L’assistant QTA peut trouver de possibles modèles de régression des requêtes en raison de la modification de l’estimateur de multiplicité, ainsi que des expériences visant à améliorer le niveau de performance. Vous pouvez ensuite créer des repères de plan pour les requêtes présentant une amélioration.
Résumé
Au même titre qu’un enregistreur de données de vol capture en continu l’activité d’un avion, le Magasin des requêtes mesure en permanence les statistiques des performances de vos requêtes. Vous pouvez activer le Magasin des requêtes sur n’importe quelle base de données s’exécutant sur SQL Server 2016 ou ultérieure, quel que soit le niveau de compatibilité. Utilisez le Magasin des requêtes pour superviser en permanence le niveau de performance des requêtes et les tests A/B pour mesurer les effets d’une modification.
Quand vous mettez à niveau une base de données vers SQL Server 2014 ou ultérieure, les modifications apportées à l’estimateur de multiplicité peuvent ralentir des requêtes qui s’exécutaient rapidement dans la version précédente de SQL Server. Dans l’idéal, vous souhaitez rechercher et corriger toutes les régressions avant qu’elles n’affectent les utilisateurs. L’activation du Magasin des requêtes sur vos bases de données permet de collecter en continu des statistiques sur les requêtes. Vous pouvez ensuite utiliser l’assistant QTA pour identifier et corriger les requêtes en régression avant qu’elles ne posent un problème.