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.