Post reply

Missing Index DMVs suggest an existing index

  • December 5, 2014 at 5:13 am

    #292603

    I've been looking to optimise queries in a database and so have been using the DMVs to find missing indexes. On this particular database, these show a huge potential benefit to adding a particular index.

    Seems simple enough, but actually that exact index already exists (and is trusted). I've been trying to find discussion of such cases but have failed. The only possibility I can come up with is that the index has no equality columns, just a single inequality column and some included columns so I wonder whether the DMVs don't recognise the existing index as the same as the suggestion.

    Has anyone encountered this situation?

  • December 5, 2014 at 5:32 am

    #1762932

    Yep, it's a documented issue, fixed in SQL 2012.

    http://www.sqlskills.com/blogs/paul/missing-index-dmvs-bug-that-could-cost-your-sanity/

    https://connect.microsoft.com/SQLServer/feedback/details/400578/query-plan-missing-index-recommendation-doesnt-check-if-an-index-actually-exists

  • December 5, 2014 at 5:59 am

    #1762939

    Gazareth,

    Thanks for the speedy reply.

    Actually, I forgot to mention my SQL version - worryingly, it is 2012 SP2.

    If it was fixed in 2012 RTM, has it perhaps been broken again in a subsequent service pack?

  • December 5, 2014 at 7:19 am

    #1762953

    That is worrying!

    Don't have any 2012 instances around to check on, so I'll resort to speculation - is the suggested index identical in the included columns, and their order too?

    Cheers

  • January 9, 2015 at 9:50 am

    #1769137

    Seems simple enough, but actually that exact index already exists (and is trusted).

    Unless I am mistaken, Trust is not an index property. It is a Foreign Key property.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • January 9, 2015 at 1:08 pm

    #1769187

    look at brent ozar's blitzindex, awesome tool and free. before you do what I did years ago and jump on the add index (unless this goes against sql you can verify is run multiple times) i would hold the results you get in a table. You can always get the onece a month query, one time query etc which will show up in most index analysis but needs to be taken with a grain of salt. I run blitzindex every 2 weeks and store the results in simple Acccess tables (after importing from Excel) If i see one showing up on a regular basis then I will act. This tool also shows you duplicate indexes, missing and unused. You would be stunned to see indexes which are really dupes which don't look that way (if you take Ozar's class on indexes gives a great explanation)

  • January 11, 2015 at 10:09 am

    #1769312

    Yes, Brent Ozar's resources are really handy - I do use them myself, but how is BlitzIndex going to identify missing indexes? It's going to use SQL Server's DMVs so, if there's a bug in them, it will more than likely affect the results of BlitzIndex too...

  • January 11, 2015 at 9:26 pm

    #1769387

    nharris (1/11/2015)

    Yes, Brent Ozar's resources are really handy - I do use them myself, but how is BlitzIndex going to identify missing indexes? It's going to use SQL Server's DMVs so, if there's a bug in them, it will more than likely affect the results of BlitzIndex too...

    Agreed.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply