Partager via

Réglage automatique

S’applique à : SQL Server 2017 (14.x) et versions ultérieures d’Azure SQL DatabaseAzure SQL Managed InstanceSQL database dans Microsoft Fabric

Le réglage automatique est une fonctionnalité de base de données qui fournit des informations sur les problèmes potentiels de performances des requêtes, recommande des solutions et corrige automatiquement les problèmes identifiés.

Le réglage automatique, introduit dans SQL Server 2017 (14.x), vous avertit chaque fois qu’un problème potentiel de performances est détecté et vous permet d’appliquer des actions correctives ou de corriger automatiquement les problèmes de performances du moteur de base de données. Le réglage automatique de SQL Server identifie et résout les problèmes de performances causés par les régressions de choix de plan d’exécution de requête. Le réglage automatique dans Azure SQL Database et SQL Database dans Microsoft Fabric crée les index nécessaires et supprime également les index inutilisés. Pour plus d’informations sur les plans d’exécution des requêtes, consultez Plans d’exécution.

Le moteur de base de données SQL Server surveille les requêtes exécutées sur la base de données et améliore automatiquement les performances de la charge de travail. Le moteur de base de données dispose d’un mécanisme d’intelligence intégré qui peut ajuster et améliorer automatiquement les performances de vos requêtes en adaptant dynamiquement la base de données à votre charge de travail. Il existe deux fonctionnalités de réglage automatique disponibles :

  • La correction automatique de plan identifie les plans d’exécution de requête problématiques, tels qu’une sensibilité des paramètres ou des problèmes de détection de paramètre, et corrige les problèmes de performances liés au plan d’exécution de requête en forçant le dernier plan correct connu avant la régression. S’applique à : SQL Server (à partir de SQL Server 2017 (14.x)), d’Azure SQL Database et de base de données SQL dans Microsoft Fabric et Azure SQL Managed Instance

  • La gestion automatique des index identifie les index qui doivent être ajoutés dans votre base de données et les index qui doivent être supprimés. S’applique à : Azure SQL Database et base de données SQL dans Microsoft Fabric

Note

Dans cet article, les fonctionnalités et les comportements d’Azure SQL Database s’appliquent également à la base de données SQL dans Microsoft Fabric.

Pourquoi le réglage automatique ?

Trois des principales tâches de l’administration de base de données classique surveillent la charge de travail, identifient les requêtes Transact-SQL critiques et identifient les index qui doivent être ajoutés pour améliorer les performances, ou les index rarement utilisés et peuvent être supprimés pour améliorer les performances. Le moteur de base de données SQL Server fournit des informations détaillées sur les requêtes et les index que vous devez surveiller. Toutefois, la surveillance constante d’une base de données est une tâche difficile et fastidieuse, en particulier lorsque vous traitez de nombreuses bases de données. La gestion d’un grand nombre de bases de données peut être impossible à faire efficacement. Au lieu de surveiller et de régler manuellement votre base de données, vous pouvez envisager de déléguer certaines des actions de surveillance et de réglage au moteur de base de données à l’aide de la fonctionnalité de réglage automatique.

Comment fonctionne le réglage automatique ?

Le réglage automatique est un processus de supervision et d’analyse continu qui apprend constamment les caractéristiques de votre charge de travail et identifie les problèmes potentiels et les améliorations.

Processus de réglage automatique.

Ce processus permet à la base de données de s’adapter dynamiquement à votre charge de travail en recherchant quels index et plans peuvent améliorer les performances de vos charges de travail et les index qui affectent vos charges de travail. En fonction de ces résultats, le réglage automatique effectue des actions de réglage qui améliorent le niveau de performance de votre charge de travail. En outre, le réglage automatique analyse en permanence le niveau de performance de la base de données après avoir implémenté toutes les modifications pour s’assurer qu’elle améliore le niveau de performance de votre charge de travail. Toute action qui n’a pas amélioré le niveau de performance est automatiquement annulée. Ce processus de vérification est une fonctionnalité clé qui garantit que toute modification apportée par le réglage automatique ne diminue pas les performances globales de votre charge de travail.

Correction de plan automatique

La correction automatique du plan est une fonctionnalité de réglage automatique qui identifie la régression de choix du plan d’exécution et corrige automatiquement le problème en forçant le dernier bon plan connu. Pour plus d’informations sur les plans d’exécution des requêtes et l’optimiseur de requête, consultez le Guide d’architecture du traitement des requêtes.

Important

La correction automatique du plan dépend de l’activation du Magasin des requêtes dans la base de données pour le suivi des charges de travail.

Qu’est-ce que la régression de choix de plan d’exécution ?

