The Optimize for Ad hoc Workload option is set at a SQL Server instance level, and it is available in SQL Server 2008 and higher. Enabling this setting improves the efficiency of the plan cache by only placing a plan stub in the plan cache the first time a batch is compiled, instead of placing the fully compiled plan. This prevents the plan cache from being filled with fully compiled plans that are only used a single time. If a query is then compiled a second time, the full plan will then be loaded into the plan cache. The OnBase application generates a significant number of ad hoc queries, many of which are only executed one time. These query plans can consume unnecessary space in the plan cache. Utilizing the Optimize for Ad hoc Workloads setting can provide benefits for the OnBase installation, though the effect may not be measurable in a system with substantial memory.
To enable the Optimize for Ad hoc Workloads option, navigate to the advanced properties of the server instance and set the Optimize for Ad hoc Workloads option to True.