I used to believe this option was something almost absolute: it should be enabled as best practice. I was in some way happy when all my demos for SQL Server 2022 started to fail, and I discovered it was because this option was enabled by default.
This weekend I attended a technical session which caught my attention to many blog posts stating the opposite. Here are some of them:
https://erikdarling.com/no-really-dont-optimize-for-ad-hoc-workloads-as-a-best-practice/ https://bornsql.ca/blog/dont-optimize-for-ad-hoc-workloads-as-a-best-practice/
Let’s analyse these scenarios in more details. But if you are arriving now, you can take a look on my blog about Ad Hoc Server configuration from