Why is DTA recommending non-clustered indexes that match the clustered Primary Key index

indexindex-tuningnonclustered-index

I'm only vaguely familiar with SQL Server's Database Engine Tuning Advisor (DTA) but I'm using it to suggest recommendations for indexes on my database.

Most of the tables in the database at present have a single index which is the Primary Key. It is defined during the tables' creation as:

CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([ID] ASC)

When the DTA runs, it is advising that I add additional non-clustered indexes on some tables containing only the [ID] column, e.g.:

CREATE NONCLUSTERED INDEX [_dta_index_Employee_18_1170844725__K1] ON [dbo].
[Employee]
(
    [ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

What I'm wondering is why it's recommending I create an index which exactly matches an index that already exists. Is there some benefit to the query optimizer of there being a non-clustered index as well as the clustered one?

For what it's worth, in most instances the [ID] column is also an IDENTITY column.

Best Answer

The index does not match exactly what already exists. The Clustered index (the primary key) contains all the other data related to the table. But the nonclustered index (NCI) contains just the primary key. The resulting NCI is much narrower than the clustered index.

Having a non-clustered index on just the primary key has a few benefits that may be relevant here. But, I also want to caution you to take the DTA and the suggested missing indexes with a grain of salt.

Benefits of having a non-clustered index in addition to the clustered index (on the same key).

  • Foreign Key Check Constraints - If other tables use this one as a foreign key reference, then having a narrow NCI will speed this up dramatically.
  • COUNT or EXISTS - If you frequently check for the existence of this value (or count them) without any other conditions, then having an NCI will also be beneficial. Having a narrower index to use for these is extremely efficient, especially if the base table is very wide.

Basically, even though both would be seek operations, if the NCI can be used it will typically be very narrow and can fit more ID values per page on disk so will reduce memory grants.

Related Question