Types détectables de goulots d’étranglement des performances de requêtes dans SQL Server et Azure SQL Managed Instance

  • Article

S’applique à : SQL Server Azure SQL Managed Instance

Quand vous tentez de résoudre un goulot d’étranglement des performances, commencez par déterminer si ce goulot d’étranglement se produit pendant que la requête est en cours d’exécution ou en attente. Des résolutions différentes s’appliquent en fonction du résultat de cette détermination. Les problèmes et résolutions relatifs à chaque type de problème sont décrits dans cet article.

Vous pouvez utiliser des vues de gestion dynamique (DMV) SQL Server pour détecter ces types de goulots d’étranglement des performances.

Problèmes liés à l’exécution : les problèmes liés à l’exécution sont généralement associés à des problèmes de compilation, ce qui entraîne des problèmes liés à un plan de requête non optimal ou à l’exécution associés à des ressources insuffisantes ou surutilisées. Problèmes liés à l’attente : les problèmes liés à l’attente sont généralement associés aux éléments suivants :

  • Verrous (blocage)
  • E/S
  • Contention liée à l’utilisation de tempdb
  • Attentes d’allocation de mémoire

Cet article a trait à SQL Server et Azure SQL Managed Instance. Consultez également les Types détectables de goulots d’étranglement des performances des requêtes dans Azure SQL Database.

Problèmes de compilation entraînant un plan de requête non optimal

Un plan non optimal généré par l’optimiseur de requête SQL peut être à l’origine du ralentissement des performances des requêtes. L’optimiseur de requête SQL peut produire un plan non optimal à cause d’un index manquant, de statistiques périmées, d’une estimation incorrecte du nombre de lignes à traiter ou bien d’une estimation inexacte de la mémoire nécessaire. Si vous savez que la requête a été exécutée plus rapidement dans le passé ou sur une autre instance, comparez les plans d’exécution réels pour voir s’ils diffèrent.

L’exemple de réglage et d’indication de requêtes dans l’article Régler les applications et les bases de données pour de meilleures performances montre l’impact d’un plan de requête non optimal en raison d’une requête paramétrable, la manière de détecter cette condition et les étapes à suivre pour résoudre le problème à l’aide d’un indicateur de requête.

Résolution de requêtes avec des plans d’exécution de requête non optimaux

Les sections suivantes expliquent comment résoudre les requêtes avec un plan d’exécution de requête non optimal.

Requêtes confrontées à des problèmes de plan sensible aux paramètres

Un problème de plan sensible aux paramètres se produit quand l’optimiseur de requête génère un plan d’exécution de requête qui est optimal uniquement pour une valeur de paramètre (ou un ensemble de valeurs) spécifique. Le plan mis en cache n’est donc pas optimal pour les valeurs de paramètre qui sont utilisées dans des exécutions consécutives. Les plans non optimaux peuvent alors provoquer des problèmes de performances des requêtes et entraîner une dégradation du débit global de la charge de travail.

Pour plus d’informations sur la détection des paramètres et le traitement des requêtes, consultez le Guide de l’architecture de traitement des requêtes.

Plusieurs solutions de contournement peuvent atténuer les problèmes de plan sensible aux paramètres. Chaque solution de contournement présente des avantages et des inconvénients :

  • Une nouvelle fonctionnalité introduite avec SQL Server 2022 (16.x) est l’optimisation du plan sensible aux paramètres qui tente d’atténuer la plupart des plans de requête non optimaux causés par la sensibilité aux paramètres. Cela est activé avec le niveau de compatibilité de la base de données 160.
  • Utilisez l’indicateur de requête RECOMPILE à chaque exécution de la requête. Cette solution compense la durée de compilation et augmente l’UC pour un plan de meilleure qualité. L’option RECOMPILE n’est souvent pas possible pour les charges de travail exigeant un débit élevé.
  • Utilisez l’indicateur de requête OPTION (OPTIMIZE FOR...) pour remplacer la valeur de paramètre réelle par une valeur de paramètre standard qui produit un plan suffisant pour la plupart des possibilités de valeurs de paramètre. Cette option nécessite une bonne compréhension des valeurs de paramètre optimal et des caractéristiques du plan associé.
  • Utilisez l’indicateur de requête OPTION (OPTIMIZE FOR UNKNOWN) pour remplacer la valeur de paramètre réelle par la moyenne du vecteur de densité. Pour effectuer cette opération, vous pouvez également capturer les valeurs de paramètres entrantes dans des variables locales, puis utiliser ces variables locales dans des prédicats à la place des paramètres. Pour cette correction, la densité moyenne doit être suffisante.
  • Désactivez entièrement la détection de paramètres en spécifiant l’indicateur de requête DISABLE_PARAMETER_SNIFFING.
  • Utilisez l’indicateur de requête KEEPFIXEDPLAN pour empêcher les recompilations dans le cache. Cette solution de contournement suppose que le plan courant suffisant est celui qui se trouve déjà dans le cache. Vous pouvez également désactiver les mises à jour de statistiques automatiques afin de réduire le risque d’éviction du plan suffisant et de compilation d’un plan insuffisant.
  • Forcez le plan en spécifiant explicitement l’indicateur de requête USE PLAN dans le texte de la requête. Ou définissez un plan spécifique en utilisant le Magasin des requêtes ou en activant le réglage automatique.
  • Remplacez la procédure unique par un ensemble imbriqué de procédures, qui peuvent être utilisées en fonction d’une logique conditionnelle et des valeurs de paramètre associées.
  • Créez des alternatives d’exécution de chaîne dynamique sur une définition de procédure statique.

Pour appliquer des indicateurs de requête, modifiez la requête ou utilisez des indicateurs du Magasin des requêtes pour appliquer l’indicateur sans apporter de modifications de code. Dans les versions de SQL Server antérieures à SQL Server 2022, utilisez des repères de plan.

Pour plus d’informations sur la résolution des problèmes de plan sensible aux paramètres, consultez ces billets de blog :

Compilations provoquées par un paramétrage incorrect

Lorsqu’une requête contient des littéraux, le moteur de base de données paramètre automatiquement l’instruction, ou un utilisateur paramètre explicitement l’instruction pour réduire le nombre de compilations. Un nombre élevé de compilations pour une requête utilisant le même modèle mais différentes valeurs littérales peut entraîner une augmentation de l’utilisation du processeur. De même, si vous ne paramétrez que partiellement une requête qui garde des littéraux, le moteur de base de données ne paramètre pas davantage la requête.

Voici un exemple de requête partiellement paramétrée :

SELECT *
FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c1 = @p1 AND t2.c2 = '961C3970-0E54-4E8E-82B6-5545BE897F8F';

Dans cet exemple, t1.c1 prend la valeur @p1, mais t2.c2 garde un GUID comme littéral. Dans ce cas, si vous changez la valeur de c2, la requête est traitée comme une requête différente, ce qui engendre une nouvelle compilation. Pour réduire le nombre de compilations dans cet exemple, vous pouvez également paramétrer le GUID.

La requête suivante affiche le nombre de requêtes par hachage de requête pour déterminer si une requête est correctement paramétrée :

SELECT TOP 10
  q.query_hash
  , count (distinct p.query_id ) AS number_of_distinct_query_ids
  , min(qt.query_sql_text) AS sampled_query_text
FROM sys.query_store_query_text AS qt
  JOIN sys.query_store_query AS q
     ON qt.query_text_id = q.query_text_id
  JOIN sys.query_store_plan AS p
     ON q.query_id = p.query_id
  JOIN sys.query_store_runtime_stats AS rs
     ON rs.plan_id = p.plan_id
  JOIN sys.query_store_runtime_stats_interval AS rsi
     ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
  rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())
  AND query_parameterization_type_desc IN ('User', 'None')
GROUP BY q.query_hash
ORDER BY count (distinct p.query_id) DESC;

Facteurs impactant les changements de plan de requête

Une recompilation du plan d’exécution de requête peut aboutir à un plan de requête généré qui diffère du plan initial mis en cache. Un plan initial existant peut être automatiquement recompilé pour diverses raisons :

  • Des changements dans le schéma sont référencés par la requête
  • Des changements de données dans les tables sont référencés par la requête
  • Des options de contexte de requête ont été changées

Un plan compilé peut être supprimé du cache pour plusieurs raisons. Par exemple :

  • Redémarrages d’instance
  • Changements de configuration dont l’étendue est la base de données
  • Sollicitation de la mémoire
  • Requêtes explicites d’effacement du cache

Si vous utilisez un indicateur RECOMPILE, le plan n’est pas mis en cache.

