Find clustered index on non primary key columns

By default when a primary key constrained is created on a table/view,  SQL Server automatically creates a unique clustered in order to enforce it.  And since a table can only have one clustered index, all the subsequent or any previous indexes created before that are created as a non-clustered index.

That works best in most cases and is the recommended best practice.

And decision to have clustered index on what columns affects everything about everyday working of an application. And also as a general best practice every table should have a clustered index, but its not required and there are cases where its best not to.

Scenario:

So now imagine a scenario where a table has the PK but the clustered index is on non PK columns. I am going to assume that there must be well thought-out index strategy for that particular table at the design time.

But over time the usage patterns may evolve and/or through endless enhancements, bug fixes etc. now that index may not be optimal. Of course that could be true for any index but the consequences are more severe if that's the case for a clustered index.

Now you are supporting that database in production mode. Users report that the query performance has gotten extremely slow and you also notice that the index optimization job is taking much longer to complete.

As part of your research and troubleshooting this issue, one of things you decide to check is index strategy already in place and you check 1) Are there any missing indexes 2) are the indexes of correct type (unique, clustered, non-clustered etc.), fill factor etc. 3) whether the clustered index is created on right columns etc....

The query that I have below is to find out if clustered index is on non-pk columns. I have consciously decided to exclude tables that have either no clustered index, no primary key or there is clustered as well as non-clustered index created on primary key columns.

-- CREATE A TEST TABLE
IF OBJECT_ID('dbo.tbl_test_ci_on_non_pk', 'U') IS NOT NULL
   DROP TABLE tbl_test_ci_on_non_pk
GO

-- ADD A CLUSTERED INDEX ON A NON-PK COLUMN
CREATE TABLE [dbo].[tbl_test_ci_on_non_pk](
	[id] [int] IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
	[name] [varchar](50) NULL)
GO
CREATE CLUSTERED INDEX [idx_ci_tbl_test_ci_on_non_pk_name] 
ON [dbo].[tbl_test_ci_on_non_pk]
([name] ASC)
GO

;WITH cte_indexes

     AS (SELECT db_name()                         db_name, 
                schema_name(o.schema_id)          schema_name, 
                object_name(i.object_id)          object_name, 
                o.type_desc                       object_type, 
                i.NAME                            index_name, 
                i.type_desc                       index_type, 
                i.is_primary_key, 
                o.object_id                       object_id, 

                pk_index_id   = (SELECT index_id FROM   sys.indexes c WHERE  c.object_id = o.object_id AND c.is_primary_key = 1),
                pk_index_name = (SELECT name FROM   sys.indexes c WHERE  c.object_id = o.object_id AND c.is_primary_key = 1),
                clustered_index_columns = COALESCE(( stuff((SELECT cast(',' + c.name AS VARCHAR(max)) 
                                                                FROM   sys.index_columns ic 
        INNER JOIN sys.indexes ii ON ii.object_id = ic.object_id AND ii.index_id = ic.index_id 
        INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id 
        WHERE  ( ic.object_id = o.object_id AND ic.index_id = i.index_id) 
        FOR xml path ('')), 1, 1, '') ), ''),

               pk_index_columns = COALESCE(( stuff((SELECT cast(',' + c.name AS VARCHAR(max)) 
                                                                FROM   sys.index_columns ic 
        INNER JOIN sys.indexes ii ON ii.object_id = ic.object_id AND ii.index_id = ic.index_id 
        INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id 
        WHERE  ( ic.object_id = o.object_id AND ii.is_primary_key = 1) 
        FOR xml path ('')), 1, 1, '') ), '')

         FROM   sys.objects o 
                INNER JOIN sys.indexes i ON o.object_id = i.object_id 
         WHERE  Objectproperty(o.object_id, 'ismsshipped') = 0) 

 
SELECT db_name, 
       schema_name, 
       object_type, 
       object_name,
       index_name  non_pk_clustered_index_name,
       pk_index_name,
       clustered_index_columns,
       pk_index_columns

FROM   cte_indexes 
WHERE  1 = 1 
       AND index_type = 'CLUSTERED' 
       AND pk_index_id ! = 1 
       AND clustered_index_columns != pk_index_columns
ORDER  BY object_name, 
          index_name 

Caveat:  I only considered the traditional index types (clustered, non-clustered, unique, non-unique etc.).

I have tested this on SQL Server versions 2008 R2 and above.