Mettre à niveau des bases de données en utilisant l’Assistant Paramétrage de requêtes

S’applique à : SQL Server 2016 (13.x) et versions ultérieures

Lors de la migration d’une version antérieure de SQL Server vers SQL Server 2014 (12.x) ou une version ultérieure et la mise à niveau du niveau de compatibilité de la base de données vers la dernière version disponible, une charge de travail peut être exposée au risque de régression des performances. Cela est également possible (à un degré moindre) lors de la mise à niveau entre SQL Server 2014 (12.x) et les versions plus récentes.

Dans SQL Server 2014 (12.x) et versions ultérieures, toutes les modifications de l’optimiseur de requête sont limitées au dernier niveau de compatibilité de la base de données. Les plans d’exécution ne sont donc pas modifiés directement au moment de la mise à niveau, mais plutôt lorsqu’un utilisateur modifie l’option de base de données vers COMPATIBILITY_LEVEL la dernière version disponible. Pour plus d’informations sur les modifications apportées à l’optimiseur de requête introduites dans SQL Server 2014 (12.x), consultez Estimation de la cardinalité (SQL Server). Pour plus d’informations sur les niveaux de compatibilité et leur impact sur les mises à niveau, consultez Niveaux de compatibilité et mises à niveau du moteur de base de données.

Cette fonctionnalité de regroupement fournie par le niveau de compatibilité de la base de données, en combinaison avec le Magasin des requêtes, vous offre un excellent niveau de contrôle sur les performances des requêtes dans le processus de mise à niveau si la mise à niveau suit le flux de travail recommandé affiché dans le diagramme suivant. Pour plus d’informations sur le flux de travail recommandé pour la mise à niveau du niveau de compatibilité, consultez Modifier le niveau de compatibilité de la base de données et utiliser le Magasin des requêtes.

Ce contrôle sur les mises à niveau a été amélioré avec SQL Server 2017 (14.x) où le réglage automatique a été introduit et permet d’automatiser la dernière étape du flux de travail recommandé.

À compter de SQL Server Management Studio v18, la fonctionnalité Assistant Paramétrage des requêtes (QTA) guide les utilisateurs via le flux de travail recommandé pour maintenir la stabilité des performances pendant les mises à niveau vers des versions plus récentes de SQL Server, comme indiqué dans la section Conserver la stabilité des performances pendant la mise à niveau vers les scénariosd’utilisation du magasin des requêtes plus récents. Cependant, l’Assistant Paramétrage de requêtes ne revient pas à un bon plan antérieur comme dans la dernière étape du workflow recommandé. Au lieu de cela, QTA effectue le suivi de toutes les régressions trouvées dans la vue Requêtes régressées du magasin de requêtes et explore les permutations possibles des modèles d'optimisation applicables afin qu’un nouveau plan plus efficace puisse être produit.

Important

L’Assistant Paramétrage de requêtes ne génère pas de charge de travail utilisateur. Si vous exécutez l’Assistant Paramétrage de requêtes dans un environnement qui n’est pas utilisé par vos applications, vérifiez que vous pouvez néanmoins exécuter des charges de travail de test représentatives sur le moteur de base de données SQL Server ciblé par d’autres moyens.

Workflow de l’Assistant Paramétrage de requêtes

Le point de départ de QTA suppose qu’une base de données d’une version précédente de SQL Server est déplacée (via Attacher une base de données ou des instructions RESTORE) à une version plus récente du moteur de base de données SQL Server, et que le niveau de compatibilité avant la mise à niveau de la base de données n’est pas modifié immédiatement. QTA guide les étapes suivantes :

Configurer le Magasin des requêtes conformément aux paramètres recommandés pour la durée de la charge de travail (en jours) définie par l’utilisateur Réfléchir à la durée de la charge de travail qui correspond à votre cycle d’entreprise classique
Demander le démarrage de la charge de travail requise, afin que le Magasin des requêtes puisse collecter une base de référence de données de charge de travail (si aucune n’est encore disponible)
Procéder à la mise à niveau vers le niveau de compatibilité de base de données cible choisi par l’utilisateur
Demander à ce qu’une deuxième série de données de charge de travail soit collectée à des fins de comparaison et de détection de régression
Itérer les régressions détectées d’après la vue Magasin des requêtes Requêtes régressées, effectuer des expérimentations en recueillant des statistiques d’exécution concernant les permutations possibles des variations de modèle d’optimiseur applicables, et mesurer le résultat
Obtenir des rapports sur les améliorations mesurées, et éventuellement autoriser la persistance de ces modifications à l’aide de repères de plan

Pour plus d’informations sur l’attachement d’une base de données, consultez Attacher et détacher une base de données.

Le diagramme suivant montre comment QTA modifie uniquement les dernières étapes du flux de travail recommandé pour mettre à niveau le niveau de compatibilité à l’aide du Magasin des requêtes vu précédemment. Au lieu de choisir entre le plan d’exécution actuellement inefficace et le dernier plan d’exécution correct connu, QTA présente les options de réglage spécifiques aux requêtes régressées sélectionnées, afin de créer un état amélioré avec des plans d’exécution paramétrés.

Espace de recherche interne de paramétrage de l’Assistant Paramétrage de requêtes

L’Assistant Paramétrage de requêtes cible uniquement les requêtes SELECT qui peuvent être exécutés à partir du Magasin des requêtes. Les requêtes paramétrables sont éligibles si le paramètre compilé est connu. Les requêtes qui dépendent des constructions au moment de l’exécution, telles que les tables temporaires ou les variables de table, ne sont pas éligibles à l’heure actuelle.

QTA cible les modèles possibles connus des régressions de requête en raison des modifications apportées aux versions d’estimation de cardinalité (SQL Server). Par exemple, lors de la mise à niveau d’une base de données à partir de SQL Server 2012 (11.x) et du niveau de compatibilité de base de données 110, vers SQL Server 2017 (14.x) et le niveau de compatibilité de base de données 140, certaines requêtes peuvent régresser, car elles ont été conçues spécifiquement pour fonctionner avec la version CE qui existait dans SQL Server 2012 (11.x) (CE 70). Cela ne signifie pas que repasser de l’estimateur de cardinalité 140 à l’estimateur de cardinalité 70 est l’unique option. Si seule une modification spécifique dans la version plus récente introduit la régression, il est possible d’indiquer que cette requête utilise uniquement la partie pertinente de la version CE précédente qui fonctionnait mieux pour la requête spécifique, tout en utilisant toutes les autres améliorations des versions PLUS récentes de CE. Vous pouvez aussi faire en sorte que d’autres requêtes dans la charge de travail qui n’ont pas subi de régression tirent parti des améliorations du nouvel estimateur de cardinalité.

Les modèles CE recherchés par QTA sont les suivants :

Indépendance et corrélation : si l’hypothèse d’indépendance fournit de meilleures estimations pour la requête spécifique, l’indicateur USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') de requête entraîne la génération d’un plan d’exécution par SQL Server à l’aide d’une sélectivité minimale lors de l’estimation des AND prédicats pour les filtres pour tenir compte de la corrélation. Pour plus d’informations, consultez Indicateurs de requête USE HINT et Versions de l’estimateur de cardinalité.
Confinement simple vs. Confinement de base : Si une autre jointure offre de meilleures estimations pour la requête spécifique, l’indicateur USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') de requête amène SQL Server à générer un plan d’exécution en utilisant l’hypothèse de confinement simple au lieu de l’hypothèse de confinement de base par défaut. Pour plus d’informations, consultez Indicateurs de requête USE HINT et Versions de l’estimateur de cardinalité.
Estimation de cardinalité fixe des fonctions table à instructions multiples (MSTVF) de 100 lignes ou 1 ligne : si l’estimation fixe par défaut pour les TVF de 100 lignes ne donne pas un plan plus efficace que l’utilisation de l’estimation fixe d’1 ligne pour les TVF (correspondant à la valeur par défaut sous le modèle d’estimateur de cardinalité de l’optimiseur de requête de SQL Server 2008 R2 (10.50.x) et versions antérieures), l’indicateur de requête QUERYTRACEON 9488 est utilisé pour générer un plan d’exécution. Pour plus d’informations sur les MSTVF, consultez Créer des fonctions définies par l’utilisateur (moteur de base de données).

En dernier recours, si les indicateurs à étendue étroite ne génèrent pas de résultats suffisamment bons pour les modèles de requête éligibles, l’utilisation complète de l’estimateur de cardinalité 70 est également considérée, en utilisant l’indicateur de requête USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') pour générer un plan d’exécution.

Important

Tout indicateur force certains comportements susceptibles d’être traités dans les futures mises à jour DE SQL Server. Nous vous recommandons d’appliquer des indicateurs uniquement quand il n’existe aucune autre option, et de revoir le code avec indicateur lors de chaque nouvelle mise à niveau. En forçant des comportements, vous pouvez empêcher votre charge de travail de bénéficier d’améliorations introduites dans les versions plus récentes de SQL Server.

Démarrer l’Assistant Paramétrage de requêtes pour les mises à niveau de base de données

L’Assistant Paramétrage de requêtes est une fonctionnalité basée sur la session qui stocke l’état de session dans le schéma msqta de la base de données utilisateur où une session est créée pour la première fois. Vous pouvez créer plusieurs sessions de paramétrage sur une même base de données au fil du temps, mais il ne peut exister qu’une seule session active pour chaque base de données.

Créer une session de mise à niveau de base de données

Dans SQL Server Management Studio, ouvrez l’Explorateur d’objets et connectez-vous au moteur de base de données.
Cliquez avec le bouton droit sur le nom de la base de données dont vous prévoyez de mettre à niveau le niveau de compatibilité, sélectionnez Tâches, Mise à niveau de la base de données, puis sélectionnez Nouvelle session de mise à niveau de base de données.
Dans la fenêtre de l’Assistant Paramétrage de requêtes, les deux étapes sont requises pour configurer une session :
1. Dans la fenêtre Configuration, configurez le Magasin des requêtes afin de capturer l’équivalent d’un cycle complet de données de charge de travail à analyser et à paramétrer.
  - Entrez la durée attendue de la charge de travail en jours (le minimum est 1). Il est utilisé pour proposer les paramètres recommandés du Store des requêtes afin de permettre de manière provisoire la collecte de l'ensemble de la ligne de base. La capture d’une bonne base de référence est importante pour s’assurer que toutes les requêtes régressées détectées après le changement du niveau de compatibilité de base de données puissent être analysées.
  - Définissez le niveau de compatibilité de base de données cible auquel doit être la base de données utilisateur une fois le workflow de l’Assistant Paramétrage de requêtes terminé.
  Quand vous avez terminé, sélectionnez Suivant.
2. Dans la fenêtre Paramètres , deux colonnes affichent l’état actuel du Magasin des requêtes dans la base de données ciblée et les paramètres recommandés .
  - Les paramètres recommandés sont sélectionnés par défaut, mais sélectionner la case d’option présente sur la colonne Actuel permet d’accepter les paramètres actuels et également d’affiner la configuration actuelle du Magasin des requêtes.
  - Le paramètre Seuil de requête obsolète proposé est deux fois la valeur de durée de charge de travail attendue, en jours. Cela est dû au fait que le Magasin des requêtes doit contenir des informations sur la charge de travail de base de référence et la charge de travail post-mise à niveau de la base de données.
  Quand vous avez terminé, sélectionnez Suivant.
  
  Important
  
  La taille maximale proposée est une valeur arbitraire qui peut être adaptée à une charge de travail à court terme. Toutefois, il peut être insuffisant de contenir des informations sur les charges de travail de base de référence et de mise à niveau après la base de données pour les charges de travail intensives, à savoir lorsque de nombreux plans différents peuvent être générés. Si vous prévoyez que ce sera le cas, entrez une valeur supérieure appropriée.
