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.