Une recompilation (ou une nouvelle compilation après éviction du cache) peut toujours aboutir à la génération d’un plan d’exécution de requête identique au plan initial. Quand le plan change par rapport au plan précédent ou au plan initial, cela s’explique généralement par l’une des raisons suivantes :

  • Modification de la conception physique : par exemple, de nouveaux index créés couvrent plus efficacement les exigences d’une requête. Les nouveaux index peuvent être utilisés sur une nouvelle compilation si l’optimiseur de requête juge que l’utilisation des nouveaux index aboutit à un plan plus optimal que l’utilisation de la structure de données qui avait été initialement sélectionnée pour la première version de l’exécution de la requête. Toute modification physique des objets référencés peut entraîner un nouveau choix de plan au moment de la compilation.

  • Différences au niveau des ressources serveur : quand un plan d’un système diffère du plan d’un autre système, la disponibilité des ressources, comme le nombre de processeurs disponibles, peut déterminer le choix du plan généré. Par exemple, si un système a davantage de processeurs, un plan parallèle peut être choisi. Pour plus d’informations sur le parallélisme, consultez Configurer l’option de configuration du serveur Degré maximal de parallélisme.

  • Statistiques différentes : les statistiques associées aux objets référencés ont peut-être changé ou sont peut-être matériellement différentes par rapport aux statistiques du système initiales. Si les statistiques changent et qu’une recompilation a lieu, l’optimiseur de requête utilise les statistiques à partir du changement. Les fréquences et distributions de données indiquées dans les statistiques révisées peuvent différer de celles de la compilation initiale. Ces changements sont utilisés pour estimer la cardinalité. (Les estimations de cardinalité correspondent au nombre de lignes qui sont supposées être transmises dans l’arborescence de requêtes logique.) Les changements apportés aux estimations de cardinalité peuvent vous amener à choisir des opérateurs physiques et ordres d’opérations associés différents. Même des modifications mineures apportées aux statistiques peuvent entraîner une modification du plan d’exécution de la requête.

  • Changement du niveau de compatibilité de la base de données ou de la version de l’estimateur de cardinalité : un changement du niveau de compatibilité de la base de données peut donner lieu à de nouvelles stratégies et caractéristiques qui peuvent entraîner un plan d’exécution de requête différent. Au-delà du niveau de compatibilité de la base de données, l’activation ou la désactivation d’un indicateur de trace 4199 ou bien un changement d’état de l’indicateur QUERY_OPTIMIZER_HOTFIXES de configuration à l’échelle de la base de données peuvent également impacter les choix de plan d’exécution de requête au moment de la compilation. Les indicateurs de trace 9481 (forcer le CE hérité) et 2312 (forcer le CE par défaut) impactent également le choix du plan.

Problèmes de limites des ressources dans Azure SQL Managed Instance

Les performances de requêtes lentes non liées aux plans de requête non optimaux et aux index manquants sont généralement associées à des ressources insuffisantes ou surutilisées. Si le plan de requête est optimal, la requête (ainsi que la base de données) pourrait atteindre les limites pour l’instance gérée. Le débit d’écriture du journal excessif pour le niveau de service en est un exemple.

Si vous identifiez que le problème est une ressource insuffisante, vous pouvez mettre à niveau des ressources pour augmenter la capacité de votre base de données afin d’absorber les besoins en processeur. Pour plus d’informations sur la mise à l’échelle d’une instance managée, consultez Limites de ressources des niveaux de service

Problèmes de performances dus à une hausse du volume de la charge de travail

Une hausse du trafic d’application et du volume de la charge de travail peut entraîner une plus forte sollicitation du processeur. Prenez toutefois le temps de bien diagnostiquer ce problème. Si vous constatez un problème d’utilisation intensive du processeur, posez-vous ces questions pour déterminer si la hausse est due à une variation du volume de la charge de travail :

  • Les requêtes de l’application sont-elles à l’origine du problème d’utilisation intensive du processeur ?

  • Pour les principales requêtes consommatrices du processeur que vous pouvez identifier :

    • Y avait-il plusieurs plans d’exécution associés à la même requête ? Le cas échéant, pour quelle raison ?
    • Entre les requêtes ayant le même plan d’exécution, les temps d’exécution étaient-ils similaires ? Y a-t-il eu davantage d’exécutions ? Si c’est le cas, la hausse de la charge de travail est probablement à l’origine des problèmes de performances.

En résumé, si le plan d’exécution de requête s’est exécuté de la même manière, mais que l’utilisation du processeur a augmenté en même temps que le nombre d’exécutions, le problème de performances est probablement lié à une hausse de la charge de travail.

Il est souvent difficile d’identifier qu’une variation du volume de la charge de travail est à l’origine d’un problème d’utilisation du processeur. Tenez compte de ces facteurs :

  • Changement dans l’utilisation des ressources : par exemple, imaginez un scénario où l’utilisation du processeur a enregistré une hausse de 80 pour cent durant une période prolongée. Cela ne suffit pas pour conclure à une variation du volume de la charge de travail. Des régressions dans le plan d’exécution de requête et des changements dans la distribution des données peuvent également entraîner une plus grande utilisation des ressources, même si l’application exécute la même charge de travail.

  • Apparence d’une nouvelle requête : une application peut exécuter un nouvel ensemble de requêtes à des moments différents.

  • Augmentation ou diminution du nombre de requêtes : ce scénario constitue la mesure la plus évidente de la charge de travail. Le nombre de requêtes ne correspond pas toujours à une augmentation de l’utilisation des ressources. Toutefois, cette mesure est toujours un signal important, qui suppose que d’autres facteurs restent inchangés.

  • Parallélisme : un parallélisme excessif peut détériorer les performances d’autres charges de travail simultanées en privant d’autres requêtes des ressources d’UC et de thread de travail. Pour plus d’informations sur le parallélisme, consultez Configurer l’option de configuration du serveur Degré maximal de parallélisme.

