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.

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

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.

 

Regards

Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook