Résolution des problèmes du gouverneur de ressources

Si aucune fonction définie par l'utilisateur (UDF) classifieur n'est disponible pour la classification, toutes les sessions iront alors automatiquement au groupe de charge de travail par défaut. Après avoir créé une fonction définie par l'utilisateur classifieur, vous devez vérifier qu'elle est inscrite auprès du gouverneur de ressources et que la configuration en mémoire est mise à jour.

La création, l'inscription et l'activation d'une fonction définie par l'utilisateur (UDF) classifieur est un processus en trois étapes :

• Premièrement, vous devez créer la fonction.

  CREATE FUNCTION function_name() RETURNS <something> 
  WITH SCHEMABINDING
  

• Deuxièmement, vous devez inscrire la fonction auprès du gouverneur de ressources.

  ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=schema_name.function_name)
  

• Troisièmement, vous devez mettre à jour la configuration en mémoire du gouverneur de ressources.

  ALTER RESOURCE GOVERNOR RECONFIGURE
  

La première chose que vous devez faire lors du dépannage de la classification est de vérifier que la fonction que vous avez créée est inscrite auprès du gouverneur de ressources et que la configuration a été mise à jour. Utilisez la requête suivante pour obtenir le nom du schéma (schema_name) et le nom de la fonction classifieur (function_name) pour la fonction définie par l'utilisateur classifieur que le gouverneur de ressources utilise actuellement.

USE master
SELECT 
      object_schema_name(classifier_function_id) AS [schema_name],
      object_name(classifier_function_id) AS [function_name]
FROM sys.dm_resource_governor_configuration

Vous pouvez utiliser l'approche précédente pour dépanner un scénario dans lequel vous avez modifié la fonction définie par l'utilisateur classifieur mais où le gouverneur de ressources utilise encore la logique de la fonction précédente pour classifier des sessions. Ce comportement indique que les modifications que vous avez apportées n'ont pas été appliquées à la configuration en mémoire.

Vous pouvez créer une fonction classifieur très complexe qui ne produit pas les résultats attendus ou qui consomme beaucoup de ressources. Si vous avez procédé à la résolution des problèmes de base, vous devez vérifier que la logique de la fonction est saine. Le scénario le plus pessimiste est celui dans lequel un défaut de codage génère une boucle infinie ou une perte de contrôle de requête.

Vous pouvez utiliser une connexion administrateur dédiée (DAC) pour corriger une fonction classifieur mal écrite, car une DAC n'est pas sujette à la classification et peut être utilisée alors que le gouverneur de ressources est en cours d'exécution et de classification des sessions entrantes. Pour plus d'informations, consultez Utilisation d'une connexion d'administrateur dédiée.

Vous pouvez obtenir des informations sur la fonction classifieur en interrogeant les éléments suivants :

• sys.dm_exec_query_stats. (contient des informations d'instructions, mais pas la fonction elle-même).

• sys.dm_exec_sql_text (utilisé en combinaison avec le sql_handle obtenu de sys.dm_exec_query_stats)

• Classe d'événements PreConnect:Starting (fournit l'ID et le nom de la fonction classifieur)

Dans ce scénario, un utilisateur signale une dégradation des performances et vous suspectez que le nombre de requêtes est limité.

La première chose à faire est de vérifier si la limitation du nombre de requêtes est explicitement définie pour le groupe auquel appartient l'utilisateur. Pour cela, vérifiez l'appartenance de l'utilisateur au groupe afin de déterminer si le paramètre GROUP_MAX_REQUESTS est activé. Si GROUP_MAX_REQUESTS n'est pas activé, il n'existe aucune limitation explicite du nombre de requêtes. Vous devez pousser plus loin vos investigations en exécutant les étapes suivantes.

• Interrogez sys.dm_os_waiting_tasks pour voir si toutes les demandes se conforment au temps d'attente RESMGR_THROTTLED. La présence de ce type d'attente indique la limitation du nombre de demandes.

