SQL Server Policy Base Management Has Clustered Index Condition

By:   |   Updated: 2010-11-05   |   Comments   |   Related: More > Policy Based Management


Free SQL Server Performance and Monitoring Report


Dear Database Professional,

Download your free copy of the MSSQLTips.com SQL Server Performance and Monitoring Report. This survey was conducted in 2022 and polled 588 database professionals about various aspects of tuning and optimizing SQL Server.

Click here to download the free report

Problem

As SQL Azure gains in popularity, more and more people find themselves thinking about having their databases migrated to SQL Azure. Microsoft has published a list of general guidelines at http://msdn.microsoft.com/en-us/library/ee336245.aspx. One of those guidelines details how every table in a SQL Azure database must have a clustered index. Therefore prior to any migration to SQL Azure you must verify that all tables in your database have clustered indexes.

Solution

You can build a policy inside of Policy Based management in order to quickly verify that all tables inside a database have a clustered index defined.


The first step is to create a new condition. The new condition should have a target of 'Table', and the facet we want to make use of is named '@HasClusteredIndex'. Since we are looking to verify that every table has a clustered index we set the operator and value to be '= True' as displayed below:

prior to any migration to SQL Azure you can build a policy inside of Policy Based management in order to quickly verify that all tables inside a database have a clustered index defined.

This next step is optional. I am going to create another new condition in order to limit the final policy to be run against just one database. You may want to run your policy against more than one database which means you will not want to bother creating this new condition. If you do decide to limit the policy to just one particular database then you are going to want to create a condition with the target of 'Database' and filtering on the @Name facet as shown here:

 you are going to want to create a condition with the target of 'Database' and filtering on the @Name facet as shown here

Now we can create the policy. We will use the condition we created above first (named 'Clustered Index Check Conditon') and run our policy against every table inside of the AdventureworksDW database. We do this by clicking on the down arrow next to the word 'Database' and selection the condition we created above named 'AdventureworksDW_DB'. We will leave the evaluation mode to be 'On Demand' and we will also not configure any server restriction as shown below:

we will leave the evaluation mode to be 'On Demand' and we will also not configure any server restriction as shown below

Click OK to save the policy. After saving you can right click and evaluate. If you use the same AdventureworksDW database that I used, then you should see a screen similar to the following:

after saving you can right click and evaluate

And now you can go back to the tables that are missing a clustered index and take action before thinking about trying to push the database into the cloud.


Next Steps





get scripts

next tip button



About the author
MSSQLTips author Thomas LaRock Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and a former Microsoft Certified Trainer with over 20 years’ experience.

View all my tips


Article Last Updated: 2010-11-05

Comments For This Article

















get free sql tips
agree to terms