Policy-Based Management (PBM) has a PR problem, even worse that DQS and MDS. A lot of people don’t know what it is, or if they do, are not sure if it’s still supported. Microsoft doesn’t even mention the feature in its list of Editions and supported features of SQL Server 2016. The PR problem is so bad, in fact, that I’ve come across community forum postings asking whether PBM still exists.
Despite its silent role, PBM is still a supported feature and appears to be available to all SQL Server editions, although I suspect it caters primarily to organizations with a large number of similar SQL Server implementations.
Microsoft introduced PBM in SQL Server 2008 to provide a policy-based system for managing one or more SQL Server instances. You can, for example, use policies to grant permissions on user databases, disable lightweight pooling, or choose an authentication mode. You can also target policies at specific SQL Server instances, databases, tables, or indexes.
Quick hits: Data Quality Services and Master Data Services have been lapped by the competition and aren’t really competitive products anymore. Policy-Based Management is still useful, and I wish it’d get some internal love to extend roles & features. Management Data Warehouse is a tire fire that you should avoid at all costs. Service Broker is useful in the right circumstances, but it needs a few big changes to become a great broker. Unfortunately, I don’t see big changes happening and so there are superior alternatives for data flow (like Apache Kafka). But read Robert’s take on these features.
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.