This metric measures the number of tables without clustered indexes defined that contain more than 1000 rows per database. It is good practice to define the clustered index for every table in the database to help improve query performance.<\/p>\r\n
Note: Having a clustered index per table is NOT compulsory. There are cases, where a heap (table without a clustered index) is acceptable.<\/p> \r\n\r\n
To analyze further, the following query can be used to find out which indexes don't have clustered indexes defined:<\/p>\r\n
WITH CTE_1\r\nAS\r\n(\r\n SELECT db_name() as dbname,\r\n o.name as tablename,\r\n (SELECT SUM(p.rows)\r\n FROM sys.partitions p\r\n WHERE p.index_id = i.index_id\r\n AND i.object_id = p.object_id) as number_of_rows\r\n FROM sys.indexes i\r\n INNER JOIN sys.objects o\r\n ON i.object_id = o.object_id\r\n WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1\r\n AND OBJECTPROPERTY(o.object_id, 'TableHasClustIndex') = 0\r\n)\r\nSELECT *\r\n FROM CTE_1\r\n WHERE number_of_rows > 1000;<\/pre>","tsql":"WITH CTE_1\r\nAS\r\n(\r\n SELECT db_name() as dbname,\r\n o.name as tablename,\r\n (SELECT SUM(p.rows)\r\n FROM sys.partitions p\r\n WHERE p.index_id = i.index_id\r\n AND i.object_id = p.object_id) as number_of_rows\r\n FROM sys.indexes i\r\n INNER JOIN sys.objects o\r\n ON i.object_id = o.object_id\r\n WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1\r\n AND OBJECTPROPERTY(o.object_id, 'TableHasClustIndex') = 0\r\n)\r\nSELECT COUNT(*) as cnt\r\n FROM CTE_1\r\n WHERE number_of_rows > 1000;\r\n","instances":true,"frequency":86400,"databases":{"mode":1},"rateofchange":false,"metricenabled":true,"alertname":"Tables without clustered indexes","alertdescription":"This alert is raised when the number of tables without clustered indexes defined that contain more than 1000 rows per database exceeds set thresholds. It is good practice to define the clustered index for every table in the database to help improve query performance.<\/p>\r\n
Note: Having a clustered index per table is NOT compulsory. There are cases, where a heap (table without a clustered index) is acceptable.<\/p> \r\n\r\n
To analyze further, the following query can be used to find out which indexes don't have clustered indexes defined:<\/p>\r\n
WITH CTE_1\r\nAS\r\n(\r\n SELECT db_name() as dbname,\r\n o.name as tablename,\r\n (SELECT SUM(p.rows)\r\n FROM sys.partitions p\r\n WHERE p.index_id = i.index_id\r\n AND i.object_id = p.object_id) as number_of_rows\r\n FROM sys.indexes i\r\n INNER JOIN sys.objects o\r\n ON i.object_id = o.object_id\r\n WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1\r\n AND OBJECTPROPERTY(o.object_id, 'TableHasClustIndex') = 0\r\n)\r\nSELECT *\r\n FROM CTE_1\r\n WHERE number_of_rows > 1000;<\/pre>","aboveorbelow":"above","collections":1,"alertenabled":true,"_thresholds_high":{"selected":true,"value":500},"_thresholds_medium":{"selected":true,"value":100},"_thresholds_low":{"selected":true,"value":0},"thresholds_comment":"Note: These thresholds are intended as guideline values. If they seem too high or too low for your environment, replace them with values more suited to your server performance.","targetentitytype":0}