Paramétrer les requêtes avec l’Assistant Paramétrage de requêtes (QTA)

  • 5 minutes

Vous décidez d’utiliser le Magasin des requêtes pour superviser les performances de la base de données avant la migration et comparer ces données aux performances après la mise à niveau. Vous envisagez d’utiliser l’Assistant Paramétrage de requêtes (QTA) pour trouver les requêtes régressées et suggérer des améliorations. Cette unité décrit les étapes permettant d’utiliser le Magasin des requêtes et QTA pour conserver le même niveau de performance des requêtes.

Étant donné que vos applications de base de données sont des systèmes critiques qui sous-tendent les opérations de l’entreprise, vous devez disposer d’un plan d’action et d’un plan d’urgence pour la mise à niveau. Mettez à niveau les bases de données de production principales une par une. Déterminez un plan de test pour les bases de données mises à niveau afin d’être certain que chaque base de données est de retour en production sans aucun problème avant de mettre à niveau d’autres bases de données.

Vous pouvez utiliser le Magasin des requêtes pour superviser en permanence les performances des requêtes, et pour les tests A/B afin de mesurer les effets d’une modification telle qu’une mise à niveau de base de données. Après une mise à niveau, QTA permet de rechercher et de résoudre automatiquement les requêtes ayant régressé en fonction des données capturées dans le Magasin des requêtes.

Pour que QTA fonctionne correctement, vous devez effectuer les étapes suivantes dans l’ordre :

Important

Veillez à utiliser votre base de données d’application de production, ou une base de données avec une charge de travail d’application qui correspond étroitement à votre charge de travail de base de données de production, afin que le Magasin des requêtes puisse collecter des métriques réalistes sur les requêtes.

  1. Migrer la base de données vers SQL Server 2022.
  2. Laisser le niveau de compatibilité inchangé au niveau de la version précédente de SQL Server.
  3. Activer le Magasin des requêtes sur la base de données.
  4. Laisser le Magasin des requêtes collecter des métriques de base de référence sur les requêtes en fonction d’une activité utilisateur réaliste suffisante.
  5. Mettre à niveau le niveau de compatibilité vers SQL Server 2022 (160).
  6. Laisser à nouveau le Magasin des requêtes collecter des données sur les requêtes en fonction d’une activité utilisateur réaliste suffisante.
  7. Utiliser l’assistant QTA pour comparer les performances des requêtes avant et après la modification du niveau de compatibilité de la base de données. Si des requêtes régressées sont trouvées, identifier les correctifs.

Migrer la base de données

Quand vous êtes prêt à passer à SQL Server 2022, commencez par migrer votre base de données vers la nouvelle instance. Il existe plusieurs manières d’effectuer cette migration. Vous pouvez utiliser par exemple une sauvegarde et une restauration simples, la mise en miroir de bases de données ou un chargement en masse. Le choix le plus approprié dépend de la configuration de votre environnement actuel et de la version de SQL Server à partir de laquelle vous effectuez la migration. Azure Data Migration Service (DMS) est une bonne solution, car elle prend en charge les bases de données SQL Server version 2005 et ultérieures.

Remarque

Azure DMS prend également en charge les migrations de base de données vers Azure SQL Managed Instance. Pour commencer, utilisez l’extension de migration Azure SQL pour Azure Data Studio.

Laisser le niveau de compatibilité inchangé

Après avoir migré la base de données, laissez le niveau de compatibilité inchangé. Cette étape est essentielle, car vous souhaitez mesurer la base de référence à partir de la configuration actuelle de la base de données. Tant que le niveau de compatibilité à SQL est antérieur à SQL Server 2014 (120), SQL Server utilise l’estimateur de cardinalité hérité. SQL Server 2014 a introduit un estimateur de cardinalité mis à niveau qui profite à la plupart des requêtes, mais qui peut rarement avoir un impact négatif sur les performances.

Activer le magasin des requêtes

Bien que le niveau de compatibilité de base de données soit celui de la version précédente, vous pouvez activer le Magasin des requêtes sur la base de données, car il s’agit d’une fonctionnalité au niveau du serveur. Pour activer le Magasin des requêtes :

  1. Dans SQL Server Management Studio (SSMS), cliquez avec le bouton droit sur la base de données et sélectionnez Propriétés.
  2. Dans la fenêtre Propriétés de la base de données, sélectionnez Magasin des requêtes dans le volet gauche.
  3. Définissez Mode d’opération (demandé) sur Lecture seule ou Lecture-écriture.
  4. Cliquez sur OK.

Vous pouvez également exécuter l’instruction suivante pour activer le Magasin des requêtes en mode READ WRITE par défaut :

ALTER DATABASE <database-name> SET QUERY_STORE = ON

Laisser le Magasin des requêtes collecter des données

