SQL Server DBA Tips & Tricks


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)

  • http://blogs.lessthandot.com/index.php SQLDenis

    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

  • http://emailtoid.net/i/79a23b74/bec3ed36/ Jorge Segarra

    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.