SQLChicken.com

SQL Server DBA Tips & Tricks

By

Find Table Heaps Using Policy-Based Management

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)

Share

2 Responses to Find Table Heaps Using Policy-Based Management

  1. SQLDenis says:

    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 *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">