Performances intelligentes
SQL Server et Azure SQL incluent l’automatisation pour faire bénéficier à votre application de performances cohérentes. Ces fonctionnalités d’automatisation sont collectivement appelées « performances intelligentes ».
Les performances intelligentes pour Azure SQL incluent le traitement intelligent des requêtes, la correction de plan automatique et le réglage automatique.
Traitement de requêtes intelligent
Le traitement intelligent des requêtes (IQP) est une suite de nouvelles fonctionnalités intégrées au processeur de requêtes. Pour l’activer, vous utilisez le dernier niveau de compatibilité de la base de données. Les applications peuvent gagner en performances en utilisant le dernier niveau de compatibilité de la base de données. Aucune modification de code n’est nécessaire. Un exemple de traitement intelligent des requêtes est la compilation différée des variables de table qui permet d’exécuter plus rapidement les requêtes utilisant des variables de table.
Azure SQL Database et Azure SQL Managed Instance prennent en charge le même niveau de compatibilité de la base de données requis (150) pour utiliser IQP que SQL Server 2019 et versions ultérieures.
Correction de plan automatique
L’un des problèmes de performances les plus difficiles à résoudre avec SQL Server est la régression du plan de requête. Une régression de plan de requête se produit lorsque la même requête est recompilée et qu’un nouveau plan entraîne une dégradation des performances.
Après analyse des données dans le Magasin des requêtes, SQL Server 2017 et Azure SQL Database ont introduit le concept de correction de plan automatique. Quand vous activez le Magasin des requêtes avec une base de données dans SQL Server 2017 (ou ultérieur) et dans Azure SQL Database, le moteur de SQL Server recherche les régressions de plan de requête et fournit des recommandations. Vous pouvez voir ces recommandations dans la vue de gestion dynamique (DMV) sys.dm_db_tuning_recommendations. Il s’agit notamment d’instructions T-SQL pour forcer manuellement un plan de requête quand les performances sont dans un état correct.
Si vous êtes en confiance dans ces suggestions, vous pouvez autoriser SQL Server à forcer les plans automatiquement lorsque des régressions sont rencontrées. Activez la correction automatique de plan en utilisant ALTER DATABASE et l’argument AUTOMATIC_TUNING.
Pour Azure SQL Database, vous pouvez également activer la correction de plan automatique par le biais des options de réglage automatique dans le portail Azure ou les API REST. Les recommandations en matière de correction de plan automatique sont toujours activées pour toute base de données où le Magasin des requêtes est activé (valeur par défaut pour Azure SQL Database et Azure SQL Managed Instance). Pour les nouvelles bases de données, la correction automatique de plan (FORCE_PLAN) est activée par défaut pour Azure SQL Database.
Réglage automatique pour Azure SQL Database
La correction automatique de plan est un exemple de réglage automatique dans Azure SQL et SQL Server, alors que l’indexation automatique est un aspect unique du réglage automatique pour Azure SQL Database.
Remarque
L’indexation automatique n’est pas actuellement disponible dans SQL Managed Instance.
Le Cloud fournit une méthode permettant à Microsoft de fournir des services supplémentaires sous forme de suggestions et d’automatisations en matière de performances en dehors des recommandations de plan. Cette fonctionnalité est appelée « réglage automatique » pour Azure SQL Database. Ces services s’exécutent en arrière-plan et analysent les données de performances d’une instance d’Azure SQL Database. Ces services sont inclus dans le prix d’un abonnement de base de données.
Le scénario principal de réglage automatique est conçu pour traiter les index. Le réglage automatique analyse les données de la télémétrie d’une base de données, notamment celles du Magasin des requêtes et des vues DMV, pour recommander des index à créer en vue d’améliorer les performances de l’application. Vous pouvez également activer le réglage automatique pour créer automatiquement des index afin d’améliorer les performances des requêtes. Le réglage automatique permet aussi de superviser les modifications d’index et de recommander la suppression d’index ou de les supprimer automatiquement s’ils n’améliorent pas les performances des requêtes.
Le réglage automatique pour Azure SQL Database suit une approche conservatrice pour recommander des index. Cela signifie que les recommandations qui peuvent apparaître dans une vue DMV, telle que sys.dm_db_missing_index_details, ou un plan d’affichage de requête risquent de ne pas apparaître immédiatement sous la forme de recommandations pour le réglage automatique. Les services de réglage automatique supervisent les requêtes au fil du temps et utilisent des algorithmes de Machine Learning pour faire des recommandations qui affectent véritablement les performances des requêtes.
Sachez que le réglage automatique pour les recommandations d’index ne tient pas compte de la surcharge qu’un index peut causer sur vos opérations, par exemple les insertions, mises à jour ou suppressions. En règle générale, les nouveaux index non-cluster créés par la fonctionnalité d’index automatiques ont un impact positif majeur sur les performances.
Les requêtes paramétrables représentent un scénario supplémentaire pour le réglage automatique. Les requêtes incluant des valeurs non paramétrisées peuvent entraîner une détérioration des performances, car le plan d’exécution est recompilé chaque fois que les valeurs non paramétrisées diffèrent. Dans de nombreux cas, des requêtes identiques comportant différentes valeurs de paramètre génèrent les mêmes plans d’exécution. Toutefois, ces plans sont toujours ajoutés séparément au cache du plan. Le processus de recompilation des plans d’exécution utilise des ressources de base de données, augmente la durée des requêtes et dépasse la capacité du cache du plan. Ces événements entraînent à leur tour la suppression des plans de ce cache.
Vous pouvez utiliser l’optimisation des plans sensibles aux paramètres (PSP) pour résoudre ce scénario. L’optimisation du plan de sensibilité des paramètres active automatiquement plusieurs plans mis en cache actifs pour une instruction paramétrable unique. Les plans d’exécution mis en cache prennent en charge différentes tailles de données en fonction des valeurs de paramètres d’exécution fournies par le client.
Exemple d’index avec réglage automatique dans Azure SQL Database
Voici un exemple du portail Azure, dans lequel des index sont recommandés pour une base de données d’après l’analyse de la charge de travail au fil du temps. Nous n’avons pas encore envoyé suffisamment d’activités à votre bac à sable Azure SQL Database pour générer des recommandations comme celle-ci. Les recommandations CREATE INDEX sont générées au fur et à mesure que votre charge de travail est capturée, et non dans le court délai de cet exercice.
Dans le portail Azure, si vous examinez la Vue d’ensemble des performances, vous pouvez voir des informations sur les performances des cinq premières requêtes consommatrices de ressources, telles qu’elles apparaissent dans le Magasin des requêtes. Une recommandation est également indiquée.
La Portail Azure offre également Query Performance Insights, qui est un outil de création de rapports visuel basé sur le Magasin des requêtes. Dans cet exemple, Query Performance Insights montre la requête qui consomme le plus de ressources et vous donne des conseils pour améliorer ses performances.
Le portail Azure offre également un moyen direct de voir les recommandations relatives aux performances.
Dans cette vue, vous voyez des recommandations spécifiques et l’historique des actions de réglage automatique. Dans le cas d’un index, les détails de l’index et de la table sont affichés. L’option Automatiser active le réglage automatique.
Vous pouvez définir les options de réglage automatique au niveau du serveur de base de données ou de la base de données. Si vous aviez activé le réglage automatique dans ce scénario, l’index aurait été créé automatiquement.
Vous pouvez également voir les options de réglage automatique par le biais de la vue DMV sys.database_automatic_tuning_options.
Remarque
Les recommandations et l’automatisation pour les index et les plans sensibles aux paramètres ne sont pas disponibles pour Azure SQL Managed Instance ou SQL Server. La correction de plan automatique est disponible.
Si vous sélectionnez l’index recommandé, vous obtiendrez plus de détails sur l’index spécifique.
Vous verrez les détails de l’index, de la table et de l’espace requis. Vous pouvez appliquer l’index recommandé ou afficher un script T-SQL qui applique l’index.
Notez que l’index est un index non-cluster qui est appliqué en tant qu’index en ligne. Si un index a été appliqué sur la base d’une recommandation, manuellement ou par le biais du réglage automatique, le moteur de recommandation supervise également les performances des requêtes sur une période donnée avec l’index appliqué. Si les performances des requêtes sont inférieures à celles mesurées avant l’application de l’index, vous pouvez supprimer cet index.