Plan cache can be optimized

If the SQL Server plan cache consists of more than 60 percent single-use plans, it is advised to enable the “Optimize for adhoc” setting. If this is the case, Performance Store will throw an alert.

To query the Performance Store database for servers where the plan cache is filled with more than 60 % single use plans, use:

select s.*
from dbo.v_servers s
where s.single_use_plans_pct > 60
and s.optimize_for_adhoc = 0
and s.single_use_plans > 1000

Single use plans can be excluded from the plan cache by enabling optimize for ad-hoc workloads. Remember if this is changed, the plan cache should be emptied before it is possible to see the changes, and changes will not be visible until the server has been running for a while.

The alert will be visible on the Latest stats dashboard.

To exclude specific servers from this alert, see how to use the dbo.team_alert_exclude table in Alerting.