x

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.

more ▼

asked Oct 13, 2009 at 05:01 PM in Default

avatar image

user-110
53 1 1 3

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Oct 13, 2009 at 11:11 PM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(comments are locked)
10|1200 characters needed characters left

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?

more ▼

answered Oct 13, 2009 at 07:14 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left

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

More detail can be found in BOL here

more ▼

answered Oct 13, 2009 at 05:22 PM

avatar image

Kev Riley ♦♦
64k 48 61 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2076
x144

asked: Oct 13, 2009 at 05:01 PM

Seen: 3233 times

Last Updated: Oct 15, 2009 at 05:55 AM

Copyright 2016 Redgate Software. Privacy Policy