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

**Nico-DBA** · 14/06/2023, 10h59

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

**SQLpro** · 14/06/2023, 11h11

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

A +

**Nico-DBA** · 14/06/2023, 11h23

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 ?