• Démarrez l'analyseur de performances et recueillez des données à l'aide des Requêtes en attente et des compteurs de requêtes actives. Un nombre de requêtes en attente autre que zéro indique une limitation de requêtes.

• Vérifiez si la valeur de requêtes actives correspond au paramètre GROUP_MAX_REQUESTS. Si la valeur de requêtes actives est plus élevée que le paramètre GROUP_MAX_REQUESTS, le groupe peut avoir des requêtes qui ne peuvent pas être limitées (par exemple, des transactions ouvertes).

• Si les requêtes en attente ont une valeur égale à zéro, vérifiez les requêtes actives de tous les groupes de charges de travail qui partagent le même pool de ressources parce que le pool a pu être surchargé avec trop de requêtes.

Si vous avez une stratégie qui repose sur la génération d'événement du gouverneur de ressources, vous pouvez utiliser l'événement généré lorsque la limite maximale de l'UC est atteinte.

Dans ce scénario, vous souhaitez déterminer si la limite maximale de l'UC (REQUEST_MAX_CPU_TIME_SEC) que vous avez configurée pour détecter une requête qui utilise trop de ressources processeur est trop basse.

Les actions suivantes vous aideront à valider le paramètre de limite de l'UC.

• Démarrez une session SQL Trace et collectez l'événement CPU Threshold Exceeded. Lorsqu'une requête utilisateur atteint la limite d'utilisation maximale de l'UC, le serveur génère automatiquement un événement SQL Trace. Si votre paramètre est trop bas, un nombre élevé de ces événements sera généré.

• Démarrez l'analyseur de performances et collectez des données à l'aide du compteur Temps proc. max. par requête (ms). Vous pouvez utiliser la valeur de ce compteur comme indicateur afin de définir la limite appropriée pour le groupe de charge de travail.

Dans ce scénario, vous suspectez que la bande passante de l'UC est limitée parce que le compteur de performance % d'utilisation de l'UC se situe au niveau du paramètre MAX_CPU_PERCENT du gouverneur de ressources, ou tout près de celui-ci. La requête suivante retourne la valeur de % d'utilisation de l'UC pour tous les groupes de charges de travail et les pools de ressources pour une instance SQL Server.

select * from sys.dm_os_performance_counters where counter_name = 'cpu usage %'

Pour plus d'informations, consultez sys.dm_os_performance_counters (Transact-SQL).

Vous pouvez déterminer si la bande passante de l'UC est limitée en effectuant les contrôles suivants sur votre système.

• Vérifiez l'utilisation totale de l'UC du serveur. Si une charge autre que SQL Server est actuellement active, elle peut affecter la requête que vous dépannez.

• Vérifiez la distribution d'utilisation de l'UC entre les pools de ressources. Un pool de ressources pourrait être limité parce qu'un autre pool a une valeur minimale haute configurée pour l'utilisation de l'UC. Comparez les compteurs pour l'utilisation de l'unité centrale calculée attendue par rapport à l'utilisation réelle de l'UC.

• Vérifiez les groupes de charges de travail affectés au pool de ressources en question. Le fait de charger d'autres groupes de charges de travail peut affecter les utilisateurs qui partagent le même pool.

• Vérifiez la distribution d'utilisation de l'UC entre les planificateurs. La requête sur laquelle vous enquêtez peut être placée sur un planificateur qui contient des requêtes longues. Dans ce cas, la requête peut sembler être limitée, mais le problème réel provient d'une distribution inégale de la charge entre les planificateurs.

• Vérifiez les cas possibles où la charge de travail peut être bloquée par d'autres sessions au lieu d'être limitée par les paramètres du gouverneur de ressources.

• Vérifiez le nombre de sessions actuellement en cours d'exécution sur le système. À mesure que le nombre de requêtes en cours d'exécution simultanées augmente, SQL Server essaie de s'assurer que toutes reçoivent au moins une certaine quantité de temps processeur pour empêcher toute privation en termes de ressources UC.

