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 some years ago.

Analysing the scenarios

They are not wrong on their statements by themselves. But there may be conceptual scenarios where they apply, and others where they don’t.

One of the main claims is that when the same query is sent with different values, it is understood as two ad hoc queries. The DBA can’t see their plan to make any optimization, creating a problem.

Let’s analyze this problem from different points of view.

This only happens if the query is sent by tools such as SSMS or similar. If the query is sent by an application, most SQL Server access frameworks parameterize the query.

In production environments, queries from SSMS or similar are ad hoc queries, unless someone is doing something very wrong.

This brings us to an interesting realization:

For production environments this setting may continue to be a best practice, but for analytical environments, not much.

What is described on the blogs above would happen in analytical environments, but it would be way rarer in production environments.

 

Let’s consider Some Exceptions

However, after talking with some fellow DBA’s, I discovered some exceptions. A company using a custom developed framework for database access, for example.

First, I would say these exceptions would be in a huge risk of suffering SQL Injection attacks. Are they really protected?

Let’s say they are. The company took all the precautions needed against this risk. Even so, a custom framework preventing query parameterization is a big technical debt.

Some could ask: Why? Only because the bloating of the query plan cache? What if there is enough memory and this is not a big deal for them?

Query Store and the Ad Hoc Queries

The big deal is that they are not using query store. They couldn’t even use it, because each query would be recognized as a different one, bloating query store, affecting database performance and resulting in nothing.

Parameterization Configuration

In order to solve these exceptional scenarios, I would strongly recommend the use of parameterization forced configuration.

This is a configuration on database level which would lower the restrictions SQL Server has when parameterizing queries. When the Parameterization configuration is set to Simple, very few queries are parameterized, usually the ones searching over the primary key.

When the Parameterization configuration is set to Forced, most queries are parameterized, independent of how they are sent to the server. The application may not be parameterizing, but the server will.

To change this configuration, you can run the following statement:

ALTER DATABASE AdventureWorks2012 SET PARAMETERIZATION FORCED

There is always the risk to parameterize something which shouldn’t be parameterized and as a result increase the amount of parameter sniffing problems. However, if the applications are already parameterizing the queries, on a regular environment this option would have minimum effect.

On a side note, you can read more about parameter sniffing on my article from some years ago.

However, on the exceptions described, the parameterization forced seems to be extremely recommended.

Why? Only because of the query store?

Additional Consequences of not parameterizing

Since query store was released in SQL Server 2016, each new version of SQL Server brings additional features for query optimization and many of these features are query-store-based.

SQL Server 2022 was no exception, on the opposite: It brought incredible features which are, in one way or another, depending on query store.

Let’s see what happens with these features in a scenario where the queries are not recognized as parameterized queries.

SQL Server 2022 Optimization Features

DOP Feedback: It uses query store history, and it doesn’t work if query store is disabled.  If the queries are not recognized as the same, this feature would not work.

CE Feedback: In the same way as DOP Feedback, this feature would not work.

PSP: This feature doesn’t depend on query store at all but depends on the query being identified as the same, with different literal values. If this identification fails, this feature will fail as well.

If you would like to know more about these advanced optimization features and how Query Store evolved to them, you can watch the videos of the Malta SQL Server 2022 Query Optimization Conference.

In summary, if “Optimize for ad hoc workloads” is not enabled, query store will be bloated, in the best scenario. On the worst scenario, you will be losing all these optimization opportunities, because queries with different literal values will not be recognized as one.

Summary

In summary, if you disable this option, you are in risk to bloat query store. If you have reasons to need to disable this option, you will be for sure bloating query store and losing the most modern optimizations available.

On the other hand, the points against this option are correct in relation to analytical environments, where we would not expect the same query to repeat lots of times. Different queries will be executed all the time.

Thank you to my friend Alessandro Mortola for the inspiration and help this blog