“Optimize for ad hoc workloads” is a server level setting introduced in SQL Server 2008. The main reason to have this option is to prevent “plan cache bloating” problem due to one time used ad hoc queries.
For any query that runs on SQL Server, query optimizer creates a plan. It first checks if there is an existing plan in the cache,if so, it will use that plan else will create a new plan. The generation of plan consumes CPU as it has to compile the code and come up with best cost based query plan.The generated query plan will then be stored in the plan cache which takes some memory. Now, when the server gets lot of these ad hoc queries,it will take significant part of the server memory. This particularly becomes disadvantage when these queries are not necessary used more than once (referred as ‘plan cache bloating’).
So, when ‘optimize for ad hoc workloads’ is set to 1,it will store plan stub instead of query plan. This plan stub is significantly smaller in size compared to query plan. This is referred as ‘Complied plan Stub’ cacheobjtype in sys.dm_exec_cached plan. But when the same query is run again(second run), it will store the actual query plan. This means that the code has to be compiled again on the second run – which could be a disadvantage but generally is acceptable in many situations.
Ad hoc queries are the queries that are run without using parameters. Depending on the nature of the ad hoc query,even in simple parameterization,SQL Server can either parameterise the query that is deemed as “Safe” or will pass the literal values to the query optimizer,if the query is not deemed as safe by the optimizer.
Configuring “Optimize for ad hoc workloads” option
sp_configure 'show advanced options',1
GO
Reconfigure with Override
sp_configure 'optimize for ad hoc workloads',1
GO
Reconfigure With Override
If exists (Select 1 from sys.tables where name='student')
Begin
Drop Table Student
End
Create Table Student(sno int identity(1,1) Primary Key,Slname varchar(20))
Go
Insert into Student(slname)
Values('Herendaz'),('Schulz'),('McKnight'),('Lavenger'),('Smith')
–even though this is ad hoc query,query optimizer considers this as “safe” and parameterize the query
Select * from [dbo].[Student] where sno=5
If you check the execution plan, you would see something like this :
For simple queries like this, ‘optimize for ad hoc work loads’ setting does not have any impact and it will store the actual query plan as the query optimizer considers them as parameterized query. By default,SQL Server will try to parameterize the queries that it considers to be “safe”, even in simple parameterization.
The below query gives you an idea on how the query plan is stored in the plan cache. In this example, it is considered as ‘Prepared’ and not ‘ad hoc’.
Select usecounts,cacheobjtype,objtype,size_in_bytes,text
from sys.dm_exec_cached_plans a Cross Apply sys.dm_exec_sql_text(a.plan_handle)
Left Outer Join sys.dm_exec_query_stats b on a.plan_handle=b.plan_handle
For queries that SQL Server implicitly will not parameterize,it will store ‘complied plan stub’ for the first run and for the second run, it will store the actual query plan.
–query optimizer does not consider this as “safe” and hence does not parameterize this query
Select * from [dbo].[Student] where sno=4 and isnull(slname,'Unknown')='Lavenger'
The execution plan looks like this:
Also, using the below query, you can find how the query plan is saved as.
Select usecounts,cacheobjtype,objtype,size_in_bytes,text
from sys.dm_exec_cached_plans a Cross Apply sys.dm_exec_sql_text(a.plan_handle)
Left Outer Join sys.dm_exec_query_stats b on a.plan_handle=b.plan_handle
For First Run:
For Second Run:
You can see the difference in cacheobjtype and size for both first and second runs. The use count for second run shows as 1,because the query plan is recreated.
The database level setting on “parameterization” will impact the decision on whether it would parameterise only the ad hoc queries that it considers are “safe”(simple parameterization) or will it parameterize all the ad hoc queries (Forced Parameterization).
If forced parameterization is set for the database, then the main ad hoc query is parameterised but all the shell executions will have ‘complied plan stub’ for first execution.
I do not think there is an option in SQL server that tells on what run to store the query plan. When ‘optimize for ad hoc workload’ is enabled, SQL server, by default, stores the compiled plan for the second run. I wonder what would be the reason in not making this as user specified option.
SQL Server Repository 