Remettez votre base de données en production et changez toutes les connexions de base de données provenant d’applications ou de rapports. La base de données commence à recevoir des requêtes à partir d’applications de production. Laissez le Magasin des requêtes s’exécuter suffisamment longtemps pour collecter une charge de travail réaliste sur la base de données.

Le Magasin des requêtes doit capturer un cycle typique d’activité commerciale, y compris les heures d’ouverture, le traitement nocturne, les fenêtres de maintenance et autres activités. Pour de nombreuses entreprises, l’activité d’une semaine est suffisante, mais pour certaines entreprises cette période peut être plus courte ou plus longue.

De nombreuses entreprises ont des cycles d’activité majeurs et, par conséquent, une activité unique, pour la paie bimensuelle ou le traitement de fin de mois. Vous devez connaître le calendrier des cycles d’activité observé par votre base de données. Pour une épicerie, les cycles hebdomadaires d’arrivée de stock et de réapprovisionnement couvrent la plupart des activités de base de données.

Vous pouvez voir les données collectées en parcourant les onglets du Magasin des requêtes. Pour afficher les onglets, sans SSMS, dans l’Explorateur d’objets, développez l’arborescence de la base de données pour afficher le Magasin des requêtes. Une fois satisfait que la quantité de données collectées est suffisante, vous pouvez planifier la mise à niveau.

Mettre à niveau le niveau de compatibilité

Avant de modifier une base de données, nous vous recommandons de la sauvegarder, en dehors des heures de travail si possible. Une fois la sauvegarde effectuée, mettez à niveau le niveau de compatibilité comme suit :

  1. Cliquez avec le bouton droit sur la base de données dans l’Explorateur d’objets de SSMS, puis choisissez Propriétés.
  2. Dans la fenêtre Propriétés de la base de données, sélectionnez l’onglet Options.
  3. Remplacez le niveau de compatibilité par SQL Server 2022 (160) et sélectionnez OK.

Vous pouvez également exécuter l’instruction suivante :

ALTER DATABASE <database-name> SET COMPATIBILITY_LEVEL = 160

Laisser le Magasin des requêtes continuer à collecter des données

Une fois que votre base de données a été mise à niveau et que les applications reprennent, le Magasin des requêtes continue à s’exécuter en arrière-plan afin de collecter des métriques pour les requêtes. Les requêtes sont désormais exposées à des problèmes potentiels en raison du nouvel estimateur de cardinalité utilisé par l’optimiseur de requête.

Continuez à exécuter le Magasin des requêtes, et autorisez-le à collecter des données pendant la même durée qu’avant la mise à niveau. Toutefois, la régression de requête peut s’observer immédiatement ; ainsi, vous pouvez prendre tout de suite des mesures pour corriger les problèmes de performances.

Exécuter l’Assistant Paramétrage de requêtes

Exécutez l’Assistant QTA pour traiter les requêtes en régression. Pour configurer l’Assistant QTA :

  1. Cliquez avec le bouton droit sur la base de données dans l’Explorateur d’objets de SSMS et sélectionnez Tâches>Mise à niveau de la base de données>Nouvelle session de mise à niveau de base de données.
  2. Dans l’écran Installation de l’Assistant Paramétrage de requêtes, entrez la Durée de capture de la charge de travail (en jours) et le Niveau de compatibilité de la base de données cible.
  3. Sélectionnez Suivant pour configurer les écrans Paramètres et Paramétrage.
  4. Sélectionnez Terminer.

Pour superviser l’Assistant QTA, cliquez avec le bouton droit sur le nom de la base de données, puis sélectionnez Tâches>Mise à niveau de la base de données>Superviser les sessions. QTA compare les données observées aux données de référence et vous fournit un rapport de synthèse indiquant les requêtes ayant le plus régressé. Vous pouvez ensuite afficher les modifications que QTA vous recommande d’apporter pour paramétrer les requêtes en cas de détérioration des performances.

Résumé

Utilisez QTA après la mise à niveau de votre base de données pour rechercher et corriger les requêtes en régression suite à la mise à niveau. Pour que QTA trouve les requêtes ayant régressé, vous devez d’abord créer une ligne de base à l’aide du Magasin des requêtes afin de mesurer les requêtes par rapport à l’ancien niveau de compatibilité.

Le Magasin des requêtes collecte ensuite les métriques après la mise à niveau, que vous pouvez utiliser avec QTA pour comparer les nouvelles performances à la base de référence. Pour que QTA fonctionne, il est essentiel que le Magasin des requêtes collecte des données avant et après la mise à niveau.

Quand QTA détecte des requêtes ayant régressé, il mène des expériences afin de trouver les meilleures actions susceptibles d’améliorer les performances. Vous pouvez ensuite appliquer ces actions.