Heaps - Tables without a clustered index

03 February,2014 by Tom Collins

In the post - SQL Server - List all indexes of all tables  , the query returns all tables that are not heaps. In this post , we’ll look at tables without a clustered index aka Heaps

Microsoft  presents good guidelines for When to Use a Heap , When not to use a heap and Managing heaps. The purpose of this post is not replicate the Microsoft website . But to highlight the need for a DBA to review Heaps and manage them to optimize the systems

All because a table is a heap – does not automatically mean a Clustered Index is required . there are some situations where a heap is OK. Example 1: The table is very small Example 2: The RID is smaller than the clustered index key and normal query processing doesn’t use the Clustered Index and relies on the Non Clustered Index.

 One approach I use is to identify tables which are heaps i.e tables without a clustered index and are queried actively.  You can build in this process to a regular review or as part of a process when you start managing a new server.

There are plenty of situations where a DDL creating table is released onto a database but without an index


use db 

SELECT DISTINCT object_name(obj.object_id),idx.type_desc,ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates	  
		  FROM sys.indexes idx INNER JOIN sys.objects obj ON idx.object_id = obj.object_id 
		  INNER JOIN sys.partitions par ON idx.object_id = par.object_id AND idx.index_id = par.index_id 
		 		  LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON idx.object_id = ius.object_id AND idx.index_id = ius.index_id 
		 		  WHERE idx.type_desc = 'HEAP' AND COALESCE(ius.user_scans, ius.user_lookups, ius.user_updates, ius.user_seeks) IS NOT NULL 
		          AND is_ms_shipped = 0

Author: Tom Collins (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Heaps - Tables without a clustered index

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer