I am performing index maintenance on a database. I have run SQL script that uses DMVs to provides a list of indexes SQL Server believes should be created. I am not creating indexes just because the index is listed as missing. My question, Why would the primary key column be listed as a missing index? The table is arranged in primary key order, correct? Why would SQL Server beleive a noclustered index should be created on the primary key?

Thanks in advance.

  • 1
    Are you sure the recommended index is on just the primary key column (and no INCLUDE columns or filters)? Is the primary key clustered? – Aaron Bertrand Mar 19 '14 at 2:38

The primary key in SQL Server is by default also the clustering key - and the data is physically ordered by the clustering key in a table.

So if this primary key is listed, maybe it's because it is a non-clustered primary key - something that you have to do explicitly - but it's entirely possible.

The primary key is a logical construct - it's job is to make sure each row in a table can be uniquely and reliably identified. It has no impact whatsoever on the physical structuring of the table.

The clustering key (and thus the clustered index) is what determines the physical ordering of the data in a SQL Server table. The table's primary key is the clustering key by default - but it doesn't have to be - you can have two totally separate sets of columns for the primary key and the clustering key.

But all these things aside: the DMV's that identify missing indexes aren't perfect - do take their recommendations with a grain of salt! Don't just blindly implement everything that DMV recommends - sometimes it recommends stuff that's already in place, or some of its recommendations are even contra-productive! Don't blindly trust it - it's a helping tool - but it's not perfect!

| improve this answer | |

As Marc mentioned, the primary key is a logical construct and doesn't by itself indicate the clustering key. I won't repeat that info in my answer here, but if you want a deeper explanation of the differences between the structures behind clustered and non-clustered indexes, I have a video here which may be helpful for you.

Now, it's entirely possible for the missing index DMVs to (correctly) tell you that an additional index on the primary key column alone is something that could have helped a query. Why?

If a query needs to scan all the PK column values in the table (and only that), it's more (perhaps much more) expensive to scan either the clustered index or another non-clustered index to get that information. A narrow index that only contains the PK column would be more efficient than that, particularly if there's a merge join involved where the key order is being exploited.

And so the missing index algorithm kicks in and suggests that the narrower index on only the column(s) it needs would be helpful.

The course of action to resolve the suggestion is dependent on the queries that are running and what other indexes you have, and getting into that is beyond the scope of this question, so I'll leave it there.

Where the missing index DMVs aren't so helpful is when it suggests slightly narrower indexes that match indexes you already have. It's also known in some cases to incorrectly suggest exact duplicates of indexes that already exist. For these reasons, you're right to filter the DMV output through your own brain and judgement instead of automatically creating the indexes it suggests.

| improve this answer | |

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.