1

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.

1 Answer 1

3

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.

3
  • I hadn't appreciated these difference between the CI and NCIs, thank you! Jul 26, 2019 at 12:25
  • Is there any benefit to DELETE's? I'm seeing some practical improvement on batch deletes (via while loop) while including an NCI on a DateTime column despite that column already being indexed in the CI with the primary key and DateTime col.
    – cvocvo
    Dec 19, 2019 at 19:22
  • Depending on the where clause for your delete statement, it can help quite a bit. If you are deleting by the value in the DateTime column then having an index with the DateTime column first will speed that up dramatically. Having (ID+Datetime) as the clustering key doesn't help with finding entries for specific DateTime values. Dec 19, 2019 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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