Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

Le cache des plans d'exécution des procédures stockées disparait


Sujet :

Développement SQL Server

  1. #1
    Nouveau Candidat au Club
    Le cache des plans d'exécution des procédures stockées disparait
    Bonjour,

    En analysant les performances d'un traitement bien précis sur mon ERP, je me suis rendu compte de variations importantes dans les performances.
    J'ai identifié que le cache des plans d'exécutions des procédures stockées se supprimait. Dès lors, le traitement suivant, au lieu de profiter du cache, devait relancer le calcul d'optimisation et forcément, c'est coûteux…
    Plus précisément j’ai identifié que seuls les plans de type « proc » et « trigger » disparaissaient (sys.dm_exec_cached_plans.objtype). la suppression peut avoir lieu dans les quelques secondes qui suivent la mise en cache.
    Les autres plans (« Adhoc », « Prepared ») sont conservés dans le cache.

    Je cherche donc à comprendre pourquoi et surtout à remédier au problème.

    Pour information :
    • Il n’y a pas de redémarrages intempestifs des serveurs
    • Pas d’options de serveurs mises à jour (surtout à intervalles si rapprochés, quelques secondes parfois)
    • Bien sur pas de RECOMPILE dans les procédures stockées
    • Pas de vidage violent du cache, type FREEPROCCACHE

    Nous avons pensé à des problèmes de mémoire, mais tout ça est très vague. Nous avons modifié la part de mémoire accordée à l’OS. Sur un serveur disposant de 64GB, nous avons tenté de laisser 4GB à l’OS puis un peu plus (10%, soit 6.4GB). Cela n’a eu aucun effet.

    Le profiler, activé au bon moment sur les bons évènements, il me semble (sp:cacheremove et sp:cacheinsert), montre bien la mise en cache des plans lorsque je déclenche un traitement faisant appel à des procédures stockées mais les quelques sp:cacheremove qui ressortent ne correspondent pas aux suppressions des plans des procédures).

    Je suis donc à la recherche de pistes : requêtes, outils de diagnostic, idée géniale, …pour comprendre pourquoi SQL Server choisit délibérément de vider une partie du cache.
    Par avance, merci de votre aide.

  2. #2
    Rédacteur

    La question est : quel est le code qui est lancé par cet ERP ? Comment envoient-ils leurs requêtes ? Quel langage/framework est utilisé ?
    Certains programmes ont un code particulier qui par son obsolescence ou ses mauvais pratiques peut engendrer un tel comportement.

    A +
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  3. #3
    Nouveau Candidat au Club
    Après vérification, le problème n'a pas lieu uniquement lorsque je déclenche l'appel depuis l'ERP.
    L'exécution de la procédure stockée depuis SSMS a le même effet.

    Après lecture de https://blog.developpez.com/babaluga/ j'ai découvert que l'option "optimize for ad hoc workloads" n'était pas active sur nos serveurs. Près de 2Go de données en cache concernaient des requêtes ad-hoc (utilisées 1 seule fois) et qu'il nous faudrait abolir...

    J'ai vidé le cache complètement, modifié la configuration serveur. Pour l'instant, c'est mieux (les plans des procédures restent en cache) mais je ne sais pas trop à quoi m'en tenir car la taille des plans ad-hoc utilisés 1 seule fois recommence à croître rapidement. Je constate en effet que je continue à avoir beaucoup de ad-hoc utilisé une fois qui sont encore de type "Compiled Plan" au lieu de "Compiled Plan Stub". Cela concerne les SELECT. Les INSERT/UPDATE passent tous en "Compiled Plan Stub". Sauriez-vous SVP m'expliquer pourquoi ?

  4. #4
    Nouveau Candidat au Club
    Pour info, voici malgré tout une synthèse des appels C# (4.7) depuis l'ERP

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
     
     
    ...
    using (var connection = OpenConnection())
                {
                    using (var cmd = connection.CreateCommand())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = storedProcedure;
                        cmd.CommandTimeout = timeoutSeconds;
     
     
     
                        ConstruireParametres(cmd, parameters);
     
                        var paramatersCollection = (SqlParameterCollection)cmd.Parameters;
     
     
     
                        if (parametersOutput != null)
                        {
                            foreach (var param in parametersOutput)
                            {
                                SqlParameter p;
     
     
                               ...
     
                                p.Direction = ParameterDirection.InputOutput;
                            }
                        }
     
     
     
                        if (parametersReturn != null)
                        {
                            foreach (var param in parametersReturn)
                            {
     
                                ...
     
                                p.Direction = ParameterDirection.ReturnValue;
                            }
                        }
     
     
     
                        var sw = Stopwatch.StartNew();
                        result = cmd.ExecuteNonQuery();
     
    ...

  5. #5
    Rédacteur

    Citation Envoyé par octave_sql Voir le message
    Après lecture de https://blog.developpez.com/babaluga/ j'ai découvert que l'option "optimize for ad hoc workloads" n'était pas active sur nos serveurs. Près de 2Go de données en cache concernaient des requêtes ad-hoc (utilisées 1 seule fois) et qu'il nous faudrait abolir...
    Je suppose que vous avez utilisé cette requête là :

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    SELECT usecounts AS NumOfExecutions,cacheobjtype,objtype AS ObjectType,size_in_bytes AS PlanSize,
           SUM(size_in_bytes / 1024.0) OVER() AS TOTAL_KB_CACHE
    FROM   sys.dm_exec_cached_plans
    WHERE  usecounts = 1;


    J'ai vidé le cache complètement, modifié la configuration serveur. Pour l'instant, c'est mieux (les plans des procédures restent en cache) mais je ne sais pas trop à quoi m'en tenir car la taille des plans ad-hoc utilisés 1 seule fois recommence à croître rapidement. Je constate en effet que je continue à avoir beaucoup de ad-hoc utilisé une fois qui sont encore de type "Compiled Plan" au lieu de "Compiled Plan Stub". Cela concerne les SELECT. Les INSERT/UPDATE passent tous en "Compiled Plan Stub". Sauriez-vous SVP m'expliquer pourquoi ?
    Il est possible que vous n'ayez pas assez de cache (RAM) et que vos plan dégagent du cache assez rapidement.

    Que disent les informations suivantes :

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT CAST(
            (
              SELECT CAST (cntr_value AS BIGINT)
              FROM sys.dm_os_performance_counters  
              WHERE counter_name = 'Buffer cache hit ratio'
            )* 100.00
            /
            (
              SELECT CAST (cntr_value AS BIGINT)
              FROM sys.dm_os_performance_counters  
              WHERE counter_name = 'Buffer cache hit ratio base'
            ) AS NUMERIC(6,3)
          ) AS [Cache Hit Ratio]


    et

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    SELECT @@SERVERNAME AS [Server Name], [object_name], instance_name, cntr_value AS [Page Life Expectancy]
    FROM sys.dm_os_performance_counters WITH (NOLOCK)
    WHERE [object_name] LIKE N'%Buffer Node%' -- Handles named instances
    AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);


    Quelle est la taille de la RAM, la RAM attribuée au serveur SQL et la taille de la base ?

    A +
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  6. #6
    Modérateur

    Bonjour

    Citation Envoyé par octave_sql Voir le message
    Après vérification, le problème n'a pas lieu uniquement lorsque je déclenche l'appel depuis l'ERP.
    L'exécution de la procédure stockée depuis SSMS a le même effet.
    Certains paramètres de session doivent être identiques pour que les plans en cache puissent être réutilisés...
    il est fort probable que certains de ces paramètres différent entre les appels depuis le C# et depuis SSMS, ça expliquerait que les plans ne soit pas réutilisés.

  7. #7
    Nouveau Candidat au Club
    Citation Envoyé par SQLpro Voir le message
    Je suppose que vous avez utilisé cette requête là :

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    SELECT usecounts AS NumOfExecutions,cacheobjtype,objtype AS ObjectType,size_in_bytes AS PlanSize,
           SUM(size_in_bytes / 1024.0) OVER() AS TOTAL_KB_CACHE
    FROM   sys.dm_exec_cached_plans
    WHERE  usecounts = 1;

    Oui


    Citation Envoyé par SQLpro Voir le message

    Il est possible que vous n'ayez pas assez de cache (RAM) et que vos plan dégagent du cache assez rapidement.

    Que disent les informations suivantes :

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT CAST(
            (
              SELECT CAST (cntr_value AS BIGINT)
              FROM sys.dm_os_performance_counters  
              WHERE counter_name = 'Buffer cache hit ratio'
            )* 100.00
            /
            (
              SELECT CAST (cntr_value AS BIGINT)
              FROM sys.dm_os_performance_counters  
              WHERE counter_name = 'Buffer cache hit ratio base'
            ) AS NUMERIC(6,3)
          ) AS [Cache Hit Ratio]


    99.969%

    Citation Envoyé par SQLpro Voir le message

    et

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    SELECT @@SERVERNAME AS [Server Name], [object_name], instance_name, cntr_value AS [Page Life Expectancy]
    FROM sys.dm_os_performance_counters WITH (NOLOCK)
    WHERE [object_name] LIKE N'%Buffer Node%' -- Handles named instances
    AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);


    La durée de vie augmente au fil du temps. A un instant t j'en suis à :
    instance_name =000
    Page Life Expectancy = 813

    Citation Envoyé par SQLpro Voir le message

    Quelle est la taille de la RAM, la RAM attribuée au serveur SQL et la taille de la base ?
    64GO, dont 10% pour l'OS. J'avais le même problème en réservant juste 4GO pour l'OS.

    Il y a actuellement 2 bases d'environ 300 GO chacune


    Je rajoute ces requêtes issues de https://blog.developpez.com/elsuket/...ife-expectancy
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
     
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    GO
     
    DECLARE @max_server_memory_MB int
            , @memory_node_count int
            , @node_ple_threshold int
     
    -- Lecture de la valeur configurée pour l'option max server memory (MB)
    SELECT  @max_server_memory_MB = CAST(value AS int)
    FROM    sys.configurations
    WHERE   name = 'max server memory (MB)'
     
    -- Lecture du nombre de noeuds NUMA
    SELECT  @memory_node_count = COUNT(*)
    FROM    sys.dm_os_memory_nodes
    WHERE   memory_node_id  64 -- le noeud 64 est pour la fonctionnalité Dedicated Administrator Connection
     
    -- Application de la règle de trois au seuil édité par Microsoft en 2006
    SELECT  @node_ple_threshold = ((@max_server_memory_MB / @memory_node_count) * 300) / 8192
     
    -- Affichage des valeurs obtenues
    SELECT  @max_server_memory_MB AS max_server_memory_MB
            , @memory_node_count AS memory_node_count
            , @node_ple_threshold AS NUMA_node_PLE_threshold
     
    -- Exposition de la PLE pour chaque noeud NUMA
    SELECT          S.parent_node_id
                    , PC.cntr_value AS node_PLE
                    , PC.cntr_value / @node_ple_threshold AS min_PLE_factor
                    , CASE WHEN PC.cntr_value <= @node_ple_threshold THEN 'KO' ELSE 'OK' END AS node_PLE_state
    FROM            sys.dm_exec_requests AS R
    INNER JOIN      sys.dm_os_schedulers AS S
                            ON R.scheduler_id = S.scheduler_id
    INNER JOIN      sys.dm_os_performance_counters AS PC
                            ON PC.instance_name = REPLICATE('0', 3 - LEN(S.parent_node_id)) + CAST(S.parent_node_id AS varchar(10))
    WHERE           R.command = 'LAZY WRITER' -- Il existe un processus Lazywriter par noeud NUMA
    AND             PC.counter_name = 'Page life expectancy'


    J'obtiens :

  8. #8
    Rédacteur

    To page life expectansy fallait le passer avant de redémarré. Il n'a aucun sens avant plusieurs jours.... Néanmoins, je dirais que tu n'as pas de problème de cache.

    A +
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  9. #9
    Nouveau Candidat au Club
    Il n'y a eu aucun redémarrage...

  10. #10
    Rédacteur

    Tu as dit :
    J'ai vidé le cache complètement,

    Ce qui correpond à un arrêt et donc PLE est plsu du tout consistant !

    A +
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  11. #11
    Nouveau Candidat au Club
    Oui, j'ai vidé le cache le 19 octobre après avoir modifié la config' serveur (optimize for ad hoc workloads) > Notamment pour supprimer le cache des requête adhoc...
    La mesure de la PLE date de ce matin (26 octobre).

###raw>template_hook.ano_emploi###