Problème avec l'option (recompile) : non efficace

Bonjour à tous,

voici mon probléme :

J'ai une requete sql sur une base de Prod d'un client. Pour info, cette requête comporte des fonctions imbriquées.

Cette requete, en "mode rapide" prend entre 4 à 10 secondes, et 7 minutes en "mode long" !

Après différents tests, vérifications des parametres bdd (flag TSQL_SCALAR_UDF_INLINING est bien activé), comparaison des plans d'exécutions "rapide" vs "long", je me rends compte qu'en vidant le cache des plans d'exec via SSMS (commande "DBCC FREEPROCCACHE") => je suis en "mode rapide"...
au fil des heures, la requête revient en "mode long", et je dois (re)faire à nouveau un DBCC pour vider le cache des plans d'exec et revenir à une situation "mode rapide".

Afin d'avoir une solution "pérenne" et ne pas avoir besoin de vider les caches chaque jour à la main, j'ai testé en ajoutant "OPTION (RECOMPILE)" en fin de ma requête.
=> problème : ce OPTION (RECOMPILE) n'a pas l'effet espéré, je reste en "mode long" malgré son ajout !?

Questions :
- Savez vous pourquoi ? (j'imagine une histoire de plan d’exécution non recalculé dans les fonctions imbriquées...).
- connaissez vous une commande / option + forte que "OPTION (RECOMPILE)" pour ne pas avoir besoin de faire un dbcc et que ma requete reste en "mode rapide" ?


Informations complémentaire :
- SQL Server 2019 / 8 processeurs
- les aspects "infra" sont écartés : j'ai une base de Test sur la même instance, ma requête est toujours en "mode rapide" alors qu'en même temps sur la PROD on est en "mode lent" (on peut écarter les pistes DD, CPU, surcharge, RAM, ..)

Merci d'avance pour vos idées, pistes, réponses ..... :)
Nicolas

Utilisez le Query Store et fixez le plan adéquat. En 2022 ceci est automatique.

A +

Actuellement, le Query store n'est pas activé chez nous (il faudrait au passage que je creuse cette fonctionnalité).
La requête en question est une requête avec passage de paramètres, je ne pense donc pas qu'il y ait un "plan adéquat" à fixer, cela variant en fonction du passage de param non ?

Oui et non... Et pour le oui, il vaut mieux un plan pas parfaitement adéquat mais rapide, qu'un plan lent, parfois adéquat...

A +

Merci pour ces retours.
Si on ne souhaite pas utiliser le magasin de requêtes, y aurait-il une autre solution ?

Voir à utiliser un OPTIMIZE for.... Mais sans la requête et les différents plans, difficile de vous aiguiller plus en avant.

A +