This is one of the blog post in a series of posts about optimizing SQL Server queries. A list of blog posts about query tuning can be found here – https://jbswiki.com/2021/04/15/sql-server-query-optimization-introduction/

-> Create the required database using below query,

create database JB_AdHoc
go

-> Execute the below query. Copy the output and place it in a new query window and execute it to create the required objects,

set nocount on
declare @i int = 1
declare @j int = 1000
print 'set nocount on'
print 'use JB_AdHoc'
print 'go'
while (@i!=1001)
begin
print 'IF OBJECT_ID(''Products'+convert(varchar,@i)+''', ''U'') IS NOT NULL
drop table Products'+convert(varchar,@i)+'
go
create table Products'+convert(varchar,@i)+'(
ProductNo int primary key identity (1,1),
ProductArea int ,
ProductQuantity int)
go
insert into Products'+convert(varchar,@i)+' values ('+convert(varchar,@j)+',10000 * rand())
go 1001
--'+convert(varchar,@i)+'-'
set @i = @i + 1
set @j = @j - 1
end

-> The output of the above query contains table creation script for 1000 tables. It also contains insert queries to insert 1001 rows on each of the 1000 tables. The query took close to 15 minutes in my lab machine to complete.

-> Once the objects are created, execute the below query,

use JB_AdHoc
go
set nocount on
declare @i int = 1
declare @SQL varchar(max)
print 'set nocount on'
print 'use JB_AdHoc'
print 'go'
print 'DECLARE @ProductNo INT'
print 'DECLARE @ProductArea int'
print 'DECLARE @ProductQuantity int'
while (@i>=1000)
begin
set @SQL = 'select @ProductNo = ProductNo, @ProductArea = ProductArea, @ProductQuantity = ProductQuantity from Products'+convert(varchar,@i)+' where ProductNo in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20) --JB_AdHoc Workload'
print (@SQL)
set @i = @i + 1
end

 -> Copy the output and place it in a new query window. Before executing the query, run “dbcc freeproccache”. The query took 3 seconds in my machine.

-> Execute the below query and check how many plan are created,

select text, query_plan, execution_count
from sys.dm_exec_query_stats
outer apply sys.dm_exec_sql_text(sql_handle)
outer apply sys.dm_exec_query_plan(plan_handle)
where text like '%DECLARE @ProductNo INT%'

-> It is clear that the optimizer has created 1 unique plan for each of the select query executed. We are able to see 1000 plans created from the above screenshot with execution_Count of 1. So this means that the adhoc query ran just once.

-> Execute the below query and check the size of the plans created,

select text, query_plan, usecounts, size_in_bytes/1024.0 as 'Size_in_KB', cacheobjtype, objtype
from sys.dm_exec_cached_plans
outer apply sys.dm_exec_sql_text(plan_handle)
outer apply sys.dm_exec_query_plan(plan_handle)
where text like 'DECLARE @ProductNo INT%'

-> It is clear that close to 39 MB is used by these 1000 plans in the plan cache.

-> Let’s execute the SELECT query again and see the plan details again,

-> This time the query completed within 1 second. This is because the optimizer did not create the plan again, instead used the plan created before.

-> Lets check the output of queries related to plans, the execution_count has changed from 1 to 2 for the plans created previously,

-> The overall size of the plan did not change, since we reused the plan from the cache. But the usecounts this time is 2.

-> There are scenarios where we may see adhoc queries plans in the cache that were run only once that may be using much of memory.

-> Sp_configure ‘optimize for ad hoc workloads’ can help us in those scenarios, wherein it creates a smaller size stubs during the initial run and then goes onto create a regular plan when the same query is run again.

-> Execute the below query,

sp_configure 'show advanced options',1
reconfigure
sp_configure 'optimize for ad hoc workloads',1
reconfigure

-> Run “dbcc freeproccache” to clear the cache and then run the select query again. the query takes 3 seconds again.

-> Looking at the plan cache now. This time we dont see any plans created, We just see NULL for query_plan.

-> This time cacheobjtype is “Compiled Plan Stub” and the size is just 242 KB and not 39 MB.

-> Let us run the select statement again. This time also it took 3 seconds.

-> Looking at the plan cache now,

-> After the second run of the select query, it has created a regular plan on the cache and we dont see the stubs anymore.

-> If you run the select query again now, it will complete within 1 sec and will not take 3 seconds as the plans are not created again, but will be used from the cache.

-> “Optimize for ad hoc workloads” will ensure that the plans of adhoc queries that are executed only once will not occupy much memory.

-> The problem with “Optimize for ad hoc workloads” setting is that the optimizer will have to spend time in creating plan twice for adhoc queries that executes more than once. Optimizer will create a stub first time and then the regular plan when the same query executes again. Any other execution of the same query will use the plan from the cache after the regular plan is created. Hence there may be a slight performance hit for adhoc queries that are executed more than once.

-> It is important to check the cache to see if the cache has more number of singleton plans before enabling this setting. Carefull testing needs to be done before enabling this setting.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.