Find Table Heaps Using Policy-Based Management

Posted on

This is just a quick post in regards to a conversation I just had via Twitter. If you don’t already use Twitter, the SQL Community has setup a great resource on there using the hashtag of #sqlhelp.

Today a conversation came up due to a forum question over at SQLServerCentral regarding applying policies to databases with tables that have heaps. If you’re not familiar with the term, a heap is a table that has no clustered index on it. This can be problematic from a performance stand point so it might benefit you to find a way to identify these potential problem children. Enter Policy-Based Management.

This is a simple policy that you can run against your servers and it will identify your tables that are heaps. Just to clarify this policy identifies if your table has a clustered index on it. If it doesn’t then it will fail policy check. I’ve provided two ways to get the policy.

Download policy by clicking here

OR (Updated 4/15/11 to include creation script for condition)

2 thoughts on “Find Table Heaps Using Policy-Based Management

  1. I am confused, if a table has no clustered index then it is a heap period.

    If you have a non clustered index on a table with a clustered index it will point to the clustered index, if the non clustered index is on a table without a clustered index it will point to the table with a row locator

    perhaps you meant to say a table that has no indexes (clustered or non clustered) at all

  2. Thanks Denis, I’ve updated the post to clarify that a heap is a table simply without a clustered index. I’ve also updated the policy accordingly.

Leave a Reply

Your email address will not be published. Required fields are marked *