Post reply

Missing Indexes in SQL Server 2005

  • September 15, 2008 at 9:48 pm

    #192831

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

  • September 15, 2008 at 11:28 pm

    #872133

    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.

    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)

  • September 16, 2008 at 12:42 am

    #872149

    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).

  • September 16, 2008 at 3:00 am

    #872200

    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

  • September 16, 2008 at 4:59 am

    #872247

    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!

  • September 16, 2008 at 6:33 am

    #872300

    GilaMonster (9/16/2008)

    It doesn't take into account existing indexes

    Gail, can you explain what you mean ??

    Kev

  • September 16, 2008 at 8:23 am

    #872408

    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

  • September 16, 2008 at 8:24 am

    #872409

    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]

  • September 16, 2008 at 8:27 am

    #872413

    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]

  • September 16, 2008 at 9:05 am

    #872454

    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]

  • September 16, 2008 at 10:48 am

    #872544

    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

  • September 16, 2008 at 6:56 pm

    #872768

    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

  • September 17, 2008 at 3:54 am

    #872896

    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.

  • September 17, 2008 at 3:56 am

    #872897

    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)

  • September 17, 2008 at 4:03 am

    #872899

    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 46 total)

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