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.
Which means that by turning this option on, you will cause ad-hoc queries that are run the 2nd time to be just as slow as the 1st.
This may not be a big deal at first, but still more noticeable.
Especially when you are developing and you run a query for 1min, then you run again to see how fast it will run with a query plan, only to have to wait another 1min, and then have to run it a 3rd time.
Imagine repeating that ad-nauseum.
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 have an s-ton of dynamic sql generating oodles of non-parameterized ad-hoc queries running in a loop, then it makes perfect sense to turn this feature on - but only temporarily.
I have a crazy stored procedure that searches the entire database for a GUID (and another sproc that searches char/text fields).
I use it once every blue moon.
The 3rd party database we use has about 8,000 tables (no joke) and you never know where the 3rd party app that uses this database will store it's data (sql-trace is not always an option).
So as I dynamically loop through 8,000 tables (times the number of Guid fields found in each table) I am hammering the Cache plan and squeezing out all those single-use (and probably multi-use) plans from memory.
This is the only time when it makes sense to turn on - again, only temporarily till my sproc completes.