Le moteur de base de données SQL Server peut utiliser différents plans d’exécution pour exécuter les requêtes Transact-SQL. Les plans de requête dépendent des statistiques, des index et d’autres facteurs. Le plan optimal qui doit être utilisé pour exécuter une requête Transact-SQL peut changer au fil du temps en fonction des modifications apportées à ces facteurs. Dans certains cas, le nouveau plan peut ne pas être meilleur que le plan précédent, et le nouveau plan peut entraîner une régression des performances, telle qu'une sensibilité des paramètres ou un problème lié au reniflage de paramètres.

Régression de choix du plan d’exécution de requête.

Chaque fois que vous remarquez qu’une régression de choix de plan s’est produite, vous devez trouver un bon plan précédent et le forcer à être utilisé au lieu de celui actuel. Cette opération peut être effectuée à l’aide de la sp_query_store_force_plan procédure. Le moteur de base de données dans SQL Server 2017 (14.x) fournit des informations sur les plans régressés et les actions correctives recommandées. En outre, le moteur de base de données vous permet d’automatiser entièrement ce processus et de laisser le moteur de base de données résoudre tout problème trouvé en lien avec la modification du plan.

Important

La correction automatique du plan doit être utilisée dans l’étendue d’une mise à niveau de niveau de compatibilité de base de données, une fois qu’une ligne de base de référence a été capturée, pour atténuer automatiquement les risques de mise à niveau de la charge de travail. Pour plus d’informations sur ce cas d’usage, consultez Conserver la stabilité des performances pendant la mise à niveau vers sql Server plus récent.

Correction automatique du choix de plan

Le moteur de base de données peut basculer automatiquement vers le dernier bon plan connu chaque fois qu’une régression de choix de plan est détectée.

Correction du choix du plan d’exécution de requête.

Le moteur de base de données détecte automatiquement toute régression de choix de plan potentielle, y compris le plan qui doit être utilisé au lieu du plan incorrect. Le plan d’exécution résultant forcé par la correction automatique du plan sera identique ou similaire au dernier bon plan connu. Étant donné que le plan résultant peut ne pas être identique au dernier bon plan connu, les performances du plan forcé peuvent varier. Dans de rares cas, la différence de performances peut être significative et négative ; dans ce cas, la correction automatique du plan cesse automatiquement de tenter de forcer le plan de remplacement.

Lorsque le moteur de base de données applique le dernier plan correct connu avant la régression, il surveille automatiquement les performances du plan forcé. Si le plan forcé n’est pas mieux que le plan régressé, le nouveau plan n’est pas appliqué et le moteur de base de données compile un nouveau plan. Si le moteur de base de données vérifie que le plan forcé est mieux que le plan régressé, le plan forcé est conservé. Elle sera conservée jusqu’à ce qu’une recompilation se produise (par exemple, lors de la prochaine mise à jour des statistiques ou du changement de schéma). Pour plus d’informations sur le forçage de plan et les types de plans qui peuvent être forcés, consultez les limitations du plan forcé.

Note

Si l'instance SQL Server est redémarrée avant qu'une action de forçage de plan ne soit vérifiée, ce plan sera automatiquement rendu non forcé. Sinon, le forçage de plan est conservé lors des redémarrages de SQL Server.

Activer la correction automatique du choix de plan

Vous pouvez activer l'optimisation automatique par base de données et spécifier que le dernier bon plan doit être forcé chaque fois qu'une régression due à une modification du plan est détectée. Le réglage automatique est activé à l’aide de la commande suivante :

ALTER DATABASE <yourDatabase>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

Une fois que vous avez activé cette option, le moteur de base de données force automatiquement toute recommandation où le gain estimé du processeur est supérieur à 10 secondes, ou le nombre d’erreurs dans le nouveau plan est supérieur au nombre d’erreurs dans le plan recommandé et vérifie que le plan forcé est meilleur que celui actuel.

Pour activer le réglage automatique dans Azure SQL Database et Azure SQL Managed Instance, consultez Activer le réglage automatique dans Azure SQL Database à l’aide du portail Azure.

Alternative - Correction manuelle du choix de plan

Sans réglage automatique, les utilisateurs doivent surveiller régulièrement le système et rechercher les requêtes qui ont régressé. Si un plan a régressé, l’utilisateur doit trouver un bon plan précédent et le forcer à la place de celui en cours, à l’aide de la procédure sp_query_store_force_plan. La meilleure pratique consisterait à forcer le dernier bon plan connu, car les plans plus anciens peuvent ne pas être valides en raison des changements de statistiques ou d’index. L’utilisateur qui force le dernier plan correct connu doit surveiller les performances de la requête exécutée à l’aide du plan forcé et vérifier que le plan forcé fonctionne comme prévu. Selon les résultats de la surveillance et de l’analyse, le plan doit être forcé ou l’utilisateur doit trouver un autre moyen d’optimiser la requête, par exemple la réécriture. Les plans forcés manuellement ne doivent pas être forcés pour toujours, car le moteur de base de données doit être en mesure d’appliquer des plans optimaux. L’utilisateur ou l’administrateur de base de données doit éventuellement annuler le plan à l’aide sp_query_store_unforce_plan de la procédure et laisser le moteur de base de données trouver le plan optimal.

