We have a primitive junction table to store a many-to-many relation between two other tables, their primary keys being bigint:

CREATE TABLE [dbo].[JunctionTable] (
    [ID-1] [bigint] NOT NULL,
    [ID-2] [bigint] NOT NULL,

When the Tuning Advisor was run on the database, it suggested (among many others, obviously) the following index to be added:

CREATE NONCLUSTERED INDEX [_dta_index_JunctionTable_8_296569782__K1] ON [dbo].[JunctionTable] 
    [ID-1] ASC

And I wonder: is this index good for anything? AFAICT it is the left half of a clustered primary key and the space reduction coming from omitting a single bigint cannot be that important, I guess. Or, is there any scenario when this index would help? And if not, why would the DTA suggest it? (The Advisor suggested many new indexes which I consider silly, but this one seemed the silliest, so I thought I ask…)

  • 2
    "And if not, why would the DTA suggest it?" Because it's rather dumb sometimes. – ypercubeᵀᴹ Sep 24 '13 at 15:54
  • Important: does the JunctionTable have only these 2 columns? – ypercubeᵀᴹ Sep 24 '13 at 15:56
  • 3
    Did you give the DTA a representative workload? Including statements that modify JunctionTable? – Martin Smith Sep 24 '13 at 16:49

The Database Tuning Advisor does not always make sensible suggestions. It is always a good idea to review any suggestions made (by any tool more that matter).

In general, the cost of an index seek is directly related to the depth of the B+Tree that makes up that index. Seeking in an index that is 8 levels deep is 4 times as expensive as seeking in an index that is only 2 levels deep. The depth of the index is strongly correlated with the length of the index key(s). Other important factors are uniqueness of the index, the length of the clustered index key and wether there are any included columns.

If you create an index on one column you generally get a index less deep than if you create an index on that same column and an additional column. However, in your case, as the index on ID-1 is not declared unique (it can't be), the clustered index key automatically becomes part of the key. Assuming there are no other columns, that additional index will be an exact copy of the clustered index. If you however have additional columns in the table, you might end up with a small difference in index depth. But even then it is usually not worth creating the index.

The difference becomes bigger if you have a lot of additional columns and you are looking at queries that would scan the new index like this one:

  FROM [dbo].[JunctionTable] 

As all those additional column are not part of the index the scan has to go through a lot less pages. If you for example had two additional DATETIME columns in that table, the index scan speed would probably double with that new index. But you would have to run this query very often for the index to be worth the additional maintenance overhead.

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.