question

user-110 avatar image
user-110 asked

SQL 2008 - missing indexes

My question surrounds the missing index DMVs.

If you take any good missing index DMV - Bart Duncan's for example http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

and run it against any DB, one thing that puzzles me is that the PK of the table is frequently in the "include columns" list.

Why is this? The PK is already there, why does it suggest it be "included" as well.

Thanks for the insight.

sql-server-2008indexing
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

Largely it's because the optimizer isn't thinking things through the way you would. It sees that it would like to have that column and adds it to the INCLUDE list. This is why almost every time you see someone show just how slick and cool the missing index information is, they usually add all kinds of caveats about testing, verification, double-checking, etc.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

Primarily to create covering indexes, and avoid key/bookmark lookups.

More detail can be found in BOL here

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

Read the answers to this question, then ask yourself - is the PK column part of the index that I am using to look up data with? Or is it part of a separate index which needs to be referenced separately?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.