SA0143 : Single use Ad-hoc plans are using considerable amount of the procedure cache
The topic describes the SA0143 analysis rule.
Single use Ad-hoc plans are using considerable amount of the procedure cache
The rule checks the cache memory used by Ad-hoc plans and reports if the amount is greater than configurable value.
It is recommended that the ‘Optimize For Ad-Hoc Workloads’ setting should be used when there are a lot of cached single use Ad-Hoc plans. These plans consume storage that can be better utilized by other plans or data pages.
The amount of ad-hoc plan cache above which the rule will start reporting can be configured using the MaxMemoryAdhocPlanCache rule parameter.
Review the cached Ad-hoc plans and consider using ‘Optimize For Ad-Hoc Workloads’ setting when there are a lot of cached single use Ad-Hoc plans.
The rule has a ContextOnly scope and is applied only on current server and database schema.
Maximum amount of memory used by Ad-Hoc plans in MB or in %.
The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.
Maintenance Rules, Bugs