When a query is executed, it compiles and generates an execution plan and stores it in the plan cache. If this is a one time query that will never be run again, then the newly generated plan and space in plan cache are wasted. This means the buffer pool now contains a compiled plan of queries that will never be executed.
Imagine if you have hundreds of such ad-hoc queries, you are occupying the buffer pool with plans that will never be used. To prevent this, SQL Server allows you to configure a 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 or ad hoc queries or batches. Once you enable this option, it changes the behavior of storing the compiled plan in the plan cache. Enabling Optimize for Ad-Hoc Workload setting prevents the buffer pool from occupying space for unwanted plans that will never be used.
SQLOPS script to help identify if you can benefit from “Optimize for Ad Hoc Workload”
Note: Our Risk and Health Audit covers this in great detail.
--This SQLOPS script allows you to check if your server workload --can benefit from "Optimize for Ad Hoc Workload" Server Setting. --Script provided as sample by https://sqlops.com SET NOCOUNT ON 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 Select @AdHocSetting='ENABLE' Else 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] GO
Internally with Optimize for Ad Hoc Workload setting, 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 it in the plan cache by removing the query hash. All subsequent execution of this query will use the plan from the plan cache.
Configure Optimize for Ad Hoc Workload setting using SSMS
You can also configure Optimize for Ad Hoc Workload settings using SQL Server Management Studio by right-clicking on the server and selecting Server Properties and then selecting Advanced page:
Keep the following points in mind about Optimize for Ad Hoc Workload setting:
1. You should enable this option when your server has more one-off or Ad-Hoc query 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 the second execution of the query/batch as the engine will need to generate a 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 the buffer pool.
Credits: https://sqlops.com/sql-server-best-practices/