Dans ce scénario, vous suspectez que la taille de l'allocation de mémoire est à l'origine d'une exécution au ralenti d'une requête.

Le gouverneur de ressources applique la limite de mémoire de requête maximale en réduisant l'allocation de mémoire afin que les requêtes volumineuses puissent tenir dans la limite. Si une requête obtient moins de 100 % de l'allocation de mémoire, il peut être nécessaire de vider les données temporaires et de les écrire sur disque, opération qui peut avoir un effet notable sur les performances.

Vous devez déterminer le pourcentage de requêtes volumineuses afin de définir la limite de taille de requête maximale appropriée. Les actions suivantes vous aideront à déterminer les meilleurs paramètres :

• Interrogez sys.dm_exec_query_memory_grants pour connaître l'état actuel des allocations de mémoire. La colonne ideal_memory_kb affiche la quantité idéale selon l'estimation de la cardinalité. La colonne requested_memory_kb affiche la quantité de la demande qui aurait pu être réduite après avoir atteint la limite de requête maximale. Si requested_memory_kb est sensiblement inférieur à ideal_memory_kb, la requête peut alors finir par se répandre fréquemment (à supposer que l'évaluation de la cardinalité soit correcte).

• Démarrez l'analyseur de performances et collectez des données à l'aide du compteur Nombre de requêtes à mémoire réduite/s. La valeur de ce compteur représente le taux du nombre d'allocations de mémoire qui ont reçu moins que la quantité idéale après avoir atteint la limite de taille maximale de la requête. Les grandes requêtes peuvent s'exécuter beaucoup plus lentement que celles qui disposent de la quantité idéale parce qu'elles ont besoin de déborder sur le disque pour rester dans les limites de la mémoire.

Pour atténuer un problème d'allocation de mémoire, vous devrez peut-être augmenter la limite de taille de pool ou la taille limite de la mémoire maximale.

Dans ce scénario, une requête échoue avec une erreur de délai d'attente de l'allocation de mémoire.

Le nombre total de demandes d'allocations de mémoire actives et les limites de mémoire spécifiées dans les définitions du pool de ressources et du groupe de charge de travail peuvent jouer un rôle dans les délais d'expiration d'allocation de mémoire. Si un pool de ressources unique est partagé par plusieurs groupes de ressources, le nombre de requêtes simultanées dans d'autres groupes peut également affecter les délais d'expiration de l'allocation de mémoire.

Les actions suivantes vous aideront à déterminer les meilleurs paramètres du pool de ressources :

• Interrogez sys.dm_exec_query_memory_grants pour connaître le nombre d'allocations de mémoire et de requêtes en attente sur ce groupe et ce pool.

• Interrogez sys.dm_exec_query_resource_semaphores pour connaître le total de mémoire allouée et de cibles.

Si l'utilisation de la mémoire allouée est supérieure à l'espace de mémoire disponible, vous pouvez envisager d'augmenter la taille limite du pool de ressources.

Une requête échoue avec une erreur de mémoire insuffisante.

Dépannage de base

Dépannage avancé

Dans ce scénario, vous suspectez qu'une requête s'exécute lentement à cause d'un plan de requête non optimal. L'optimiseur de requête peut générer un plan de requête non optimal planifie s'il ne reçoit pas assez de mémoire à cause d'un paramètre de limite basse de la mémoire pour un pool de ressources.

Les actions suivantes vous aideront à déterminer les meilleurs paramètres du pool de ressources :

• Obtenez des données du compteur Optimisations de requêtes/s afin de déterminer si le groupe de charge de travail a un nombre élevé de compilations de requêtes.

• Obtenez des données du compteur Plans non optimaux/s afin de déterminer si l'optimiseur de requête génère fréquemment des plans non optimaux.

Si l'une ou l'autre des conditions précédentes existe, pensez à augmenter la limite de mémoire du pool de ressources.