SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

See if Auto Create Statistics is enabled using PBM

We will walk through creating a policy to evaluate the status of “Auto Create Statistics” on our server named “File2”.  First we need to open SQL Server Management Studio and connect to our server.  Then we need to enable Policy Management by expanding the Management node, right clicking on Policy Management, and selecting Enable.

You will notice three folders under the Policy Management node.  If explore them you will see that the Policies and Conditions folders are empty while the Facets folder contains many items.  Facets are created by Microsoft and you cannot create your own.  The other folders are empty because we have not yet created any policies.  This is a good time to browse through the facets and open a few to get an idea of what they contain.

We want Auto Create Statistics to be turned on for all our databases, so let’s start by right clicking on the Conditions folder and selecting New Condition.  This opens our create condition dialog box where we define the condition name, the Facet that contains the properties we want to evaluate, and the expression used to evaluate those properties.  This is what those options should look like:

Now that our condition is defined, we’re ready to create our policy.  Right click the policies folder under the Management node and select Create Policy.  We need to give it a name, select the check condition we created previously, define the targets to apply it to, choose the evaluation mode, and select any server restrictions.  Here is what our example will look like:

Now that we have our policy configured let’s test it out by evaluating it against our local server and reviewing the results.  Simply right click our new “Auto Create Statistics” policy and select Evaluate.  This will cause the policy to be evaluated immediately on the local SQL instance.  Here is what we get:

We can see that our server has four databases and one of them has violated our policy.  If you check the box next to the offending database and select apply, PBM will bring that database into compliance by changing the Auto Create Statistics setting from false to true.  Before we do that we can also select the View hyperlink in the details column to see the exact settings that caused this policy to be violated.

We can see that the policy expected AutoCreateStatisticsEnabled to be set to True, but the actual value was False.

Ryan Adams

Ryan Adams has worked for Verizon for 15 years. His primary focus is the SQL Server Engine, high availability, and disaster recovery. Previously he was a Senior Active Directory Architect and designed the company's worldwide Active Directory infrastructure. He serves on the Board of Directors for the North Texas SQL Server User Group and is President of the PASS Performance Virtual Chapter. He also serves as a Regional Mentor for PASS and holds the following certifications: MCP MCSA MCSE MCDBA MCTS MCITP.


No comments.

Leave a Comment

Please register or log in to leave a comment.