While it is easy to build and test policies by executing them on demand (especially powerful when run through Central Management Server) I had some issues getting my policies to run in “on schedule” mode.
To be more specific, my policies that use the ExecuteSQL function have been an issue. What I was finding was:
- The policy would run fine “on demand” but…
- When I run the policy through the PBM scheduler, the policy would fail.
Dealing with false positives is not a good start for any monitoring service, so getting to the root of the issue was critical.
Read on for the solution.
Pieter Vanhove has published his Policy-Based Management-based DBA Morning Checklist and has some post-Summit additions:
Optimize for Ad Hoc Workloads
The policy is going to check if the server setting Optimize for Ad Hoc Workloads, is set to True. By default, this setting is set to False.
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. More information can be found on https://msdn.microsoft.com/en-us/library/cc645587.aspx
I don’t see any downside by enabling this setting.
Not many shops use PBM, so I’m happy to see Pieter contributing this to the general community.