Déterminer si l’Assistant Paramétrage de requêtes est approprié pour vous
Vous êtes conscient des risques de régression des plans de requête et de perte de performances suite aux mises à niveau des versions de la base de données. Pour maintenir les performances après les mises à niveau de la base de données, vous devez trouver la meilleure méthode pour identifier et atténuer les requêtes en régression. Cette unité explique comment vous pouvez utiliser le Magasin des requêtes et l’Assistant Paramétrage de requêtes pour faire en sorte que des performances réduites ne soient pas un problème après les mises à niveau.
Bien démarrer avec le Magasin des requêtes et l’Assistant Paramétrage de requêtes
L’Assistant Paramétrage de requêtes dépend des données du Magasin des requêtes pour trouver les requêtes qui régressent après une mise à niveau. Le Magasin des requêtes vous permet de collecter des métriques sur l’ancienne version de la base de données avant de procéder à la mise à niveau.
Le Magasin des requêtes a été introduit dans SQL Server 2016 et l’Assistant Paramétrage de requêtes a été introduit dans SQL Server 2017. Les versions d’une base de données qui s’exécutent sur une instance de SQL Server 2022 peuvent utiliser ces deux fonctionnalités. Ces outils sont intégrés à SQL Server Management Studio (SSMS) et fonctionnent au niveau de la base de données.
Le niveau de compatibilité de la base de données sur l’instance détermine sa version, qui à son tour détermine la version de l’estimateur de cardinalité qu’il utilise. L’estimateur de cardinalité prédit le nombre de lignes qu’une requête est susceptible de retourner, de sorte que l’optimiseur de requête puisse sélectionner le plan le plus économique. SQL Server 2014 a introduit un algorithme d’estimateur de cardinalité mis à niveau qui profite à la plupart des requêtes, mais qui peut rarement avoir un impact négatif sur les performances.
Pour mesurer l’impact sur les performances, le Magasin des requêtes signale les requêtes en régression et les requêtes qui consomment le plus de ressources système. L’Assistant Paramétrage de requêtes compare les données de performances des requêtes du Magasin des requêtes avant et après la mise à niveau de la base de données, et fait des essais sur les requêtes pour en améliorer les performances.
Remarque
L’Assistant Paramétrage de requêtes n’est pas disponible pour les bases de données Azure SQL Database ou SQL Managed Instance. Pour ces bases de données, envisagez d’utiliser l’extension de migration Azure SQL pour Azure Data Studio.
L’Assistant Paramétrage de requêtes et la correction de plan automatique
Quand SQL Server exécute une requête Transact-SQL (T-SQL), il analyse les plans possibles qui peuvent exécuter la requête. SQL Server met en cache les plans pour les requêtes qui s’exécutent correctement et les réutilise quand les requêtes sont réexécutées.
SQL Server sélectionne le plan optimal pour une requête et l’utilise jusqu’à ce qu’une circonstance le force à en choisir un nouveau. Ces circonstances peuvent être la recompilation du plan par le moteur de base de données, l’ajout ou la suppression d’un index, ou un changement dans les statistiques.
Le nouveau plan n’est pas toujours une amélioration de l’ancien. Vous pouvez exécuter la commande suivante pour rechercher les requêtes dont le plan a régressé.
SELECT * FROM sys.dm_db_tuning_recommendations
Vous pouvez ensuite utiliser la procédure stockée sp_force_plan pour forcer SQL Server à utiliser un plan spécifique recommandé.
EXEC sp_force_plan @query_id = 1187, @plan_id = 1975
La procédure sp_force_plan est un processus manuel qui est potentiellement fastidieux si de nombreuses requêtes régressent sur une base de données mise à niveau. SQL Server 2017 a introduit une nouvelle fonctionnalité nommée correction de plan automatique pour optimiser automatiquement les requêtes et supprimer la nécessité d’une intervention manuelle. Vous pouvez activer la correction de plan automatique sur une base de données en exécutant l’instruction suivante :
ALTER DATABASE <database-name> SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)
Quand elle est définie au niveau de la base de données, la correction de plan automatique indique à SQL Server d’utiliser le dernier plan de requête approprié. SQL Server continue de surveiller le plan pour détecter les régressions pendant l’exécution du plan et vérifier qu’il offre des performances optimales.
La correction de plan automatique fonctionne différemment de l’Assistant Paramétrage de requêtes. L’utilisation du dernier plan approprié peut signifier le retour à un estimateur de cardinalité antérieur. À l’inverse, l’Assistant Paramétrage de requêtes effectue ses essais en utilisant la version de l’estimateur de cardinalité correspondant au niveau de compatibilité de la base de données cible.
Résumé
L’Assistant Paramétrage de requêtes est disponible dans SQL Server 2022 et son fonctionnement dépend du Magasin des requêtes. L’Assistant Paramétrage de requêtes doit disposer des données de référence du Magasin des requêtes pour une base de données à son niveau de compatibilité précédent pour pouvoir observer des requêtes et effectuer des comparaisons après une mise à niveau.
La correction de plan automatique, introduite dans SQL Server 2017, supprime la nécessité d’identifier et de forcer manuellement un plan de requête. La correction de plan automatique peut être activée au niveau de la base de données, mais elle peut faire passer l’estimateur de cardinalité à une version antérieure. L’Assistant Paramétrage de requêtes utilise la version de l’estimateur de cardinalité correspondant au niveau de compatibilité cible.