missing indexes dmv and selectivity

When the missing indexes dmv suggests an index, it gives the equality and inequality columns. Has it already ordered the columns based on the selectivity?

thanks
LVL 6
anushahannaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Has it already ordered the columns based on the selectivity?

Yes and excerpt from MSDN here:

"Use the following guidelines for ordering columns in the CREATE INDEX statements you write from the missing indexes feature component output:

* List the equality columns first (leftmost in the column list).
* List the inequality columns after the equality columns (to the right of equality columns listed).
* List the include columns in the INCLUDE clause of the CREATE INDEX statement.
* To determine an effective order for the equality columns, order them based on their selectivity; that is, list the most selective columns first."
http://msdn.microsoft.com/en-us/library/ms345405.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anushahannaAuthor Commented:
>>To determine an effective order for the equality columns, order them based on their selectivity; that is, list the most selective columns first."

Is it recommending us to figure out the selectivity?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Yes, if a particular index involves more that one column like

Index1 - col1, col2

Then this index can be used by the following two queries which has WHERE conditions
* Where col1 = ?
* Where col2 = ? and col1 = ?

If you have the order of your index as col2, col1, then this index would not be used by first query.
Hope this clarifies.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

anushahannaAuthor Commented:
*Do the DMV does not check the selectivity for us; we have to, manually?
*let's say the index is col2, col1 order. But if selectivity is 0.6, 0.8 respectively, we should ask the programmer to rearrange his query?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> *Do the DMV does not check the selectivity for us; we have to, manually?

In other words Yes, based upon the selectivity it suggests you the order of the columns based upon the queries till now executed on that table

>> *let's say the index is col2, col1 order. But if selectivity is 0.6, 0.8 respectively, we should ask the programmer to rearrange his query?

Can you explain more on this..
0
anushahannaAuthor Commented:
Thanks.

I meant the index is based on 2 keys: col2, col1. The selectivity in the columns are 60% and 80% respectively for the 2 columns. Let's assume the query uses "Where col2 = ? and col1 = ?"- but see that the selectivity for col1 is better than col2. Is this OK, or should the programmer try to change the query to "Where col1 = ? and col2 = ?" and also change the index to col1,col2 order, because of the greater selectivity in col1.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Then the order should be col1, col2 since col1 has 80% selectivity compared to col2 with 60% selectivity.

>> and also change the index to col1,col2 order, because of the greater selectivity in col1.

Yes..Order in WHERE clause doesn't makes sense as SQL Server would try to use the index in an optimal way..
That's where optimizer plays a major role in the selection of indexes.
0
anushahannaAuthor Commented:
OK. So 'Where col2 = ? and col1 = ?' can stay the same, but the index should be dropped and redone with the correct order, right?

but if the DMV suggested this index, it would have already checked the selectivity, and put the col1 as the main key, right?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> So 'Where col2 = ? and col1 = ?' can stay the same, but the index should be dropped and redone with the correct order, right?

Yes, just drop it and recreate index with correct order.

>> but if the DMV suggested this index, it would have already checked the selectivity, and put the col1 as the main key, right?

yes, it would.
0
anushahannaAuthor Commented:
Thanks very much for your guidance.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.