index advantage query

  • could anny one please explain me the meaning of equality_columns, inequality_columns, included_columns,

    unique_compiles, user_seeks,avg_total_user_cost, avg_user_impact i the which column should i use the index

    SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek,

    mid.statement as 'Database.Schema.Table',

    mid.equality_columns, mid.inequality_columns, mid.included_columns,

    migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact

    FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)

    INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)

    ON migs.group_handle = mig.index_group_handle

    INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)

    ON mig.index_handle = mid.index_handle

    ORDER BY index_advantage DESC;

  • Equality and inequality columns are explained in the Microsoft documentation for sys.dm_db_missing_index_details;k%28SQL11.SWB.TSQLRESULTS.F1%29;k%28SQL11.SWB.TSQLQUERY.F1%29;k%28MISCELLANEOUSFILESPROJECT%29;k%28DevLang-TSQL%29&rd=true



    Comma-separated list of columns that contribute to equality predicates of the form:

    table.column =constant_value



    Comma-separated list of columns that contribute to inequality predicates, for example, predicates of the form:

    table.column > constant_value

    Any comparison operator other than "=" expresses inequality.

    Using Missing Index Information in CREATE INDEX Statements

    To convert the information returned by sys.dm_db_missing_index_details into a CREATE INDEX statement, equality columns should be put before the inequality columns, and together they should make the key of the index. Included columns should be added to the CREATE INDEX statement using the INCLUDE clause. To determine an effective order for the equality columns, order them based on their selectivity: list the most selective columns first (leftmost in the column list).

    So you should create the index based on equality columns, then inequality (if any) and add an INCLUDE statement for columns that can be returned but do not participate in the seek operation.

    You should have a look at Brent Ozar's implementation of the same thing, but it generates the T-SQL based on the a similar query to yours.

  • Don't create missing indexes off the missing index DMV unless you've tested and ensured they really are 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

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

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