Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In this article, we will examine what happens when we enable Optimize for ad hoc workloads and when we use parameterization. We will see what will happen when we use the two features together and separately.
Optimize for ad hoc workloads is a feature that is introduced with SQL Server 2008. If you enable Optimize for ad hoc workloads, it creates only a small Compiled Plan Stub for single-use queries, rather than creating the entire query plan on the plan cache. The entire Query plan is created when the query is executed second time.
Enable optimize for ad hoc workloads
We can enable this feature on sql server as follows. Queries must be executed one by one or use GO between queries.
1 2 | sp_configure 'show advanced options',1 reconfigure |
1 2 | sp_configure 'optimize for ad hoc workloads',1 reconfigure |
SQL Server is using simple parameterization by default. But you can also set to forced parameterization.
Enable Forced Parameterization
You can change the parameterization type with the help of the following query.
1 2 3 4 | USE [master] GO ALTER DATABASE [AdventureWorks2012] SET PARAMETERIZATION FORCED WITH NO_WAIT GO |
Why we Enable Forced Parameterization
Suppose application sends queries as unparameterized(ad hoc) and we cannot change the application to send the queries with parameters. In this case, we can enable forced parameterization.
Optimized For ad hoc workloads and Parameterization Scenarios
Now let’s see what happens when enabling and disabling the optimized ad hoc workloads with simple or forced parameterization.
Scenario 1: Optimize for ad hoc workloads disabled and SQL Server use simple parameterization
Clean the Cache with the following query. You should not do this in the production environment.
1 2 | DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS |
We will run our queries in the AdwentureWorks database. So we’re running the following query.
1 2 | USE AdventureWorks GO |
Let’s run the following 3 queries one by one after clearing the cache.
1 2 3 4 5 6 7 8 9 | SELECT * FROM HumanResources.Shift SELECT * FROM HumanResources.Shift where ModifiedDate='2002-06-01 00:00:00.000' SELECT * FROM HumanResources.Shift where ISNULL(ModifiedDate,'1/1/2003')>'1/1/1990' |
After running the queries above, we’re running the following query to see if the query plans of our queries were created, and also size of query plans if they were created.
1 2 3 4 5 6 7 8 9 | SELECT p.size_in_bytes,p.cacheobjtype,p.objtype,qp.query_plan,t.text FROM sys.dm_exec_cached_plans p CROSS APPLY sys.dm_exec_query_plan(p.plan_handle) qp CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t where t.text like '%HumanResources% |
While optimizing for ad hoc workloads is disabled and simple parameterization is enabled, we get a result as follows;
As you can see, all query plans have created. We only see that there is a difference in a query.
1 | SELECT * FROM HumanResources.Shift where ModifiedDate='2002-06-01 00:00:00.000' |
A query plan has been created for both the parameterized state and the unparameterized state of the above query. But for another query which have a where condition, a query plan for the parameterized state is not created while the query plan of the unparameterized state is created. This means, it could not parameterized this query.
Because we used simple parameterization, it was only able to parameterize the query which have a simple where condition. But it was unable to parameterize the other query that has a complex where condition.
That is, in some cases, simple parameterization may not be able to parameterize queries that have a complex where condition.
After making the necessary settings(clean cache,enable or disable optimize for ad hoc workloads, etc) to perform each step below, we will run the above scripts repeatedly.
Scenario 2: Optimize for ad hoc workloads enabled and SQL Server use simple parameterization
When we run the queries again, we get a result as follows.
As you can see, it only created a query plan for the query it can be parameterized. It just created the “Compiled Plan Stub” for the remaining queries.
While “Optimize for ad hoc workloads” is enabled and simple parameterization is enabled, we execute the same queries for the second time without clearing the cache and we get a result as follows. As we have seen, when we run the queries second time, we got the same result with the first scenario.
Scenario 3: Optimize for ad hoc workloads disabled and SQL Server use forced parameterization
After clearing Cache, we execute the same queries again. As you can see, all query plans were created. Unlike the first scenario, it have created a query plan for all the queries which have a where condition. Because this time it use forced parameterization.
Scenario 4: Optimize for ad hoc workloads enabled and SQL Server use forced parameterization
After clearing Cache, we execute the same queries again.
As you can see, unlike the second scenario, it has created a query plan for the parameterized states of all queries which have a where condition.
While “Optimize for ad hoc workloads” is enabled and “forced parameterization” is enabled, we execute the same queries second time without clearing the cache and we get a result as follows. The result is the same with the third scenario.
Examples were performed in SQL Server 2012 Enterprise Edition. I got the same results when I performed with SQL Server 2008 R2 Enterprise Edition and Standard Edition and SQL Server 2008 Standard Edition.
Conclusion
The purpose of “Optimize for Ad Hoc Workloads” is not to create query plans for the queries that executed only once. Thus, the query plan is not created unnecessarily in memory for single use queries.
I do not prefer using “forced parameterization” together with “optimized for Ad Hoc Workloads”. Because, if the query can be parameterized, the query plan will be created when the query is first executed. So in this case, enabling “optimized for ad hoc workloads” has no meaning. Instead of using forced parameterization, it is better to parameterize queries from your applications.
