do you ever get crazy results with the missing indexes DMV's?

  • so back in september i started running the missing index DMV's once a day and then running reports from the data to see where new indexes can be created.

    i finally get to one of the tables/indexes and it's a table with 216,000 rows. it wants me to create an index on a column that is a tinyint. i run a select distinct on it just to be sure and get back 8 rows.

    and my report filters the data so i only see recommended indexes for anything over 200 user seeks

    and then a week ago i created new recommended indexes in the Blackberry server database and it's still asking for the same ones to be created.

  • Yes, I've seen similar behavior, although not exactly the same as your first example I have seen the second one.

    2 things I've learned about the DMV Index recommendations. Don't accept them as the truth and use some intelligence when creating indexes based on the recommendations.

    I ALWAYS test the indexes. I try find the query(s) that caused SQL to recommend them, and see if adding them does actually improve performance. I also go through all similar looking indexes and see if I can find one or two that will satisfy multiple recommendations.

    It looks like the 2008 version has been tweaked a bit and seems to be more accurate.

    Cheers

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • You must test indexes suggested by the missing index process. It is absolutely not to be blindly trusted. I've seen it recommend an index on Column A and an Index on Column B, and an index on Column A & B, and an index on Column B & A, and an index on Column A with an include of Column B, and an index on Column B with an include of Column A. I'd propose that many of those suggested indexes are utter junk. Evaluate and carefully test any recommendations made by the missing index DMV.

    One more thing, have you tried to match the missing index DMV information to a particular query? I mean, the index suggested might be for a query that was run once and won't ever be run again. The suggested index right next to it might be for a query that gets called hundreds of times in a minute.

    The missing index DMVs are a slightly useful tool, but you need to be very careful in their application.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • and then a week ago i created new recommended indexes in the Blackberry server database and it's still asking for the same ones to be created.

    Paul has a blog about this bug in the DMV.

    http://www.sqlskills.com/BLOGS/PAUL/post/Missing-index-DMVs-bug-that-could-cost-your-sanity.aspx

Viewing 4 posts - 1 through 3 (of 3 total)

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