Tables without clustered indexes defined

| 3 Comments | 8,589 views
Categories: ,

VN:F [1.9.17_1161]
Rating: 5.0/5 (1 vote cast)

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.

Note: Having a clustered index per table is NOT compulsory. There are cases, where a heap (table without a clustered index) is acceptable.

To analyze further, the following query can be used to find out which indexes don’t have clustered indexes defined:

WITH CTE_1
AS
(
  SELECT db_name() as dbname,
         o.name as tablename,
         (SELECT SUM(p.rows)
            FROM sys.partitions p
           WHERE p.index_id = i.index_id
             AND i.object_id = p.object_id) as number_of_rows
    FROM sys.indexes i
   INNER JOIN sys.objects o
      ON i.object_id = o.object_id
   WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
     AND OBJECTPROPERTY(o.object_id, 'TableHasClustIndex') = 0
)
SELECT *
  FROM CTE_1
 WHERE number_of_rows > 1000;
Tables without clustered indexes defined, 5.0 out of 5 based on 1 rating

Metric definition

Metric Name:
Tables without clustered indexes defined
Description:

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.

Note: Having a clustered index per table is NOT compulsory. There are cases, where a heap (table without a clustered index) is acceptable.

To analyze further, the following query can be used to find out which indexes don't have clustered indexes defined:

WITH CTE_1
AS
(
  SELECT db_name() as dbname,
         o.name as tablename,
         (SELECT SUM(p.rows)
            FROM sys.partitions p
           WHERE p.index_id = i.index_id
             AND i.object_id = p.object_id) as number_of_rows
    FROM sys.indexes i
   INNER JOIN sys.objects o
      ON i.object_id = o.object_id
   WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
     AND OBJECTPROPERTY(o.object_id, 'TableHasClustIndex') = 0
)
SELECT *
  FROM CTE_1
 WHERE number_of_rows > 1000;
Enter the T-SQL query that will collect data:
WITH CTE_1
AS
(
  SELECT db_name() as dbname,
         o.name as tablename,
         (SELECT SUM(p.rows)
            FROM sys.partitions p
           WHERE p.index_id = i.index_id
             AND i.object_id = p.object_id) as number_of_rows
    FROM sys.indexes i
   INNER JOIN sys.objects o
      ON i.object_id = o.object_id
   WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
     AND OBJECTPROPERTY(o.object_id, 'TableHasClustIndex') = 0
)
SELECT COUNT(*) as cnt
  FROM CTE_1
 WHERE number_of_rows > 1000;
Select instances to collect from:
Select all
Choose databases to collect from:
All user databases
Set collection frequency:
Collect data every day
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:
Enabled

Alert definition

Alert name:
Tables without clustered indexes
Description:

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.

Note: Having a clustered index per table is NOT compulsory. There are cases, where a heap (table without a clustered index) is acceptable.

To analyze further, the following query can be used to find out which indexes don't have clustered indexes defined:

WITH CTE_1
AS
(
  SELECT db_name() as dbname,
         o.name as tablename,
         (SELECT SUM(p.rows)
            FROM sys.partitions p
           WHERE p.index_id = i.index_id
             AND i.object_id = p.object_id) as number_of_rows
    FROM sys.indexes i
   INNER JOIN sys.objects o
      ON i.object_id = o.object_id
   WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
     AND OBJECTPROPERTY(o.object_id, 'TableHasClustIndex') = 0
)
SELECT *
  FROM CTE_1
 WHERE number_of_rows > 1000;
Raise an alert when the metric value goes:
Above the defined thresholds
Default threshold values:
Set the thresholds as follows:
High:500
Medium:100
Low:0
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.
Raise an alert when the threshold is passed for:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 5.0/5 (1 vote cast)
  • So you have to add a distinct to the query because if you dont and you have multiple indexes on a table that is not clustered each index is a row in the result set giving a false count….

    WITH CTE_1

    AS
    (
    SELECT distinct db_name() as dbname,

    o
    .name as tablename,

    (SELECT SUM(p.rows)

    FROM sys.partitions p

    WHERE p.index_id = i.index_id

    AND i.object_id = p.object_id) as number_of_rows

    FROM sys.indexes i

    INNER JOIN sys.objects o

    ON i.object_id = o.object_id

    WHERE OBJECTPROPERTY(o.object_id, ‘IsUserTable’) = 1

    AND OBJECTPROPERTY(o.object_id, ‘TableHasClustIndex’) = 0

    )
    SELECT COUNT(*) as cnt

    FROM CTE_1

    WHERE number_of_rows > 1000;

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
    • Fabiano Amorim

       Hi Tony, thanks a lot for the comment, you are right, looks like I’ve forgot a filter here… You can do both, add a distict or a extra filter on “i.type = 0″…
      Here is the changed query:
      WITH CTE_1
      AS
      (
        SELECT db_name() as dbname,
               o.name as tablename,
               (SELECT SUM(p.rows)
                  FROM sys.partitions p
                 WHERE p.index_id = i.index_id
                   AND i.object_id = p.object_id) as number_of_rows
          FROM sys.indexes i
         INNER JOIN sys.objects o
            ON i.object_id = o.object_id
         WHERE OBJECTPROPERTY(o.object_id, ‘IsUserTable’) = 1
           AND OBJECTPROPERTY(o.object_id, ‘TableHasClustIndex’) = 0
           AND i.type = 0
      )
      SELECT COUNT(*) AS Cnt
        FROM CTE_1
       WHERE number_of_rows > 1000

      VA:F [1.9.17_1161]
      Rating: 0 (from 0 votes)
  • Linda

    I think a query to find wasted space in heaps would be even more helpful. Perheps help compel application teams to add a clustered index to all transactional tables that assures records are always appended at the end or bottom of the table. Although in SQL 2012 you can rebuild heaps, it still makes more sense to include a clustered index on all transactional tables. This would both assure tables are rebuild by standard index rebuild job, but also improves query performance in general. People forget heaps also don’t compress as well with row and page level compression and/or the sql native compressed backup. I typically find 30% or more wasted space in databases that haven’t added clustered indexes to their transactional table. Redgate could help us by providing script with an equation to identify wasted space in heaps. Especially where rows were deleted from a heap that have not been reclaimed by standard index rebuild maintenance job.

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)