Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will learn how to improve SQL Server Ad Hoc Queries Performance and what Ad Hoc Query means.
Ad Hoc Query
The word ad hoc comes from Latin and means “for purpose”.
In other words, it refers to a query written in case the written query is written for a purpose and has no continuation or rarely.
If I need to explain with an example, imagine that you have a table with user information and let’s assume that a dynamic query is made over this table over a different user number.
In this case, the written query is actually an Ad Hoc query.
When you run a query in SQL Server, the execution plan is created for the query run by the SQL Server Query Optimizer and the query runs through this plan.
The execution of the said execution plan can be completed in a few milliseconds for a simple query,
while it may take longer for complex queries.
For this reason, SQL Server keeps the execution plan information it creates for each running query in its cache in case the same query is run again.
Think about it, even if you run it only once, SQL Server creates an execution plan for your query to run and keeps it in its cache so that you can run it again in the future.
Of course, if there is no suitable execution plan for your query to run,
a new query plan is created by SQL Server Query Optimizer.
In order to increase the cache efficiency of the execution plans of the infrequently used queries that entered our lives with SQL Server 2008,
it introduced “Optimize for Ad hoc Workloads”, a feature at the presentation level.
When the query is compiled for the first time, instead of keeping the execution plan,
the Compiler Plan stores the Stub value. If the query is run again, it converts the Stub value to the execution plan.
There are two methods you can use to determine whether this feature we have described is on or off.
The first one is SQL Server Management Studio (SSMS) Right click on the server in Object Explorer and then click Properties,
then in the advanced menu the “Optimize for ad hoc workloads” section defaults to False,
which means that when a query is compiled and executed, your entire plan will be placed in cache .
You can use TSQL to learn whether this feature is active or passive for GUI -On screen- learning.
The following query will solve your need.
1 2 | SELECT name , value , description FROM sys.configurations WHERE Name = 'optimize for ad hoc workloads' |
The query result is;
Before making our example work, I do the execution plan cleaning with the following command as the first step.
Of course, restarting the SQL Server service will ensure that the cache – cache – is cleared, but we do not prefer this.
1 | DBCC FREEPROCCACHE |
I will be making our example with AdventureWorks2019, one of Microsoft’s sample databases, on SQL Server 2019 Developer Edition.
The aforementioned “Optimize for Ad Hoc Workloads” feature is currently disabled.
I ran the following query.
1 | SELECT * FROM [AdventureWorks2019].[Person].[Person] Where BusinessEntityID = 3 |
After running my query, I run the following query to get the query plan.
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT c.usecounts , c.objtype , t.text , q.query_plan , size_in_bytes , cacheobjtype , plan_handle FROM sys.dm_exec_cached_plans AS c CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS q WHERE t.text LIKE '%select% *%' AND q.dbid = (select database_id from sys.databases where name ='AdventureWorks2019′) ORDER BY c.usecounts DESC |
Although I ran the query for the first time, “106496” byte was used for this query and execution plan on the cache.
In the query I run on AdventureWorks, I make changes to the BusinessEntityID,
run it again and check the execution plans again.
Query1:
1 | SELECT * FROM [AdventureWorks2019].[Person].[Person] Where BusinessEntityID = 11 |
Query2:
1 | SELECT * FROM [AdventureWorks2019].[Person].[Person] Where BusinessEntityID = 6 |
Query3:
1 | SELECT * FROM [AdventureWorks2019].[Person].[Person] Where BusinessEntityID = 20 |
As you can see, apart from the BusinessEntityID = 3 query we ran before,
a separate execution plan was created for the other three queries, and each plan occupied the same size of space on the cache.
Let’s activate the “optimize for ad hoc workloads” feature and run our queries again.
For this process, it will be sufficient to run the following query.
1 2 3 4 5 6 7 8 | EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1′ GO RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE GO |
Query output;
Let’s clear the plan caches again and run the queries we have run before, in order.
1 | DBCC FREEPROCCACHE |
1 | SELECT * FROM [AdventureWorks2019].[Person].[Person] Where BusinessEntityID = 3 |
With the activation of the feature, we see the Complied Plan Stub value as you can see.
A decrease is observed in the size of the cache.
Let’s run the same query with the same parameter more than once and examine the plan details again.
It is no longer a Compiled Plan Stub state, so we know that the query being executed runs multiple times – 12 times in our example – and its size will not change in the next run.
What if we change the BusinessEntityID in our example and run the query again? I run the query again as BusinessEntityID = 20.
A different plan has been created and stands as the Compiled Plan Stub.
The next run of the same query will change the Compiled Plan Stub value to Compiled Plan.
We have observed how effective the “Optimize for Ad Hoc Workloads” feature is in improving the performance of Ad Hoc queries.
After examining the Ad Hoc queries on the system you are managing,
evaluating how often and what size they are, you can work on activating the feature.
