[QUOTE]Bonjour,
J'ai vu passer pas mal de discussions sur le sujet sur ce forum et sur d'autres forums... et je n'arrive pas à me faire une conviction forte sur le sujet.

un article sur le sujet
on peut y voir
In fact, there is basically no downside to using this option, so you may consider enabling it for every SQL Server installation.
Alors que sur une discussion sur brentozar

on peut lire
Jeremiah Peschka
September 4, 2013 4:22 pm

I can’t recommend “optimize for ad hoc workloads” as a blanket recommendation. While this can control the overall growth of the size of the plan cache, it doesn’t control growth of the count of entries in the plan cache. Since search time in a hash table is O(n) (it grows linearly with hash table size), this can become a big problem when you have a lot of ad hoc queries and a high query volume. Basically – you can end up performing enough plan stub lookups to cause performance problems.

Compression is an EE only feature in SQL Server 2008 but is free in Standard starting with 2008R2. That’s why I mentioned “If you’re using SQL Server starting with SQL Server 2008R2…”
et un peu plus loin


Jeremiah Peschka
September 10, 2013 8:32 am

Good news! You already heard about it the comment Kendra replied to.

Optimize for Ad Hoc can end up causing huge wait types when you’re looking up plans when you have a sizable plan cache.

Let’s say you have a table and all reads on the table are performed on an indexed BIGINT column. Queries from the application are unparameterized and come in as fast as SQL Server can process them.

In this situation, you can end up with hundreds of thousands (if not more) plan stubs in the execution plan cache. Whenever a new query comes in, SQL Server has to check to see if that plan has ever been seen before. This lookup operation isn’t free, neither is inserting a plan or plan stub into the cache.

As the plan cache grows, you can expect the time it takes to perform a lookup or insertion to grow linearly with the cache. Busy server + optimize for ad hoc = LOLSLOW.
Dans une Ancienne discussion sur ce forum on peut y voir SQL Pro et mikedavem proposer d'utiliser
Code : Sélectionner tout - Visualiser dans une fenêtre à part
 SET PARAMETERIZATION FORCED
A la place
et dans une autre discussion sur ce forum on peut y lire janlouk dire
Voici ce que j'ai reçu de Microsoft suite à leur "offline assessment" il y a quelques jours chez nous.

Optimize for ad hoc workloads Server Configuration Option
https://docs.microsoft.com/en-us/sql...uration-option
In most cases you either benefit from enabling the setting or there’s no large difference. In rare cases you don’t benefit and need to keep the setting disabled.
et mikedavem plussoyer en disant
+1 avec Microsoft et cela fait parti de nos BPs chez nous.

Le risque est relativement faible pour la majorité des charges de travail même si on ne gagne rien au final en fonction du type de workload (purement adhoc ou non).

•Dans le cas le plus défavorable un batch est exécuté la première fois, on compile et on obtient un stub de plan d'exécution (moins de place en cache => optimisation). La 2ème fois le même batch est exécuté et relance une compilation de plan et cette fois et on obtient un vrai plan d'exécution.
•Dans le cas le plus favorable, le même batch n'est jamais exécuté une seconde fois et on reste avec notre stub de plan mais on passera par la phase de compilation quoi qu'il en soit (adhoc).
Quel est votre avis A ce sujet ?

Cordialement,

BERNARDOS