How to find Active tables without a clustered index

22 September,2014 by Jack Vamvas

Question: How can I find all active tables but without a clustered index? I’m doing a review of databases  to create a report for database owners.

Answer:  An alternative way of calling a table without a clustered index is “heap”. In summary , they are scattered across the disk(s)  in no order. The main benefit is for very fast inserts, for example in an ETL process requiring a staging table to commit some data clean up and then moving out again into the target permanent table.

Think before you add a Clustered Index

Don’t just add a clustered index to every heap and hope for the best! Think about which tables require a clustered index , apply and test . Once in production monitor the performance and maintain the indexes effectively

Query to find active tables without a Clustered Index

 

  SELECT DISTINCT so.name,so.type_desc,idx.type_desc
		  FROM sys.indexes idx INNER JOIN sys.objects so ON idx.object_id = so.object_id 
		  INNER JOIN sys.partitions p ON idx.object_id = p.object_id AND idx.index_id = p.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_seeks, ius.user_scans, ius.user_lookups, ius.user_updates) IS NOT NULL 
		  AND so.type <> 'S'
          order by so.name

 

Read more on Index Management

SQL Server - How to find the largest sql index and table size

SQL Server - Predict ALTER INDEX REORGANIZE finish time

SQL Server Disable Indexes and Rebuild Indexes dynamically

SQL Index Fragmentation and sys.dm_db_index_physical_stats

SQL Server - Index missing Statistics

SQL Server - List all indexes of all tables


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment on How to find Active tables without a clustered index


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