Bert Wagner looks at the optimize for ad hoc workloads option in SQL Server:
Instead of filling the execution plan cache with plans that will never get reused, the optimize for ad hoc workloads option will cache a plan stub instead of the full plan. The plan stub is significantly smaller in size and is only replaced with the full execution plan when SQL Server recognizes that the same query has executed multiple times.
This reduces the amount of size one-time queries take up in t he cache, allowing more reusable plans to remain in the cache for longer periods of time.
I’ve run into several cases where this has helped SQL Server and don’t think I’ve found a scenario where it actively hurts.