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

Article
05/23/2023

S’applique à : SQL Server 2016 (13.x) et versions ultérieures d’Azure SQL DatabaseAzure Synapse Analytics Analytics Platform System (PDW)

Quand vous migrez d’une ancienne version de SQL Server vers SQL Server 2014 (12.x) ou version ultérieure et que vous passez au tout dernier niveau de compatibilité de la base de données, il est possible que les performances d’une charge de travail fassent l’objet d’une régression. Cela est également possible dans une moindre mesure lors de la mise à niveau entre SQL Server 2014 (12.x) et toute version plus récente.

À compter de SQL Server 2014 (12.x) et avec chaque nouvelle version, toutes les modifications de l’optimiseur de requête sont contrôlées vers le dernier niveau de compatibilité de la base de données, de sorte que les plans d’exécution ne sont pas modifiés au moment de la mise à niveau, mais plutôt quand un utilisateur modifie l’option COMPATIBILITY_LEVEL de base de données vers 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 l’estimateur de cardinalité. 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 liaison fournie par le niveau de compatibilité de base de données, en association avec le Magasin des requêtes, procure un niveau élevé de contrôle sur les performances des requêtes dans le processus de mise à niveau si celle-ci respecte le flux de travail recommandé ci-dessous. Pour plus d’informations sur le flux de travail recommandé pour la mise à niveau du niveau de compatibilité, consultez Modifier le mode de compatibilité de base de données et utiliser le magasin des requêtes.

Recommended database upgrade workflow using Query Store

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é ci-dessus.

À compter de SQL Server Management Studio v18, la nouvelle fonctionnalité d’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 un serveur SQL Server plus récent de Magasin des requêtes Scénarios d’utilisation. 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é. Il repère les régressions trouvées dans l’affichage Requêtes en régression Magasin des requêtes et parcourt les permutations possibles des variations du modèle d’optimiseur applicable dans le but de produire un meilleur plan.

Important

L’Assistant Paramétrage de requêtes ne génère pas de charge de travail utilisateur. Si vous exécutez QTA dans un environnement qui n’est pas utilisé par vos applications, vérifiez que vous pouvez toujours exécuter une charge de travail de test représentative sur le serveur SQL Server ciblé Moteur de base de données 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 CREATE DATABASE ... FOR ATTACH ou RESTORE) vers une version plus récente du Moteur de base de données SQL Server, et le niveau de compatibilité de la base de données avant la mise à niveau n’est pas modifié immédiatement. L’Assistant Paramétrage de requêtes vous aide à effectuer 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.

Voir ci-dessous comment l’Assistant Paramétrage de requêtes ne fait que changer les dernières étapes du workflow recommandé pour la mise à niveau du niveau de compatibilité à l’aide du Magasin des requêtes mentionné ci-dessus. Au lieu d’offrir la possibilité de choisir entre le plan d’exécution inefficace actuel et le dernier bon plan d’exécution connu, l’Assistant Paramétrage de requêtes présente des options de paramétrage propres aux requêtes en régression sélectionnées, afin de créer un nouvel état amélioré avec des plans d’exécution paramétrés.

Recommended database upgrade workflow using QTA

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.

L’Assistant Paramétrage de requêtes cible les modèles possibles connus de régressions de requêtes dues aux changements de version de l’estimateur de cardinalité. Par exemple, lors de la mise à niveau d’une base de données 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. Et autorisez également d’autres requêtes dans la charge de travail qui n’ont pas régressé pour bénéficier d’améliorations plus récentes de CE.

Les modèles d’estimateur de cardinalité recherchés par l’Assistant Paramétrage de requêtes 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é.
Contenant-contenu simple et conteneur de base : si un conteneur de jointure différent fournit de meilleures estimations pour la requête spécifique, l’indicateur USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') de requête entraîne la génération d’un plan d’exécution par SQL Server à l’aide de l’hypothèse de l’endiguement simple au lieu de l’hypothèse d’endiguement de base par défaut. Pour plus d’informations, consultez Indicateurs de requête USE HINT et Versions de l’estimateur de cardinalité.
Fonction table à instructions multiples (MSTVF) fixe carte inalité de estimation de 100 lignes par rapport à 1 ligne : si l’estimation fixe par défaut de 100 lignes de 100 lignes n’entraîne pas un plan plus efficace que l’utilisation de l’estimation fixe de 1 ligne (correspondant au modèle CE de l’optimiseur de requête par défaut de SQL Server 2008 R2 (10.50.x) et versions antérieures), l’indicateur QUERYTRACEON 9488 de requête est ensuite 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).

