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