La fenêtre Réglage conclut la configuration de session et indique les étapes suivantes à effectuer pour ouvrir et poursuivre la session. Lorsque vous avez terminé, sélectionnez Terminer.

Exécuter le workflow de mise à niveau de base de données

Cliquez avec le bouton droit sur le nom de la base de données dont vous prévoyez de mettre à niveau le niveau de compatibilité, sélectionnez Tâches, Mise à niveau de la base de données, puis sélectionnez Superviser les sessions.
La page Gestion des sessions liste les sessions actives et passées pour la base de données dans la portée. Sélectionnez la session souhaitée, puis sélectionnez Détails.

Remarque

Si la session active n’est pas présente, sélectionnez le bouton Actualiser.

La liste contient les informations suivantes :
- ID session
- Nom de la session : nom généré par le système, composé du nom de la base de données, de la date et de l’heure de création de la session.
- État : état de la session (active ou fermée).
- Description : générée par le système, elle comprend le niveau de compatibilité de base de données cible sélectionné par l’utilisateur et le nombre de jours pour la charge de travail du cycle des opérations.
- Heure de début : date et heure de création de la session.
Remarque

L’option Supprimer la session permet de supprimer toutes les données stockées pour la session sélectionnée. Cependant, la suppression d’une session fermée ne supprime pas les repères de plan déployés précédemment. Si vous supprimez une session qui a déployé des repères de plan, vous ne pouvez pas utiliser QTA pour restaurer. Au lieu de cela, recherchez les repères de plan à l’aide de la table système sys.plan_guides et supprimez-les manuellement à l’aide de sp_control_plan_guide.
Le point d’entrée pour une nouvelle session est l’étape Collecte des données.

Remarque

Le bouton Sessions permet de revenir à la page Gestion des sessions en laissant la session active telle quelle.

Cette étape comporte trois sous-étapes :
1. La Collecte des données de référence invite l’utilisateur à exécuter le cycle de charge de travail représentatif, afin que le Magasin des requêtes puisse collecter une base de référence. Une fois cette charge de travail terminée, vérifiez Terminé avec l'exécution de la charge de travail, puis sélectionnez Suivant.
  
  Remarque
  
  La fenêtre de l’Assistant Paramétrage de requêtes peut être fermée pendant l’exécution de la charge de travail. Revenir ultérieurement à la session qui reste en état actif permet de reprendre à partir de la même étape où elle a été laissée.
2. Mettre à niveau la base de données demande l’autorisation de mettre à niveau le niveau de compatibilité de la base de données vers la cible souhaitée. Pour continuer à l’étape suivante, sélectionnez Oui.
  
  La page suivante confirme que le niveau de compatibilité de base de données a été correctement mis à niveau.
3. La collecte de données observée demande à l’utilisateur d’exécuter à nouveau le cycle de charge de travail représentatif afin que le Magasin des requêtes puisse collecter une base de référence comparative utilisée pour rechercher des opportunités d’optimisation. Pendant l’exécution de la charge de travail, utilisez le bouton Actualiser pour continuer à mettre à jour la liste des requêtes régressées, si certaines ont été détectées. Modifier la valeur de Requêtes à afficher pour limiter le nombre de requêtes affichées. L’ordre de la liste est affecté par la Métrique (durée ou temps processeur) et l’Agrégation (Moyenne est la valeur par défaut). Sélectionnez également le nombre de Requêtes à afficher. Une fois cette charge de travail terminée, cochez Terminé avec l'exécution de la charge de travail et sélectionnez Suivant.
  
  La liste contient les informations suivantes :
  - ID de requête
  - Texte de la requête : instruction Text: Transact-SQL que vous pouvez développer en sélectionnant le bouton ....
  - Exécutions : affiche le nombre d’exécutions de cette requête pour toute la collection de charges de travail.
  - Métrique de référence : métrique sélectionnée (durée ou temps processeur), en ms, pour la collecte de données de référence avant la mise à niveau de la compatibilité de base de données.
  - Métrique observée : métrique sélectionnée (durée ou temps processeur), en ms, pour la collecte de données après la mise à niveau de la compatibilité de base de données.
  - % de changement : pourcentage de changement pour la métrique sélectionnée, entre les états antérieur et postérieur à la mise à niveau de la compatibilité de base de données. Un nombre négatif représente la quantité de régression mesurée pour la requête.
  - Paramétrable : True ou False selon que la requête est éligible pour l’expérimentation.
