Procédure stockée WITH OPTION RECOMPILE perte de performance

Bonjour à tous,

Je suis sur une BDD SQL Server Managé Azure et depuis hier j'ai des problèmes de performance avec certaines procédures stockées.
Avant 15h00 les proc. stoc. prenaient chacune 4 à 5s et d'un seul coup les délais sont passés à 6mn.

Après plusieurs recherches, j'écarte plusieurs hypothèses:
> les index sont bien fragmentés
> les stats ont été recalculés
> pas de "corruption de donnée" qui alourdirait les requêtes

Par contre, très surprenant, en supprimant l'option RECOMPILE dans les procédures stockées je reviens à des temps d'exécution plus acceptables en moyenne 20s.
Mais j'ai malgré tout une perte significative de performance.

Pour le moment, j'ai supprimé "RECOMPILE" parce que si je le laissais j'avais des Timeouts sur la BDD.

Pour info, les procédures stockées utilisent des bloc WITH, et font de l'INSERT. Sur les IO je ne vois rien de spécial.

Est ce que quelqu'un aurait une piste pour comprendre ce qui se passe ??
Est il possible que tempdb soit full ?

RECOMPILE impose de recréer un plan de requête à chaque fois que la requête est lancée. C'est effectivement une perte de temps dans une très grande majorité de cas. De plus, il arrive souvent que l'option RECOMPILE oblige un recalcul des statistiques. Pour peu que la requête soit très complexe et les tables très grande, ce mécanisme peut prendre beaucoup de temps. Il faudrait auditer les requêtes passées et voir si le processus STATMAN est déclenché...

Vous pouvez aussi regarder les attentes avec sys.sys.dm_exec_session_wait_stats et notamment si vous avez des attentes de type WAIT_ON_SYNC_STATISTICS_REFRESH

A +

Pour autant j'ai lu beaucoup d'article que OPTION RECOMPILE évitait le sniffing de paramètre et donc de recréer des plans inutilement et que c'est pour cette raison que les procédures avec cette option sont plus rapides.
Ou bien j'ai mal compris :( !

Voici quelques stats' (en pièce jointe)que j'ai réussi à trouver avec quelques requêtes préco par Microsoft.
Mais je ne vois rien de choquant de mon côté.

Pièce jointe 620191

Alors nuance quand même voici le code dans mes headers de procédure TSQL

Code:

---

1 2 3 4 5

CREATE PROCEDURE pc_test @dtDay DATETIME WITH RECOMPILE AS

---

Sur cette option j'ai rien touché pour le moment ...

Par contre dans les procédures stockées, j'ai un SELECT assez complexe ou je fini par

Code:

---

OPTION (RECOMPILE)

---

C'est celui ci que j'ai mis en commentaire, et depuis plus de timeout, mais toujours pas les mêmes performances qu'auparavant.

Il faudrait le plan de requête, la requête et la structure des tables et index

pour information, les plans évoluent en fonction de différents paramètres tels que la volumétrie et la distribution des données (cardinalité). Traiter 2000000 clients à Paris ne générera pas le même plan que traiter le seul client de Trifouilly-lès-Argonzettes.

A +

Vous avez aussi la solution Query Store, qui permet de garder le bon plan d'exécution et ainsi d'eviter le parametre sniffing
Vous pouvez aussi OPTMIZE for @valeurdonné qui peut vous dépanner quand vous utilisez une variable bien précise :

https://www.youtube.com/watch?v=XaTPt_ep2Es


Sinon bcp moins connu vous pouvez utiliser les sous procedures stockées, j'en parle aussi dans cette vidéo


https://www.youtube.com/watch?v=2iBP0uAhnjM