Une fois que vous avez éliminé les problèmes liés à un plan non optimal et ceux liés à l’attente qui sont associés à des problèmes d’exécution, le problème de performances est généralement dû au fait que les requêtes attendent probablement des ressources. Les problèmes liés à l’attente peuvent être provoqués par :

  • Un blocage :

    une requête peut maintenir le verrou sur certains objets de la base de données pendant que d’autres requêtes essaient d’accéder aux mêmes objets. Vous pouvez identifier les requêtes bloquantes en utilisant des vues de gestion dynamiques. Pour plus d’informations, consultez Compréhension et résolution des problèmes bloquants.

  • Problèmes d’E/S

    les requêtes peuvent attendre que les pages soient écrites dans les fichiers de données ou les fichiers journaux. Dans ce cas, examinez les statistiques d’attente INSTANCE_LOG_RATE_GOVERNOR, WRITE_LOG ou PAGEIOLATCH_* dans la DMV. Consultez l’utilisation des vues de gestion dynamique (DMV) pour identifier les problèmes de performances d’E/S.

  • Problèmes liés à Tempdb

    Si la charge de travail utilise des tables temporaires ou s’il y a des déversements tempdb dans les plans, les requêtes peuvent avoir un problème avec le débit tempdb. Pour approfondir les recherches, consultez identifier les problèmes relatifs à tempdb.

  • Problèmes liés à la mémoire

    si la charge de travail n’a pas assez de mémoire, l’espérance de vie des pages peut diminuer, ou les requêtes risquent de ne pas obtenir toute la mémoire dont elles ont besoin. Dans certains cas, l’intelligence intégrée dans l’optimiseur de requête résoudra les problèmes de mémoire. Consultez l’utilisation des vues de gestion dynamique (DMV) pour identifier les problèmes liés à l’allocation de mémoire. Si vous rencontrez des erreurs de mémoire insuffisante, consultez sys.dm_os_out_of_memory_events. Envisagez également le niveau matériel d’Azure SQL Managed Instance Série Premium optimisée en mémoire avec des ratios plus élevés de mémoire par rapport aux vCores.

Méthodes permettant d’afficher les principales catégories d’attente

Ces méthodes sont couramment utilisées pour afficher les principales catégories de types d’attentes :

  • Utilisez le Magasin des requêtes pour avoir des statistiques sur les attentes par requête dans le temps. Dans le magasin des requêtes, les types d’attentes sont combinés en catégories d’attentes. Vous pouvez consulter une table de mappage des catégories d’attentes avec les types d’attentes dans sys.query_store_wait_stats.
  • Utilisez sys.dm_os_wait_stats pour retourner des informations sur toutes les attentes rencontrées par les threads exécutés pendant une opération de requête. Vous pouvez utiliser cette vue agrégée pour diagnostiquer des problèmes de performances avec l’Azure SQL Managed Instance ou l’instance SQL Server. Les requêtes peuvent subir des attentes sur les ressources, des attentes sur la file d’attente ou des attentes externes.
  • Utilisez sys.dm_os_waiting_tasks pour retourner des informations sur la file d’attente des tâches qui sont en attente de certaines ressources.

Dans les scénarios d’utilisation intensive du processeur, le Magasin des requêtes et les statistiques d’attente ne reflètent pas forcément l’utilisation du processeur dans les cas suivants :

  • Des requêtes grandes consommatrices de ressources processeur sont toujours en cours d’exécution.
  • Les requêtes consommant beaucoup de ressources processeur étaient en cours d’exécution au moment d’un basculement.

Les vues de gestion dynamiques qui font le suivi des données du Magasin des requêtes et des statistiques d’attente affichent des résultats uniquement pour les requêtes terminées avec succès et les requêtes ayant expiré. Elles n’affichent pas de données ayant trait aux instructions en cours d’exécution tant que ces instructions ne sont pas terminées. Utilisez la vue de gestion dynamique sys.dm_exec_requests pour suivre les requêtes en cours d’exécution et la durée du worker associé.

Conseil

Outils supplémentaires :

Étapes suivantes