SQL Server Parameter: Optimize for Ad Hoc Workloads and Query Store
When tuning server performance, one of the most critical resources is memory (Plan Cache). Ad hoc or dynamically generated queries can create a situation where the memory buffer fills up with single-use execution plans, displacing frequently used procedures and data.
Optimize for Ad Hoc Workloads: This setting changes the logic of storing plans in memory:
-
First query: The server does not store the full plan, only a small reference (plan stub / hash).
-
Second query: When the exact same query reaches the server again, a full-size plan is generated and stored.
This protects the server in situations where the system is flooded with unique queries (typical for poorly parameterized code or certain ORMs). The downside is a risk situation: if a query is executed exactly twice, the server has to generate the plan twice, consuming additional CPU resources.
Changes in SQL Server 2016+ Versions In earlier versions, there was a concern that enabling ad hoc optimization would result in a loss of visibility over single queries. In SQL Server 2016 and newer versions, this issue has been resolved thanks to Query Store.
Query Store stores query statistics and plans regardless of whether the plan is in memory (RAM) or not. Even if the plan cache at the server level is protected from single plans, Query Store captures that data.
Why always enable "Ad Hoc" optimization today? If Query Store is active in the database, this combination is the best solution for the following reasons:
-
Memory saving: The Plan Cache remains clean of single-use garbage plans, leaving room for more valuable data.
-
Visibility is maintained: Query Store provides a complete overview even of queries where only a hash was left in memory. Analytical capabilities are not reduced.
-
Lower administrative burden: If we once had to consider CPU vs RAM costs, nowadays, with current resources, the memory saving and Query Store analytics combination is a safer choice for most workloads.
Summary If using SQL Server 2016 or newer and Query Store is enabled, it is recommended to also configure optimize for ad hoc workloads = 1. This ensures cleaner memory usage without losing diagnostic information about query performance. The setting is especially critical in systems with a lot of dynamic SQL.