Décrire le paramétrage automatique
Le paramétrage automatique est une fonctionnalité de supervision et d’analyse qui apprend en continu sur votre charge de travail et identifie les problèmes et améliorations potentiels.
Les recommandations de paramétrage automatique sont basées sur les données collectées à partir de Magasin des requêtes. Les plans d’exécution évoluent avec le temps en raison des modifications apportées aux schémas, aux index ou aux données qui entraînent la mise à jour des statistiques. Cette évolution peut entraîner des performances faibles pour les requêtes, car le plan d’exécution ne répond plus aux demandes d’une requête donnée.
En outre, le paramétrage automatique permet la collecte et l’application de métriques de services de Machine Learning et de métriques de performances afin de fournir des suggestions d’amélioration, et même permettre l’auto-correction.
Qu’il soit local ou dans le cloud, le paramétrage automatique vous permet d’identifier les problèmes provoqués par la régression du plan d’exécution des requêtes. De plus, dans Azure SQL Database, vous pouvez améliorer encore davantage les performances des requêtes par le paramétrage des index. La paramétrage automatique Azure SQL Database peut identifier les index qui doivent être ajoutés ou même supprimés de la base de données afin d’améliorer les performances des requêtes.
Correction de plan automatique
Avec l’aide des données du Magasin des requêtes, le moteur de base de données peut déterminer quand les performances des plans d’exécution des requêtes ont régressé. Même si vous pouvez identifier manuellement un plan qui a connu une baisse de performances par le biais de l’interface utilisateur, le Magasin des requêtes permet quant à lui d’être averti automatiquement.
Dans l’exemple ci-dessus, vous pouvez voir une coche à côté de l’ID de plan 1, ce qui signifie que le plan a été forcé. Une fois la fonctionnalité activée, le moteur de base de données force automatiquement tout plan d’exécution de requête recommandé lorsque :
- Le plan précédent avait un taux d’erreur plus élevé que le plan recommandé
- Le gain estimé du processeur a été supérieur à 10 secondes
- Le plan forcé a été meilleur que le précédent
Le plan reviendra au dernier plan valide connu après 15 exécutions de la requête.
Lorsque le forçage de plan se produit automatiquement, le moteur de base de données applique le dernier plan valide connu et continue de superviser les performances du plan d’exécution des requêtes. Si le plan forcé n’est pas plus performant que le plan précédent, il cessera d’être forcé, ce qui entraînera le forçage de la compilation d’un nouveau plan. Si le plan forcé continue d’être plus performant que le précédent plan, il restera forcé jusqu’à ce qu’une recompilation se produise.
Vous pouvez activer la correction automatique des plans à l’aide d’une requête T-SQL, comme indiqué ci-dessous. Le magasin des requêtes doit être activé et doit être en mode Lecture-Écriture pour que la commande aboutisse. Si l’un de ces deux critères n’est pas respecté, l’instruction ALTER échouera.
ALTER DATABASE [WideWorldImporters] SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);
Vous pouvez examiner les recommandations relatives au paramétrage automatique via la vue de gestion dynamique (DMV) sys.dm_db_tuning_recommendations, qui est disponible dans SQL Server 2017 et versions ultérieures, ainsi que dans les solutions Azure SQL Database. Cette vue DMV fournit des informations telles que la raison pour laquelle la recommandation a été fournie, le type de recommandation et l’état de la recommandation. Pour vérifier que le paramétrage automatique est activé pour une base de données, vérifiez la vue sys.database_automatic_tuning_options.
Gestion automatique des index
Azure SQL Database peut paramétrer automatiquement les index. Avec le temps, la base de données apprendra à connaître les charges de travail existantes et fournira des recommandations concernant l’ajout ou la suppression d’index afin d’offrir de meilleures performances. Comme pour le forçage de plans de requête améliorés, la base de données peut être configurée de manière à permettre la création ou la suppression automatique d’index en fonction des performances d’index existantes, comme indiqué ci-dessous :
Lorsqu’il est activé, la page Recommandations sur les performances identifie les index qui peuvent être créés ou supprimés en fonction des performances des requêtes. N’oubliez pas que cette fonctionnalité n’est pas disponible pour les bases de données locales, et qu’elle n’est disponible que pour Azure SQL Database.
Vous pouvez également utiliser la requête suivante pour voir les fonctionnalités de réglage automatique activées dans votre base de données :
SELECT name,
desired_state_desc,
actual_state_desc,
reason_desc
FROM sys.database_automatic_tuning_options
La création d’index peut consommer des ressources, et le timing des créations d’index est essentiel pour garantir qu’elles n’affecteront pas vos charges de travail.
Azure SQL Database supervisera les ressources nécessaires pour implémenter de nouveaux index afin d’éviter une détérioration des performances. L’action de paramétrage est différée jusqu’à ce que les ressources disponibles le soient, par exemple si des ressources sont requises pour les charges de travail existantes et qu’elles ne sont pas disponibles pour la création d’un index.
La surveillance garantit que toute action effectuée ne risque pas de nuire aux performances. Si un index est supprimé et que les performances des requêtes se dégradent, l’index supprimé est automatiquement recréé.