5

Does it ever make sense to have a clustered and non clustered index for the same column

I inherited a database and a web service that goes with it.

Looking at the indexes for duplicates I see an two indexes that point at the same column (1 column and sorted the same way). One is clustered and one is not, that is the only difference I see.

  1. Does it ever make sense to keep both of these?
  2. If I delete one which one should I delete
11

Before addressing the two questions, let me briefly define the difference between clustered & non-clustered indexes.

The clustered index is organized by the key columns. It also includes every other column as part of the row structure (ie, it has the entire row).

The nonclustered index is also organized by the key columns. It implicitly includes the clustering key columns (if the table is clustered), or a pointer to the row (if the table's a heap). If any INCLUDE columns are explicitly specified, they will also be included in the index structure.

Does it ever make sense to keep both of these?

There is a corner case where it makes sense to have a non-clustered index "duplicating" the clustered index. If you have a query that frequently scans the table, and ONLY makes use of the clustering key column, the query optimizer will prefer to use the non-clustered index. The non-clustered index does not contain the full row data, and thus it will take up less physical space. Because it takes up less physical space, SQL Server can scan the table with fewer IOs, and will make use of it for performance reasons.

You can query sys.dm_db_index_usage_stats, you can see the number of user_seeks and user_scans on the two indexes. This will help you see how the two indexes are being used, and determine the usefulness of the two indexes.

If I delete one which one should I delete?

As a general rule, most tables should have a clustered index. (That's a whole other topic). Not knowing any details about your index usage or data access, I'd guess that if you were to drop one, you would drop the nonclustered index (but, that really is a guess based on what I know).

Depending on the clustering key, the data, the data access patterns, etc., the most correct answer might be to drop the clustered index and create a different clustered index. You may want to read more on Effective Clustered Indexes.

  • I did look at the usage and they are both being used. The non clustered appears to have more usage but both have usage. So maybe it is ok. – Maestro1024 Aug 17 '16 at 1:53
  • 1
    Are they both being used HEAVILY? The usage DMV is cumulative, so I'd suggest looking at it 24 hours apart, and calculating the difference over 1 day. If the non-clustered index is being used frequently, you may want to try to track down what code is using that index, and test it without the nonclustered index. – AMtwo Aug 17 '16 at 1:58
1

I am not sure there is a safe or good answer without knowing the index stats. You really should start by seeing how much of each is actually used, or not used, then determine what should be kept or not. This is something I use when determining what indexes are or are not being used.

CREATE TABLE #IndexStats
(
ObjectName VARCHAR(200),
    IndexName VARCHAR(200),
    UserSeeks BIGINT,
    UserScans BIGINT,
    UserLookups BIGINT,
    UserUpdates BIGINT
)

INSERT INTO
    #IndexStats
SELECT
    OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
    I.[NAME] AS [INDEX NAME],
    USER_SEEKS,
    USER_SCANS,
    USER_LOOKUPS,
    USER_UPDATES
FROM
    SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN
    SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID
WHERE
    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
ORDER BY
    USER_LOOKUPS DESC

SELECT
    s.[name] AS 'Schema',
    t.[name] AS 'Table',
    i.[name] AS 'Index',
    p.avg_fragmentation_in_percent AS 'Fragmentation Percent',
    p.page_count AS 'Page Count',
    i.fill_factor AS 'Fill Factor',
    i.is_padded AS 'Padding',
    IDX.UserLookups,
    IDX.UserScans,
    IDX.UserSeeks,
    IDX.UserUpdates
FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS p
INNER JOIN
    sys.tables t ON t.[object_id] = p.[object_id]
INNER JOIN
    sys.schemas s ON t.[schema_id] = s.[schema_id]
INNER JOIN
    sys.indexes AS i ON i.[object_id] = p.[object_id] AND p.index_id = i.index_id
JOIN
    #IndexStats IDX ON IDX.ObjectNAME = t.name
WHERE
    p.database_id = DB_ID()
    AND p.avg_fragmentation_in_percent >= 50
ORDER BY
    p.avg_fragmentation_in_percent DESC, IDX.UserLookups ASC

DROP TABLE #IndexStats

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.