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

user-110 gravatar image

user-110
53 1 1 1

(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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.1k 19 21 74

thanks - I guessed that was the issue, just wanted to be validated by an expert.
Oct 14, 2009 at 10:01 AM user-110
(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

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Read the post. The general prob is I am basically tuning an inherited code base of 1000's of objects, so I have to generically use stats, etc and go with simple index tuning based on the dmvs. In the case I mention above, the LINENUMBER is one of two columns requested in the index, with ORDERID being added as an include. I could understand LINENUMBER (but have a hard time imagining what the dev was thinking not also querying ORDERID)... .. Why should ORDERID be included? Its right there in the index because its the clustered index..
Oct 13, 2009 at 09:23 PM user-110
I did read the post. A PK is not a clustered index, necessarily - and this is not something you put in your post. When you say 'one of the two columns requested' do you mean two key columns?
Oct 14, 2009 at 05:51 AM Matt Whitfield ♦♦
Sorry - "I Read the post" I wasn't directing you too read it, I think you had a post in it anyways.... I know a PK is not a clustered index, thus I cleared that up - yes - two columns comprise the PK AND clustered index. Just curious about why the include - Grant cleared it up.
Oct 14, 2009 at 09:21 AM user-110
Ahhh - I think you might have missed the 'I' off the start! :) No worries.
Oct 14, 2009 at 09:43 AM Matt Whitfield ♦♦
(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

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

But the key data is already in the index? Not explicitly mind you.
Oct 13, 2009 at 05:28 PM user-110
The PK field is in the equality/inequality columns?
Oct 13, 2009 at 05:36 PM Kev Riley ♦♦

PK/Clustered index are one in the same in this case. Table is OrderLine, key is ORDERID, LINENUMBER. missing index stats comes up with indexes like LINENUMBER, somefield , include(ORDERID, etc,etc)

ORDERID,LINENUMBER are already in any NC index I make, as they are the link back to the data should I need to do a bookmark etc. Just can't pinpoint what the purpose would be to include.
Oct 13, 2009 at 09:16 PM user-110
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1833
x127

asked: Oct 13, 2009 at 05:01 PM

Seen: 2933 times

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