Policy-Based Management(Check Heap Tables in Databases-Table Facet)

Policy-Based Management is a feature that introduced with SQL Server 2008. It allows us to set the rules we want in our systems, to determine our standards and to perform our controls automatically.

For example, we can create a policy for control purposes so that the names of our stored procedures do not start with a number or database recovery models are not simple.

If the tables do not have a clustered index, these tables are called heap. And heap tables generally effect performance poorly. That’s why I’d recommend putting a clustered index on a column in your table.

Before reading this article, I recommend you read the article “Index Concept and Performance Effect on SQL Server” in SQL Server to understand the importance of Clustered Index. For more information, you can use the Search section of our site.

To comprehend “Policy Based Management (PBM)”, you need to understand some concepts.

Facet: A feature that can be managed by the PBM. For example, there is a facet called Table. And using this facet, you can create a policy that checks for clustered indexes in tables.

You can access all facets via SSMS as follows. You have to double-click on Facet details.

Condition: checks whether the sub properties of the related facets meet the specified condition.

Let’s create a policy that checks whether the tables in the databases have a clustered index.

We click New Policy on the Management> Policy Management> Policies tab as follows.

We give a name to Policy and we need to create a condition from Check Condition.

We click on New Condition.

We give a name to the condition from the Name section.

We choose the Table facet in the Facet section.

By clicking “…” in the Expression section, we select @HasClusteredIndex, which is the sub property of the Table facet.

We select “=” in Operator and “True” in Value.

Thus, if there is a table without the Clustered Index, the policy will fail.

By selecting “On Schedule” from the Evaluation Mode section, we determine at what intervals the policy will be checked, and then click the Enable check box.

If we choose On Demand, we only do checks when we execute policy.

In the server restriction section, if you have a server-based condition, you can control your condition by creating a server-based condition.

If we want to automatically check at regular intervals, we click new in schedule and determine the frequency of the policy check. As you can see below, we set it to work once every day at 00:00:000.

We created our policy. To run manual, we click Evaluate as follows.

When I performed this operation in my local, I got a result like this. As you can see, in some databases there are some tables thet dont have a clustered index, so the policy has failed.

Click on View to get more detailed information.

Because the policy is failing, when you refresh on Instance, a red x sign appears next to Instance. The reason for this is that there is an unsuccessful policy that SQL Server wants to notify us. When you see this mark, you must look at the defined policies and correct the problems.

There are many controls you can do on SQL Server with Policy Based Management. And I think a professional database administrator should use this feature that SQL Server provide us with all the details. You can see other controls you can do with Policy Based Management by typing “Policy Based Management” in our search section.