When you turn the "Optimize for Ad Hoc Workloads" option on, you will cause ad-hoc queries that are run the 2nd time to be just as slow as the 1st, because you will be Compiling an Execution Plan and pulling the same Data (without it cached) those first 2 times.
This may not be a big deal, but you'll notice it when testing queries.
So what happens now, without this option turned on and a cache full of 1-Off Ad-Hoc queries?
The Caching Management Algorithm:
As this Optimization feature was introduced, the caching management algorithm was also updated.
Kimberly Tripp's article also references Kalen Delaney's post about this algorithm change.
She explains it best:
The change actually computes a plan cache size at which SQL Server
recognizes that there is memory pressure, and it will start removing
plans from cache. The plans to be removed are the cheap plans that
have not been reused, and this is a GOOD THING.
This means those pesky one-timer plans will be the first to go when you need to free up resources.
So now the question becomes:
"Why do we NEED 'Optimize for Ad Hoc Workloads' when SQL Server takes care of removing unused plans when necessary?"
My answer to that is, if you regularly have an s-ton of dynamic-sql generating oodles of non-parameterized ad-hoc queries, then it makes perfect sense to turn this feature on.
You want to avoid placing a strain on system resources, such that it forces cached-plan/data removal after you've used up the max cache memory space.
How do I know when I need to turn this on?
Here's a query I wrote to show you how many Ad-Hoc Plans you currently have cached and how much disk-space they're eating up (results will change throughout the day - so test it during a time of heavy load):
--Great query for making the argument to use "Optimize for Ad Hoc Workloads":
SELECT S.CacheType, S.Avg_Use, S.Avg_Multi_Use,
S.Total_Plan_3orMore_Use, S.Total_Plan_2_Use, S.Total_Plan_1_Use, S.Total_Plan,
CAST( (S.Total_Plan_1_Use * 1.0 / S.Total_Plan) as Decimal(18,2) )[Pct_Plan_1_Use],
CAST( (S.Total_MB_1_Use * 1.0 / S.Total_MB ) as Decimal(18,2) )[Pct_MB_1_Use]
SUM(CASE WHEN CP.usecounts > 2 THEN 1 ELSE 0 END)[Total_Plan_3orMore_Use],
SUM(CASE WHEN CP.usecounts = 2 THEN 1 ELSE 0 END)[Total_Plan_2_Use],
SUM(CASE WHEN CP.usecounts = 1 THEN 1 ELSE 0 END)[Total_Plan_1_Use],
CAST((SUM(CP.size_in_bytes * 1.0) / 1024 / 1024) as Decimal(12,2) )[Total_MB],
CAST((SUM(CASE WHEN CP.usecounts = 1 THEN (CP.size_in_bytes * 1.0) ELSE 0 END)
/ 1024 / 1024) as Decimal(18,2) )[Total_MB_1_Use],
CAST(AVG(CP.usecounts * 1.0) as Decimal(12,2))[Avg_Use],
CAST(AVG(CASE WHEN CP.usecounts > 1 THEN (CP.usecounts * 1.0)
ELSE NULL END) as Decimal(12,2))[Avg_Multi_Use]
FROM sys.dm_exec_cached_plans as CP
GROUP BY CP.objtype
) AS S
ORDER BY S.CacheType
I'm not gonna say, "When you have X MB's" or "If X% of your Ad Hoc are Single-Use" to turn this on.
It doesn't affect Sprocs, Triggers, Views or Parameterized/Prepared SQL - just the Ad-Hoc queries.
My personal recommendation is to just turn in on in your Prod Environment, but consider leaving it off in your Dev Environment.
I say this only for Dev, because if you're optimizing a query that takes a minute or more to run, then you don't want to run it 3 times before you may see how fast it will go with with it cached - every single time you edit it to find the best optimization design.
If your job doesn't involve doing this all day, then go nuts and ask your DBA to turn it on everywhere.