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 bon plan connu et surveille les performances d'exécution des requêtes. Si le plan forcé ne fonctionne pas mieux que le plan précédent, il n’est pas appliqué et un nouveau plan est compilé. Toutefois, si le plan forcé continue de surperformer le plan incorrect précédent, il reste en place jusqu’à ce qu’une recompilation se produise.

Vous pouvez activer la correction automatique du plan via une requête T-SQL. 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 satisfait, l’instruction ALTER échoue.

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 a la possibilité d’effectuer le réglage automatique des index. Au fil du temps, il apprend des charges de travail existantes et fournit des recommandations pour ajouter ou supprimer des index afin d’améliorer les performances. Comme pour forcer des plans de requête améliorés, la base de données peut être configurée pour créer ou supprimer automatiquement des index en fonction de leurs performances, comme illustré dans l’image suivante.

Lorsqu’elle est activée, 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 surveille les ressources requises pour implémenter de nouveaux index afin d’éviter la dégradation 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 sensiblement, l’index récemment supprimé est recréé automatiquement.