Post reply

sys.dm_db_missing_index_details

  • June 23, 2009 at 8:41 am

    #211214

    Hi there

    I was wondering if anyone could shed some light on this system view: sys.dm_db_missing_index_details

    Should I be taking a the index suggestions with a pinch of salt or it is accurate? and I should trust the index suggestions it has made.

    I have been using it today and it tallies up pretty well with the indexes I think should be added, but some of the indexes I find a little strange where they have lots of included columns

  • June 23, 2009 at 9:13 am

    #1014028

    A block of salt would be better. Use it as a guide to determine what indexes may be needed. Review them carefully, you may find several that can be combined into one index instead of multiple indexes. I'd be careful about add all the included columns as well. I've had it suggest that I include over half of the columns from the table and that just doesn't make sense to me as that would make the index particularly large and require the system to update data in two locations.

  • June 23, 2009 at 9:31 am

    #1014038

    Thanks for your response, the reason I was a bit scepticle was for exactly the same reason you have mentioned it was suggesting a massive covered index over 10 columns, but however some of the other indexes do definitely make sense.

    This was my first post on here and I am considering moving over to this forum from my usual forum....

    Are there many question answering posters here? room for one more?

  • June 23, 2009 at 9:38 am

    #1014046

    Lynn Pettis (6/23/2009)

    I've had it suggest that I include over half of the columns from the table and that just doesn't make sense to me as that would make the index particularly large and require the system to update data in two locations.

    Sometimes it makes sense to do that, typically on high-read, low-write tables, but most of the time that many include columns is nonsensical.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 23, 2009 at 9:40 am

    #1014048

    Always room for more. Welcome aboard!

    I would also recommend reading the first two articles I reference in my signature block below. You may also find yourself recommending these articles to others yourself over time on this forum.

  • June 23, 2009 at 9:53 am

    #1014062

    GilaMonster (6/23/2009)

    Lynn Pettis (6/23/2009)

    I've had it suggest that I include over half of the columns from the table and that just doesn't make sense to me as that would make the index particularly large and require the system to update data in two locations.

    Sometimes it makes sense to do that, typically on high-read, low-write tables, but most of the time that many include columns is nonsensical.

    That is true, but I can tell you in the cases I was looking at, not a chance; high volumn OLTP system.

  • June 23, 2009 at 10:18 am

    #1014082

    Thanks very much 🙂

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

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