03 February,2014 by Jack Vamvas
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 go 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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |