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

Leave a Reply

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