Post reply

check missing index

  • May 13, 2013 at 4:38 pm

    #394767

    Is there some good queries for checking missing indexes on all tables of a database?

    THanks

  • May 14, 2013 at 2:37 am

    #1614861

    try below query

    SELECT

    mid.statement

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

    '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

  • May 14, 2013 at 3:01 am

    #1614873

    Do not automatically create all the indexes that the missing index DMVs suggest, that's a good way to severely over-index and hinder performance.

    Evaluate what the missing index DMVs suggest, examine the existing indexes, test the suggestions, implement what's useful.

    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

  • May 14, 2013 at 4:09 am

    #1614908

    And remember, the missing index information, as is, doesn't relate directly to any query at all. So simply taking that stuff as written, you might be creating an index for a query that was run once and then never again.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

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