SQL Server Performance

missing index

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by jit1999, Mar 31, 2007.

  1. jit1999 New Member

    how to find out missing index on table
  2. MohammedU New Member

  3. satya Moderator

    DMVs are very helpful in SQL 2005 and Mohammed's reference is right on track.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  4. MichaelB Member

    Here's a nice one already done up for you!

    The following select shows you the indexes SQL 2005 server (portfolio) thinks could use some index tuning. The fourth column from the left shows us what impact the user can expect to have - not always 100% right on, but it gives some idea. The second column really tells us usage.


    select
    unique_compiles,
    User_seeks,User_scans,
    avg_user_impact,
    equality_columns,
    inequality_columns,
    included_columns,
    [statement],
    last_user_seek,
    avg_total_user_cost
    from
    sys.dm_db_missing_index_details d
    inner join
    sys.dm_db_missing_index_groups g ON
    d.index_handle = g.index_handle
    inner join
    sys.dm_db_missing_index_group_stats s ON
    g.index_group_handle = s.group_handle
    order by
    left (avg_user_impact,1) desc,
    user_seeks desc,
    unique_compiles desc;

    Michael
    MCDBA

    "The fear of the Lord is the beginning of knowledge,
    but fools despise wisdom and instruction." Proverbs 1:7
  5. rohit2900 Member

    Hi,
    when I ran below query I got the result now my question is, How should I interpret the result as in the result I'm having equality_columns, inequality_columns, included_columns which represents the column names where the indexes are missing on a given table but on which column I should I create the index....
    select * from sys.dm_db_missing_index_details

    Rohit
  6. MartinSmithh New Member

  7. satya Moderator

Share This Page