Hi @Bob sql
The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches.
When this option is set to
1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This may help to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that aren't reused. However, enabling this option may affect your ability to troubleshoot single-use plans.
If your adhoc plan cache is 20-30% of total Plan Cache, you should turn on the Optimize for Ad Hoc Workloads. In another scenario it may be not beneficial to you and in very rare scenario, it will degrade your server’s settings.
Here is the script which gives you the size of the AdHoc Plan Cache and Total Plan Cache.
SELECT AdHoc_Plan_MB, Total_Cache_MB, AdHoc_Plan_MB*100.0 / Total_Cache_MB AS 'AdHoc %' FROM ( SELECT SUM(CASE WHEN objtype = 'adhoc' THEN size_in_bytes ELSE 0 END) / 1048576.0 AdHoc_Plan_MB, SUM(size_in_bytes) / 1048576.0 Total_Cache_MB FROM sys.dm_exec_cached_plans) T
Based on the result you can make your own conclusion and change your settings.
Note that this value gets reset when you restart SQL Server services.
Refer to this article for more details: SQL SERVER – When to Turn On Optimize for Ad Hoc Workloads?
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.