Missing Indexes in SQL Server 2005

  • Ranga N

    SSChasing Mays

    Points: 642

    Comments posted to this topic are about the item Missing Indexes in SQL Server 2005

  • Jeff Moden

    SSC Guru

    Points: 994682

    Great info! I gotta try it. Thanks.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • marshall.jones

    SSC Enthusiast

    Points: 167

    this is super handy.

    does anyone know if i can truncate these tables at any time safely (for example before running a test load for example).

  • Gail Shaw

    SSC Guru

    Points: 1004446

    The missing indexes DMVs are reset when the server starts. They can't be modified by a user.

    One thing I would like to add is that the missing indexes DMVs should (like the Database Tuning Advisor) be taken as a suggestion and tested carefully before been applied. The missing indexes are less accurate than the DTA, as only single queries are considered when entries are added to that (the query optimiser adds the entries as it's optimising queries).

    It doesn't take into account existing indexes, it doesn't compare with other similar suggestions already in the DMV and it doesn't considered clustered/nonclustered indexes.

    They are very, very useful for index tuning, they just shouldn't be applies without consideration.

    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
  • SuperDBA-207096

    SSCrazy Eights

    Points: 8176

    GilaMonster (9/16/2008)


    They are very, very useful for index tuning, they just shouldn't be applies without consideration.

    Good point! Remember every action has consequences, so be sure to test!

  • kevriley

    SSCrazy Eights

    Points: 8907

    GilaMonster (9/16/2008)


    It doesn't take into account existing indexes

    Gail, can you explain what you mean ??

    Kev

  • Charles Kincaid

    SSChampion

    Points: 13593

    Good article. Great starting point. I one time saw a script that used this info to build the suggested indexes.

    There is also some DMV about unused indexes. I'd love to see a set of scripts that would build the missing and deleted the unused.

    ATBCharles Kincaid

  • RBarryYoung

    SSC Guru

    Points: 143327

    Great article, I'm looking forward to using this.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    Gail already mentioned some of the caveats of the missing-index DMVs.

    Here are a couple more:

    (1) Because the data in the DMVs are kept in memory, they are at risk of being flushed from the cache if there is memory pressure. Therefore, the DMVs need to be polled regularly and stored in a database for later analysis. This is something Gail has mentioned before in one of the posts here.

    (2) The DMVs are no substitute for a strategy of regular index defragmentation and statistics updating. The optimizer may make the wrong choices when it constructs the 'best' execution plans if indexes are defragmented and/or the stats is not up-to-date and accurate. This can cause "wrong" data to be deposited to the DMVs and therefore erroneous information.

    I absolutely love the DMVs. They have opened a much-needed window into the internals of the database engine and have made DBA work all the more interesting. I use them all the time.

    We just need to keep these limitations in mind as well.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    See this great link on the same topic:

    Uncover Hidden Data to Optimize Application Performance:

    http://msdn.microsoft.com/en-us/magazine/cc135978.aspx

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Gail Shaw

    SSC Guru

    Points: 1004446

    kevriley (9/16/2008)


    GilaMonster (9/16/2008)


    It doesn't take into account existing indexes

    Gail, can you explain what you mean ??

    Kev

    If there's an existing index on a table on (for example) columns A, B, C and then the optimiser optimises a query and decides that an index on A, B INCLUDE D would be very useful, it will suggest that as a new index even though just adding D as an include to the existing index would work just as well.

    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
  • bob.taylor

    Newbie

    Points: 7

    And in SQL Server 2008, it gets even easier. When you display an actual execution plan in SQL Server Management Studio query windows, you will receive a message in green indicating the missing index information!

    boB Taylor, MCA: Database

  • Rob Reid-246754

    Hall of Fame

    Points: 3901

    In answer to Charles request to see a script that puts all this info into use with a script that runs on a timed basis that creates a list of index recommendations to create and drop.

    http://blogs.msdn.com/queryoptteam/archive/2006/06/01/613516.aspx

    I use it on my system and its a good tool for advice but as Gila said you will often find that you get

    very similar recommendations from it.

  • Manie Verster

    SSCertifiable

    Points: 7020

    Very good article! I immediately copied the code and ran it on my database but I have a couple of questions if anyone can help me please. I do not quite understand inequality_columns, equality_columns and included_columns. Which of these columns should be used to test your indexes? 'Scuse me if I sound a bit dumb but I am still learning about these things.:w00t::w00t::w00t::w00t::w00t:

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • Manie Verster

    SSCertifiable

    Points: 7020

    Hi all,

    Just after I posted my reply on this web I came upon another website (MSDN) where someone posted a blog on this exact same article. Visit it and let me know what you think.

    http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

Viewing 15 posts - 1 through 15 (of 47 total)

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