Tip

Vous pouvez également utiliser la vue Query Store des requêtes avec plans forcés pour localiser et annuler les plans.

SQL Server fournit toutes les vues et procédures nécessaires pour surveiller les performances et résoudre les problèmes dans le Magasin des requêtes.

Dans SQL Server 2016 (13.x), vous pouvez identifier les régressions du choix de plan en utilisant les vues système du Query Store. À compter de SQL Server 2017 (14.x), le moteur de base de données détecte et montre les régressions potentielles du choix de plan ainsi que les actions recommandées à appliquer dans la vue DMV sys.dm_db_tuning_recommendations (Transact-SQL). La vue DMV affiche des informations sur le problème, l’importance du problème et des détails tels que la requête identifiée, l’ID du plan régressé, l’ID du plan utilisé comme base de référence pour la comparaison et l’instruction Transact-SQL qui peut être exécutée pour résoudre le problème.

type description datetime score details ...
FORCE_LAST_GOOD_PLAN Temps processeur passé de 4 ms à 14 ms 3/17/2017 83 queryId recommendedPlanId regressedPlanId T-SQL
FORCE_LAST_GOOD_PLAN Temps processeur passé de 37 ms à 84 ms 3/16/2017 26 queryId recommendedPlanId regressedPlanId T-SQL

Certaines colonnes de cette vue sont décrites dans la liste suivante :

  • Type de l’action FORCE_LAST_GOOD_PLANrecommandée .
  • Description qui contient des informations sur la raison pour laquelle le moteur de base de données pense que ce changement de plan est une régression potentielle des performances.
  • Datetime quand la régression potentielle est détectée.
  • Score de cette recommandation.
  • Détails sur les problèmes tels que l’ID du plan détecté, l’ID du plan régressé, l’ID du plan qui doit être forcé de résoudre le problème, Transact-SQL script qui peut être appliqué pour résoudre le problème, etc. Les détails sont stockés au format JSON.

Utilisez la requête suivante pour obtenir un script qui résout le problème et des informations supplémentaires sur le gain estimé :

SELECT reason, score,
      script = JSON_VALUE(details, '$.implementationDetails.script'),
      planForceDetails.*,
      estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount)
                  * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000,
      error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES','NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            regressedPlanId int '$.regressedPlanId',
            recommendedPlanId int '$.recommendedPlanId',
            regressedPlanErrorCount int,
            recommendedPlanErrorCount int,
            regressedPlanExecutionCount int,
            regressedPlanCpuTimeAverage float,
            recommendedPlanExecutionCount int,
            recommendedPlanCpuTimeAverage float
          ) AS planForceDetails;

Voici l'ensemble des résultats.

reason score script query_id plan_id actuel plan_id recommandé gain_estimé sujette à des erreurs
Temps processeur passé de 3 ms à 46 ms 36 EXEC sp_query_store_force_plan 12, 17 ; 12 28 17 11.59 0

La colonne estimated_gain représente le nombre estimé de secondes qui seraient enregistrées si le plan recommandé serait utilisé pour l’exécution de la requête au lieu du plan actuel. Le plan recommandé doit être forcé au lieu du plan actuel si le gain est supérieur à 10 secondes. S’il existe plus d’erreurs (par exemple, des délais d’attente ou des exécutions abandonnées) dans le plan actuel que dans le plan recommandé, la colonne error_prone est définie sur la valeur YES. Un plan sujet aux erreurs est une autre raison pour laquelle le plan recommandé doit être forcé au lieu de celui actuel.

Bien que le moteur de base de données fournisse toutes les informations nécessaires pour identifier les régressions de choix de plan, la surveillance continue et la résolution des problèmes de performances peuvent devenir un processus fastidieux. Le réglage automatique facilite beaucoup ce processus.

Note

Les données de la sys.dm_db_tuning_recommendations vue dynamique ne sont pas conservées après un redémarrage du moteur de base de données. Utilisez la colonne sqlserver_start_time dans sys.dm_os_sys_info pour rechercher la dernière heure de démarrage du moteur de base de données.

Gestion automatique des index

Dans Azure SQL Database, la gestion des index est facile, car Azure SQL Database apprend votre charge de travail et garantit que vos données sont toujours indexées de manière optimale. Une conception d’index appropriée est essentielle pour optimiser les performances de votre charge de travail, et la gestion automatique des index peut vous aider à optimiser vos index. La gestion automatique des index peut résoudre les problèmes de performances dans des bases de données indexées incorrectement, ou gérer et améliorer les index sur le schéma de base de données existant. Le réglage automatique dans Azure SQL Database effectue les actions suivantes :

  • Identifie les index qui peuvent améliorer les performances de vos requêtes Transact-SQL qui lisent les données des tables.
  • Identifie les index redondants ou inutilisés durant une longue période qui pourraient être supprimés. La suppression d’index inutiles améliore les performances des requêtes qui mettent à jour les données dans les tables.

Pourquoi avez-vous besoin de la gestion des index ?

Les index accélèrent certaines de vos requêtes qui lisent les données des tables, mais elles peuvent ralentir les requêtes qui mettent à jour les données. Vous devez analyser soigneusement quand créer un index et quelles colonnes vous devez inclure dans l’index. Certains index peuvent ne pas être nécessaires après un certain temps. Par conséquent, vous devez identifier et supprimer périodiquement ces index qui n’apportent aucun avantage. Si vous ignorez les index inutilisés, les performances des requêtes qui mettent à jour les données seraient réduites sans aucun avantage pour les requêtes qui lisent les données. Les index inutilisés affectent également les performances globales du système, car des mises à jour supplémentaires nécessitent une journalisation inutile.

La recherche du jeu optimal d’index qui améliore les performances des requêtes qui lisent les données de vos tables et qui ont un impact minimal sur les mises à jour peut nécessiter une analyse continue et complexe.

Azure SQL Database utilise des informations intégrées et des règles avancées qui analysent vos requêtes, identifient les index qui seraient optimaux pour vos charges de travail actuelles et identifient les index susceptibles d’être supprimés. Azure SQL Database garantit que vous disposez d’un ensemble minimal d’index nécessaires qui optimisent les requêtes qui lisent les données, avec un impact réduit sur les autres requêtes.

Gestion automatique des index

Outre la détection, Azure SQL Database peut appliquer automatiquement des recommandations identifiées. Si vous constatez que les règles intégrées améliorent les performances de votre base de données, vous pouvez laisser Azure SQL Database gérer automatiquement vos index.

Quand Azure SQL Database applique une recommandation CREATE INDEX ou DROP INDEX, elle surveille automatiquement les performances des requêtes affectées par l’index. Un nouvel index sera conservé uniquement si les performances des requêtes affectées sont améliorées. L’index supprimé est automatiquement recréé s’il existe des requêtes qui s’exécutent plus lentement en raison de l’absence de l’index.

Considérations relatives à la gestion automatique des index

Les actions requises pour créer des index nécessaires dans Azure SQL Database peuvent consommer des ressources et affecter temporellement les performances de la charge de travail. Pour réduire l’impact de la création d’index sur les performances de la charge de travail, Azure SQL Database trouve une fenêtre de temps appropriée pour toute opération de gestion des index. L’action de réglage est reportée si la base de données a besoin de ressources pour exécuter votre charge de travail et redémarre lorsque la base de données a suffisamment de ressources inutilisées qui peuvent être utilisées pour la tâche de maintenance. Une fonctionnalité importante de la gestion automatique des index est une vérification des actions. Quand Azure SQL Database crée ou supprime un index, un processus de supervision analyse les performances de votre charge de travail pour vérifier que l’action a amélioré les performances globales. S’il n’a pas apporté d’amélioration significative , l’action est immédiatement rétablie. De cette façon, Azure SQL Database garantit que les actions de réglage automatique n’affectent pas négativement les performances de votre charge de travail. Les index créés par le réglage automatique sont transparents pour l’opération de maintenance sur le schéma sous-jacent. Les modifications de schéma telles que la suppression ou le changement de nom de colonnes ne sont pas bloquées par la présence d’index créés automatiquement. Les index créés automatiquement par Azure SQL Database sont immédiatement supprimés lorsque la table ou les colonnes associées sont supprimées.

Alternative - gestion manuelle des index

Sans gestion automatique des index, un utilisateur ou un administrateur de base de données doit interroger manuellement la vue sys.dm_db_missing_index_details (Transact-SQL) ou utiliser le rapport Tableau de bord des performances dans Management Studio pour rechercher des index susceptibles d’améliorer les performances, créer des index à l’aide des détails fournis dans cette vue et surveiller manuellement les performances de la requête. Pour trouver les index qui doivent être supprimés, les utilisateurs doivent surveiller les statistiques d’utilisation opérationnelle des index pour trouver des index rarement utilisés.

Azure SQL Database simplifie ce processus. Azure SQL Database analyse votre charge de travail, identifie les requêtes qui peuvent être exécutées plus rapidement avec un nouvel index et identifie les index inutilisés ou dupliqués. Pour plus d’informations sur l’identification des index qui doivent être modifiés, consultez Rechercher des recommandations d’index dans le portail Azure.

Étapes suivantes

  • Last updated on