sp_configure 'optimize for ad hoc workloads',1\nreconfigure<\/pre>\nSQL Server is using simple parameterization by default. But you can also set to forced parameterization.<\/p>\n
Enable Forced Parameterization<\/h3>\n
You can change the parameterization type with the help of the following query.<\/p>\n
USE [master]\nGO\nALTER DATABASE [AdventureWorks2012] SET PARAMETERIZATION FORCED WITH NO_WAIT\nGO\n<\/pre>\nWhy we Enable Forced Parameterization<\/h3>\n
Suppose application sends queries as unparameterized(ad hoc<\/strong>) and we cannot change the application to send the queries with parameters. In this case, we can enable forced parameterization.<\/p>\n
Optimized For ad hoc workloads and Parameterization Scenarios<\/h2>\n
Now let’s see what happens when enabling and disabling the optimized ad hoc workloads with simple or forced parameterization.<\/p>\n
Scenario 1: Optimize for ad hoc workloads disabled and SQL Server use simple parameterization<\/h3>\n
Clean the Cache with the following query. You should not do this in the production environment.<\/p>\n
DBCC FREEPROCCACHE\nDBCC DROPCLEANBUFFERS<\/pre>\nWe will run our queries in the AdwentureWorks database. So we’re running the following query.<\/p>\n
USE AdventureWorks\nGO<\/pre>\nLet’s run the following 3 queries one by one after clearing the cache.<\/p>\n
SELECT * FROM HumanResources.Shift\n\nSELECT * FROM HumanResources.Shift\n\nwhere ModifiedDate='2002-06-01 00:00:00.000'\n \nSELECT * FROM HumanResources.Shift\n\nwhere ISNULL(ModifiedDate,'1\/1\/2003')>'1\/1\/1990'<\/pre>\nAfter 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.<\/p>\n
SELECT p.size_in_bytes,p.cacheobjtype,p.objtype,qp.query_plan,t.text\n\nFROM sys.dm_exec_cached_plans p\n\nCROSS APPLY sys.dm_exec_query_plan(p.plan_handle) qp\n\nCROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t\n\nwhere t.text like '%HumanResources%<\/pre>\nWhile optimizing for ad hoc workloads is disabled and simple parameterization is enabled, we get a result as follows;<\/p>\n
<\/p>\n
As you can see, all query plans have created. We only see that there is a difference in a query.<\/p>\n
SELECT * FROM HumanResources.Shift where ModifiedDate='2002-06-01 00:00:00.000'<\/pre>\nA 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.<\/p>\n
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.<\/p>\n
That is, in some cases, simple parameterization may not be able to parameterize queries that have a complex where condition.<\/p>\n
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.<\/p>\n
Scenario 2: Optimize for ad hoc workloads enabled and SQL Server use simple parameterization<\/h3>\n
When we run the queries again, we get a result as follows.<\/p>\n
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.<\/p>\n
<\/p>\n
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.<\/p>\n
<\/p>\n
Scenario 3: Optimize for ad hoc workloads disabled and SQL Server use forced parameterization<\/h3>\n
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.<\/p>\n
<\/p>\n
Scenario 4: Optimize for ad hoc workloads enabled and SQL Server use forced parameterization<\/h3>\n
After clearing Cache, we execute the same queries again.<\/p>\n
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.<\/p>\n
<\/p>\n
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.<\/p>\n
<\/p>\n
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.<\/p>\n
Conclusion<\/h2>\n
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.<\/p>\n
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.<\/p>\n\n
<\/div>\n