Remarque

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 qui peuvent être traités dans les futures mises à jour 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 des 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 à Moteur de base de données.
Pour la base de données destinée à mettre à niveau le niveau de compatibilité de la base de données, cliquez avec le bouton droit sur le nom de la base de données, sélectionnez Tâches, mise à niveau de la base de données, puis nouvelle session de mise à niveau de la 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). Cette valeur servira à proposer des paramètres recommandés dans le Magasin des requêtes pour autoriser provisoirement la collecte de la base de référence entière. 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 indiquent l’état Actuel du Magasin des requêtes dans la base de données ciblée ainsi que 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 le nombre de durées de charge de travail attendues, en jours. En effet, le Magasin des requêtes devra contenir des informations sur la charge de travail de base de référence et la charge de travail de post-mise à niveau de base de données. Quand vous avez terminé, sélectionnez Suivant.
  Important
  
  La proposition Taille maximale est une valeur arbitraire qui peut convenir à une charge de travail de courte durée. Toutefois, gardez à l’esprit qu’elle risque d’être insuffisante pour contenir des informations sur les charges de travail de base de référence et de post-mise à niveau de base de données très intensives, c’est-à-dire quand de nombreux plans différents peuvent être générés. Si vous prévoyez que ce sera le cas, entrez une valeur plus élevée qui convient.
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 la base de données

Pour la base de données destinée à mettre à niveau le niveau de compatibilité de la base de données, cliquez avec le bouton droit sur le nom de la base de données, sélectionnez Tâches, mise à niveau de la base de données, puis surveillez 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 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é par le système composé du niveau de compatibilité de la base de données cible sélectionné par l’utilisateur et du nombre de jours pour la charge de travail du cycle d’activité.
- 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 avait des repères de plan déployé, vous ne pouvez pas utiliser l’Assistant Paramétrage de requêtes pour revenir en arrière. 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, cochez la case Exécution terminée de la charge de travail et 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. Si vous revenez ultérieurement à la session qui reste dans un état actif, vous reprendrez à partir de l’étape où vous vous êtes arrêté.
2. Mettre à niveau la base de données vous invitera à autoriser la mise à niveau du niveau de compatibilité de base de données vers le niveau cible souhaité. 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 des données observées invite l’utilisateur à réexécuter le cycle de la charge de travail représentative, afin que le Magasin des requêtes puisse collecter une base de référence comparative qui servira à rechercher des pistes 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 la case Exécution terminée de la charge de travail et sélectionnez Suivant.
  
  La liste contient les informations suivantes :
  - ID de requête
  - Texte de requête : instruction Transact-SQL qui peut être développée en sélectionnant le bouton ...
  - Exécutions : affiche le nombre d’exécutions de cette requête pour l’ensemble de 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.
  - Pourcentage de modification : changement de pourcentage pour la métrique sélectionnée entre l’état de mise à niveau avant et après la mise à niveau de la compatibilité de la 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.

Remarque

Les requêtes avec Paramétrable = False ne peuvent pas être sélectionnées pour l’expérimentation.

Important

Un message signale qu’une fois que l’Assistant Paramétrage de requêtes sera passé à la phase d’expérimentation, il ne sera plus possible de revenir à la page Afficher l’analyse.
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 requête : instruction Transact-SQL qui peut être développée 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.
- Pourcentage de modification : changement de pourcentage pour 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é.

S’il se révèle par la suite nécessaire d’annuler une optimisation proposée, sélectionnez la requête en question et sélectionnez Restaurer. 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 avait des repères de plan déployé, vous ne pouvez pas utiliser l’Assistant Paramétrage de requêtes pour revenir en arrière. 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.