Afficher l’analyse permet de sélectionner les requêtes sur lesquelles expérimenter et trouver des opportunités d’optimisation. La valeur de Requêtes à afficher devient l’étendue des requêtes éligibles sur lesquelles expérimenter. Une fois que vous avez coché les requêtes souhaitées, sélectionnez Suivant pour démarrer l’expérimentation.

Les requêtes dont le paramètre Tunable est réglé sur False ne peuvent pas être sélectionnées pour l'expérimentation.

Important

Une invite indique qu’une fois que QTA passe à la phase d’expérimentation, le retour à la page d'analyse de la vue n’est pas possible. Si vous ne sélectionnez pas toutes les requêtes éligibles avant de passer à la phase d’expérimentation, vous devez créer ultérieurement une nouvelle session et répéter le workflow. Cela nécessite la réinitialisation du niveau de compatibilité de base de données à la valeur précédente.
Afficher les résultats vous permet de sélectionner les requêtes pour lesquelles déployer l’optimisation proposée en tant que repère de plan.

La liste contient les informations suivantes :
- ID de requête
- Texte de la requête : instruction Text: Transact-SQL que vous pouvez développer en sélectionnant le bouton ....
- État : affiche l’état actuel de l’expérimentation pour la requête.
- Métrique de référence : métrique sélectionnée (durée ou temps processeur), en ms, pour la requête telle qu’exécutée à l’Étape 2 Sous-étape 3, représentant la requête régressée après la mise à niveau de la compatibilité de base de données.
- Métrique observée: métrique sélectionnée (durée ou temps processeur), en ms, pour la requête après l’expérimentation, pour une optimisation proposée suffisamment bonne.
- % Modification : spécifie la modification du pourcentage de la métrique sélectionnée entre l’état avant et après l’expérimentation, représentant la quantité d’amélioration mesurée pour la requête avec l’optimisation proposée.
- Option de requête : lien vers l’indicateur proposé qui améliore la métrique d’exécution de requête.
- Peut déployer : True ou False selon que l’optimisation de requête proposée peut être déployée ou non en tant que repère de plan.
Vérification affiche l’état du déploiement des requêtes sélectionnées précédemment pour cette session. La liste dans cette page diffère de la page précédente, dans le sens où Peut restaurer remplace la colonne Peut déployer. Cette colonne peut être True ou False selon que l’optimisation de requête déployée peut être annulée et son repère de plan supprimé.

Si à une date ultérieure, il est nécessaire de restaurer une optimisation proposée, puis de sélectionner la requête appropriée et de sélectionner Restauration. Ce repère de plan de requête est supprimé et la liste est mise à jour de façon à supprimer la requête restaurée. Dans l’image ci-dessous, notez que la requête 8 a été supprimée.

Remarque

La suppression d’une session fermée ne supprime pas les repères de plan déployés précédemment. Si vous supprimez une session qui a déployé des repères de plan, vous ne pouvez pas utiliser QTA pour restaurer. Au lieu de cela, recherchez les repères de plan à l’aide de la table système sys.plan_guides et supprimez-les manuellement à l’aide de sp_control_plan_guide.

autorisations

Nécessite l’appartenance au rôle db_owner.

Rétroaction

Cette page vous a-t-elle été utile ?

Last updated on 2025-10-10