Click or drag to resize

SA0143 : Single use Ad-hoc plans are using considerable amount of the procedure cache

The topic describes the SA0143 analysis rule.

Message

Single use Ad-hoc plans are using considerable amount of the procedure cache

Description

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.

Scope

The rule has a ContextOnly scope and is applied only on current server and database schema.

Parameters
NameDescriptionDefault Value
MaxMemoryAdhocPlanCache

Maximum amount of memory used by Ad-Hoc plans in MB or in %.

20%

Remarks

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Categories

Maintenance Rules

Additional Information
See Also

Other Resources