Optimizing Ad-hoc Workload in SQL Server

By default, when a query is executed in SQL Server, it compiles and generates an execution plan for that query and stores that plan in the plan cache. Now if this happens to be a one-off/ad-hoc query that will never be run again, then the newly generated plan is wasted and its space that is being used in the plan cache is also wasted. This means, the buffer pool now contains compiled plan of queries that will never be executed.

Now imagine if you have hundreds of ad-hoc queries like this?

SQL Server introduced a new server level setting called Optimize for Ad Hoc Workload which helps address this issue. This setting improves the efficiency of plan cache for one-off/ad hoc queries/batches or workloads. Once you enable this option it changes the behavior of storing the compiled plan in the plan cache. Internally, when the query is executed the first time, the database engine stores query hash in the plan cache which is very small in size compared to the compiled plan.

However, the second time the same query is executed, the database engine checks and recognizes that the query hash exists for the ad-hoc workload and it goes ahead with creating a full compiled plan for that query and stores is in the plan cache by removing the query hash. All subsequent execution of this query will use the plan from the plan cache.

Enabling Optimize for Ad Hoc Workload setting prevents buffer pool from occupying space for unwanted plans that will never be used.

Important things to keep in mind:

1. You should enable this option when your server has more one-off or Ad Hoc requests.
2. This prevents plan cache pollution by ad-hoc workloads whose plans will never be used again.
3. Since it stores the complete plan during the second run, expect a small delay during second execution of the query/batch since the engine will need to generate full plan, remove query hash value from the planned cache and store the full new plan in the plan cache.
4. Enabling this option, helps save plan cache memory in buffer pool.
5. You can also use the below stored procedure to identify if your server has heavy ad-hoc workload or not.

Let’s now take a look at how to check if enabling Ad-Hoc workload setting would benefit your server or not:

Create Procedure SQLOPS_AdHocWorkload as
Set NOCOUNT ON
--Author: Saleem Hakani (http://sqlcommunity.com)
--This procedure allows you to check if your server workload can benefit from "Optimize for Ad Hoc Workload" Server Setting.
DECLARE @AdHocWorkloadSize decimal (14,2), 
        @TotalSizeInMB decimal (14,2),
        @AdHocSetting Varchar(20)
 
SELECT @AdHocWorkloadSize = SUM(CAST(
(
CASE 
   WHEN usecounts = 1 AND LOWER(objtype) = 'adhoc' THEN size_in_bytes
   ELSE 0
END
) as decimal(14,2))) / 1048576,
   @TotalSizeInMB = SUM (CAST (size_in_bytes as decimal (14,2))) / 1048576
   FROM sys.dm_exec_cached_plans
 
IF @AdHocWorkloadSize > 200 or ((@AdHocWorkloadSize / @TotalSizeInMB) * 100) > 25
Begin
   Select @AdHocSetting='ENABLE'
End
Else
Begin 
   Select @AdHocSetting='DO NOT ENABLE'
   Select 
      @AdHocSetting as Recommendation, 
      @AdHocWorkloadSize as [Single_Plan_Memory_Usage],
      @TotalSizeInMB as [Cache Plan Size_MB],
      CAST((@AdHocWorkloadSize / @TotalSizeInMB) * 100 as decimal(14,2)) as [%_of_Single_Plan_Cache_Used]
End
GO