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.
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:
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)
DECLARE @AdHocSetting Varchar(20)
SELECT @AdHocWorkloadSize =
SUM(CAST((CASE WHEN usecounts = 1 AND LOWER(objtype) = 'adhoc'
) as decimal(14,2))) / 1048576,
@TotalSizeInMB = SUM (CAST (size_in_bytes as decimal (14,2))) / 1048576
IF @AdHocWorkloadSize > 200 or ((@AdHocWorkloadSize / @TotalSizeInMB) * 100) > 25
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]