Post reply

Problem Query

  • August 2, 2011 at 3:58 pm

    #1362733

    jez.lisle (8/2/2011)

    All, Thanks for the help and advice... erm where to start with the replies 🙂

    ...

    LutzM - How can I tell if there are missing Indexes?

    ...

    Jez

    Here are some alternatives:

    If it's a query that is called frequently, you could query sys.dm_db_missing_index_details and sys.dm_db_missing_index_group_stats or search the script section of this site for either one or for "missing index" to get a script.

    You could run the code and capture the XML Showplans (using profiler), which include a "MissingIndexes" element. The tool mentioned earlier will help you to analyze the actual execution plan (or XML Showplan). SSMS will also show a Missing Index information, but this will always be only one (and I haven't figured the logic which one is selected if there are more than one missing)

    The most difficult approach (at least from my perspective) is to analyze the query itself and check if the tables involved provide a covering index. This will get more and more complex with OR conditions or nested joins and I guess there'll be just a few people being able to check if the indexes available will support the "monster query" without using any of the "little helpers".


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • August 2, 2011 at 4:03 pm

    #1362738

    The XML contains more than 1 missing index. In the properties window you can see more than 1 IIRC.

    It's the ssms GUI that seems to only show the 1st one in the graphical plan.

Viewing 2 posts - 16 through 16 (of 16 total)

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