Ad hoc workloads is there to help with plan cache pollution. If you have multiple single use queries, a stub is saved instead of the entire plan. If a query comes along again and thinks the plan stub is appropriate, then the query will cause the full plan associated to that stub to be stored in the plan cache.

If you have a ton of one off types of queries - this can be helpful. The downside is that you may start seeing parameter sniffing and longer running queries.

If you decide to enable it - be prepared to disable it if performance starts degrading. And of course, the best option is to test it in a suitable environment first.

Jason AKA CirqueDeSQLeilI have given a name to my pain...MCM SQL Server, MVPSQL RNNR

SQLRNNR (2/5/2013)If you have a ton of one off types of queries - this can be helpful. The downside is that you may start seeing parameter sniffing and longer running queries.

Optimise for ad-hoc can't cause parameter sniffing where there was none before. Forced parametrisation can, but that's a whole nother matter.Optimise for ad-hoc just means that an ad-hoc batch's plan is only cached the second time it's seen, not the first. It can cause higher compilations in cases where identical ad-hoc queries often run 2 or 3 times.

SQLRNNR (2/5/2013)If you have a ton of one off types of queries - this can be helpful. The downside is that you may start seeing parameter sniffing and longer running queries.

Optimise for ad-hoc can't cause parameter sniffing where there was none before.

I'll agree on that. Sometimes though the param sniffing isn't made evident until adhoc workload is enabled. Could be a coincidence and I haven't dived deeply into it. I just ran into a case in the last two weeks and then ran into another DBA last week that experienced the same thing.

Jason AKA CirqueDeSQLeilI have given a name to my pain...MCM SQL Server, MVPSQL RNNR

I read a tutorial and recommend this option should be enabled for all instances. Read and still a little confusing about when I should enable or disable it.

I will dig more.

Cite that article please.

I wouldn't agree that it is something that must be done on all instances. Test it first for your environment and then make a decision whether or not to implement it in prod - but certainly not just a blanket yes for all instances.

Jason AKA CirqueDeSQLeilI have given a name to my pain...MCM SQL Server, MVPSQL RNNR

SQLRNNR (2/5/2013)If you have a ton of one off types of queries - this can be helpful. The downside is that you may start seeing parameter sniffing and longer running queries.

Optimise for ad-hoc can't cause parameter sniffing where there was none before.

I'll agree on that. Sometimes though the param sniffing isn't made evident until adhoc workload is enabled. Could be a coincidence and I haven't dived deeply into it. I just ran into a case in the last two weeks and then ran into another DBA last week that experienced the same thing.

I'd say unrelated coincidence. Optimise for ad-hoc only works on ad-hoc queries, not procedures. Can't recall offhand whether it works on prepared statements (parameterised) or not.