First of all, let's see, what is 'optimize for ad hoc workloads' in SQL Server?
In simple words, the term 'ad hoc' means 'as needed' or a single-use query plan. The Plan that generated for a specific query and never used again. Single-use queries or dynamically created queries, it's an 'ad hoc" part of the workload.
That can cause high memory consumption, high compilations, high CPU, and so on. Exactly for these cases, we have a 'optimize for ad hoc workloads' option.
When this option turned on, the DB Engine stores only a small plan stub in the plan cache on the first execution. That instead of a full compiled plan - because it's 'as needed' query. At the next runs, the engine will use this stub to recognize that this batch has compiled before. The full compiled plan will take place of the stub in the plan cache.
Now, "I have learned today" part:
When we turn this option on, it's starting work immediately. No restart required, no plan cache flushes. Awesome, but there's a dark side - it affects only new plans!
You will ask why that a dark side? When we touch an option that affects a Plan Cache or plan creation, the expected for some behavior of the system. In this case, is a clean start.
I'm expecting some action on existing plans. Like: Plan Cache flushing, flushing of old ad-hock plans with count 1 or at least marking them with some Stub too. But, plans that are already in the plan cache stay untouched and become trash. That until we cleared procedure cache or restart the SQL Server service.
I know people and I'm one of them, that avoided using this on busy servers. Because of fear to flush Plan Cache, not during maintenance hours, and wait with the fix.
Bug or feature, decide yourself, but very important to know this in case we try to optimize for ad hoc workloads.
-----------------------------
More info: Microsoft Docs