Query to find Nonclustered Index clashing with Clustered Index

It’s being quite a long time when I made the last post. Actually, I was lacking a topic that should trigger me to write. Finally, I got one when I was working on one of the Performance Tuning assignments.

Performance Tuning is a mix of wisdom and exploring the unknown, especially when you have a time-bound project. I’m going to share an interesting finding. I came across a scenario where there was an index, a nonclustered one, whose first key column was similar to that of the clustered key column.

If there is a nonclustered index, with the initial columns of the nonclustered index key, matching with the clustered index column key columns, then there will clustered index seek, instead of nonclustered index seek. This is pretty clear! Isn’t it?

It may not affect the read performance, but will surely impact the write performance, especially when you have millions of rows to be written using INSERT, UPDATE and DELETE commands. The situation would worsen further if such a nonclustered index is a covering index (have included columns).

It made me explore how many similar indexes do exist in the database, and I wrote the below script. I hope you would find it useful!

; WITH cte_clustered_index
AS
(
	SELECT C.name AS TableName
		, A.name AS IndexName
		, D.name AS ColumnName
		, B.key_ordinal
	FROM sys.indexes A
		INNER JOIN sys.index_columns B
			ON B.object_id = A.object_id
			AND B.index_id = A.index_id
		INNER JOIN sys.tables C
			ON C.object_id = A.object_id
		INNER JOIN sys.columns D
			ON D.object_id = B.object_id
			AND D.column_id = B.column_id
	WHERE A.type_desc = 'CLUSTERED'
		AND A.is_hypothetical = 0
)
, cte_nonclustered_index
AS
(
	SELECT C.name AS TableName
		, A.name AS IndexName
		, D.name AS ColumnName
		, B.key_ordinal
	FROM sys.indexes A
		INNER JOIN sys.index_columns B
			ON B.object_id = A.object_id
			AND B.index_id = A.index_id
		INNER JOIN sys.tables C
			ON C.object_id = A.object_id
		INNER JOIN sys.columns D
			ON D.object_id = B.object_id
			AND D.column_id = B.column_id
	WHERE A.type_desc = 'NONCLUSTERED'
		AND A.is_hypothetical = 0
		AND B.is_included_column = 0
)

SELECT CL.TableName
	, CL.IndexName AS Clustered_Index_Name
	, NCL.IndexName AS Nonclustered_Index_Name
	, STUFF
            (
                (
                    SELECT ', ' + ColumnName 
                    FROM cte_clustered_index TMP
                    WHERE TMP.TableName = CL.TableName
						AND TMP.IndexName = CL.IndexName
					ORDER BY TMP.key_ordinal ASC
                    FOR XML PATH ('')
                ), 1, 2, ''
            )	AS Clustered_Index_Key
	, STUFF
            (
                (
                    SELECT ', ' + ColumnName 
                    FROM cte_nonclustered_index TMP
                    WHERE TMP.TableName = NCL.TableName
						AND TMP.IndexName = NCL.IndexName
					ORDER BY TMP.key_ordinal ASC
                    FOR XML PATH ('')
                ), 1, 2, ''
            )	AS Nonclustered_Index_Key
	, STUFF
            (
                (
                    SELECT ', ' + CAST(TMP.key_ordinal AS VARCHAR)
                    FROM cte_nonclustered_index TMP
                    WHERE TMP.TableName = CL.TableName
						AND TMP.ColumnName = CL.ColumnName
					ORDER BY TMP.key_ordinal ASC
                    FOR XML PATH ('')
                ), 1, 2, ''
            )	AS Key_Ordinal_Of_Clustered_Index_Key_Columns_In_Nonclustered_Index_Key
FROM cte_clustered_index CL
	INNER JOIN cte_nonclustered_index NCL
		ON CL.TableName = NCL.TableName
WHERE EXISTS (SELECT 1 FROM cte_clustered_index CL WHERE CL.TableName = NCL.TableName AND CL.ColumnName = NCL.ColumnName)

The output of the query will be similar to as can be seen in the image below. It will list down all the nonclustered indexes, which includes key columns as that of the clustered index. It will also list the key columns of both – nonclustered index, and the corresponding clustered index, along with the key ordinal of the clustered index key columns in the respective nonclustered index.

It is now up to you to decide which one makes sense, and which doesn’t. Take the decision accordingly and delete the ones that do not make sense.  

One thought on “Query to find Nonclustered Index clashing with Clustered Index

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s