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)
--CREATE CONDITION
Declare @condition_id intEXEC msdb.dbo.sp_syspolicy_add_condition @name=N'Find heaps', @description=N'', @facet=N'Table', @expression=N'<Operator> <TypeClass>Bool</TypeClass> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <TypeClass>Bool</TypeClass> <Name>HasClusteredIndex</Name> </Attribute> <Function> <TypeClass>Bool</TypeClass> <FunctionType>True</FunctionType> <ReturnType>Bool</ReturnType> <Count>0</Count> </Function></Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUTSelect @condition_id GO --CREATE POLICY Declare @object_set_id int EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'Find Table Heaps_ObjectSet', @facet=N'Table', @object_set_id=@object_set_id OUTPUT Select @object_set_id Declare @target_set_id int EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Find Table Heaps_ObjectSet', @type_skeleton=N'Server/Database/Table', @type=N'TABLE', @enabled=True, @target_set_id=@target_set_id OUTPUT Select @target_set_id EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/Table', @level_name=N'Table', @condition_name=N'', @target_set_level_id=0 EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0 GO Declare @policy_id int EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'Find Table Heaps', @condition_name=N'Find heaps', @policy_category=N'', @description=N'Heaps are tables without clustered indexes. Read the link below to learn more about heaps.', @help_text=N'Fragmentation (part 4):what are heaps? by Paul Randal', @help_link=N'http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/09/19/761437.aspx', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=0, @is_enabled=False, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'Find Table Heaps_ObjectSet' Select @policy_id GO
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
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.