I've read lots of articles about this setting and when it should be turned on. Some articles say it should always be turned on and others (like Pinal Dave) say to only turn it on if ad-hoc workloads consume greater than 20-30% of your plan cache. I checked my database servers and ad-hoc workloads only consume 1% of most of the plan caches. But I've yet to read anything that indicates that there is a downside to having it on. I turned it on for my databases way back in 2012 and hadn't really thought about it since. Is it possible that it is having a negative impact on performance since ad-hoc represents such a small portion of my workloads?
Probably not - if I recall the documentation, what happens is the first time the plan is called only a stub is created in the cache. The second time the full plan will be cached and utilized.
This may lead to a small issue because the plan is not cached until the second time it is called but I can't see how that would have any significant impact.
Ah. So if your non-adhoc workload had low average uses (say 2) then it could have an impact but if they have average uses of 10000 or something (in my case 86000), the creation of a stub the first time is pretty irrelevant. Make sense. Thanks!