Missing Indexes in SQL Server 2005

  • srawant

    SSCommitted

    Points: 1829

    Am I understanding differently or is it the fact that to have a view on the missing indexes we gotta run the query first ? If so would it be useful to simply run a query just to know the missing indexes. Please can somebody throw a light on this...

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    srawant (6/22/2010)


    Am I understanding differently or is it the fact that to have a view on the missing indexes we gotta run the query first ? If so would it be useful to simply run a query just to know the missing indexes. Please can somebody throw a light on this...

    No, SQL 2005 (and later) keeps a recent history of performance statistics in memory, so missing indexes reported are those based on queries executed in the recent past or since last instance restart.

    So you don't have to run a query first, it has already kept a log of recently run queries, exec plans, missing indexes etc.

    __________________________________________________________________________________
    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]

  • Nitya

    Say Hey Kid

    Points: 699

    Its noteworthy to know that DMV's are a reflection of historical query data since SQL Server started.

    If you try running it right after restart, the results could be disastrous

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Nitya (7/23/2010)


    Its noteworthy to know that DMV's are a reflection of historical query data since SQL Server started.

    In this case it's since the database was opened. Taking the DB offline, autoclose, restoring, detach/attach will all clear the missing index DMVs (and several other DB-specific DMVs)

    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
  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    Memory pressure may also cause this information to be erased from the cache, so the time frame during which this info is maintained may be even shorter than the latest time database was started.

    __________________________________________________________________________________
    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]

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    Great article. Short, easy to understand, and helpful. Great follow on discussion to review some of the finer points of indexes and when to create & modify. I have been using some of these DMVs for several months, they are great tools. But with greatness comes responsibility so understand what you are doing and exercise care before proceeding.

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    bob.taylor (9/16/2008)


    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

    i think it's only in the R2 version. helped solve a performance problem a few weeks ago. for some reason devs think their queries are some state secret and it's a PITA troubleshooting problems

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    As great as "missing indexes" is, it is good to be aware of some pitfalls of the feature:

    http://msdn.microsoft.com/en-us/library/ms345485.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]

  • Reagan Boone

    Grasshopper

    Points: 13

    Someone else deserves credit for this "missing indexes" query, but I'm not sure where I found it and whether I've modified it much over the years:

    SELECT

    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,

    'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)

    + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'

    + ' ON ' + mid.statement

    + ' (' + ISNULL (mid.equality_columns,'')

    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END

    + ISNULL (mid.inequality_columns, '')

    + ')'

    + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,

    migs.*, mid.database_id, mid.[object_id]

    FROM sys.dm_db_missing_index_groups mig

    INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

    INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

    WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10

    ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

    Reagan Boone

  • Gail Shaw

    SSC Guru

    Points: 1004484

    alen teplitsky (7/23/2010)


    bob.taylor (9/16/2008)


    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

    i think it's only in the R2 version. helped solve a performance problem a few weeks ago. for some reason devs think their queries are some state secret and it's a PITA troubleshooting problems

    SQL 2008 does it (management studio, it doesn't depend on the server version, just the version of the tools)

    The information is in the XML plan in both 2005 and 2008, it's just that the 2008 management studio exposes it.

    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
  • Charles Kincaid

    SSChampion

    Points: 13593

    For both the Missing and Unused index tools it is good to remember that these are recommendations. The best tool is properly tuned squishware (that part of the system that exists between your ears).

    How many programmers does it take to change a light bulb? None; It's a hardware problem.

    How many therapists does it take to change a light bulb. None; The light bulb must change on its own. The therapist is only a guide.

    Likewise the missing index tool is a guide.

    ATBCharles Kincaid

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    ok

    i remember i had the R2 and it popped up in green when i did the execution plan. boss had SQL 2008 SSMS and he said he never saw it. after that he installed the R2 version

  • Gail Shaw

    SSC Guru

    Points: 1004484

    It's there in 2008.

    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
  • Charlie Fargis

    Grasshopper

    Points: 17

    Would someone kindly elaborate on sys.dm_db_missing_index_group_stats

    column user_scans. I tried all kinds of good and bad things in queries to bump user_scans in statistics and it doesnt budge. WHat will cause it to be non-zero?

  • SQLRNNR

    SSC Guru

    Points: 281252

    Nice article - good read.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 15 posts - 31 through 45 (of 47 total)

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