Columns recommended, but already there

anushahanna
anushahanna used Ask the Experts™
on
DMV recommends 3 different versions of an index based on usage:
1)
Equality_Columns: Col1,COl2
Inequality_Columns: Col3
Included_Columns: Col4,Col5,Col6
2)
Equality_Columns: Col1,COl2
Inequality_Columns: Col3
Included_Columns: Col4,Col5,Col6,Col7
3)
Equality_Columns: Col1,COl2
Inequality_Columns: Col3
Included_Columns: Col4,Col5,Col8,Col9,Col10

The actual index is on Col2,COl1,Col3; no included columns

Apparently the index is not being used, what could be the reason?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
sorry the title should have 'Index recommended', not 'Columns recommended'.
Chief Technology Officer
Most Valuable Expert 2011
Commented:
Order of the columns in the index matters if I am not mistaken.  You have col2, col1 and col3 whereas the recommendation is col1 then col2.

Author

Commented:
Kevin
In one of my older posts,
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24880475.html
25773024 (dbbishop) & 25773562(aneesh) mentioned that optimizer would take care of the ordering of the where clause. that is why i was expecting the index to be used since all the 3 columns are indeed present.

thanks for your input.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Top Expert 2012
Commented:
Then you had better read through that thread again.  That is not what they said.

But the truth of the matter is that you already know the answer to will it use your index and if you take time to re-read that thread you referred to you will see that is to be expected.
Top Expert 2012

Commented:
But rather than just hypotheticals, why don't you post the real CREATE TABLE structure of your table with CREATE INDEX as well as the Execution Plan for your query.

Author

Commented:
dbbishop:
the optimizer will be able to determine that you have used all the columns in the index
---------------------
I got the recos from dm_db_missing_index_group_stats, and based on the columns it recommended, I went to the table and found that the index exists. I'll have to dig up the query behind it...
As acperkins said, the order of the columns makes a substantial difference. The leading column of your index is Col2. The leading column of all of those recommended indexes is Col1. That makes your index totally different to the recommended one.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial