T-SQL find tables without clustered index

One of the important tasks when optimizing a SQL Server for performance is to find and convert heaps to clustered index. A HEAP is a messy collection of rows piled up unevenly. This does makes insert faster however, select/update/delete are very slow.  On the other hand, Clustered index arranges the table as B-Tree structure based on the clustered index key value. A best practice is to have clustered index defined for a table unless it’s a very small one say a list of countries, states etc. Given below is a T-SQL to find tables without clustered index.

USE ADVENTUREWORKS2014 
GO 
SELECT tables.NAME, 
       (SELECT rows 
        FROM   sys.partitions 
        WHERE  object_id = tables.object_id 
               AND index_id = 0 -- 0 is for heap 
			   -- 1 is for clustered index 
       )AS numberofrows 
FROM   sys.tables tables 
WHERE  Objectproperty(tables.object_id, N'TableHasClustIndex') = 0

The query uses OBJECTPROPERTY command to check whether a table in sys.tables view has a clustered index or not. A value of 1 indicates that table has clustered index and 0 indicates that it doesn’t has a clustered index.

The query returns number of rows each of the heaps contains so as to select a clustered index candidate. Another deciding factor is the table usage, which can be obtained from sys.dm_db_index_usage_stats dmv as shown below

USE ADVENTUREWORKS2014 
GO 
SELECT tables.NAME, 
       (SELECT rows 
        FROM   sys.partitions 
        WHERE  object_id = tables.object_id 
               AND index_id = 0 -- 0 is for heap 
			   -- 1 is for clustered index 
       )AS numberofrows,
	   ius.last_user_scan As TableScan,
	   ius.last_user_lookup As RIDlookup
		
FROM   sys.tables tables 
LEFT JOIN sys.dm_db_index_usage_stats ius 
ON tables.object_id=ius.object_id and ius.index_id=0
WHERE  Objectproperty(tables.object_id, N'TableHasClustIndex') = 0

The above query gets the last_user_scan and last_user_lookup date for a particular. This tells how frequently the table is being used in queries. Thus, if a table has fairly large number of rows and is being accessed frequently, then it’s a good candidate to create clustered index.  The output from above query is shown below.

1_t-sql find tables without clustered index

As shown in above snapshot, the table databaselog was recently scanned and a RIDlookup was performed on it too. This data can be recorded and analyzed over a period to decide whether or not to create clustered index on particular tables.  The tables with null values against tablescan and RIDLookup column doesn’t exists in sys.dm_db_index_usage_stats dmv and are not being used recently.

 

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *