Missing index details

  • SELECT

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

    mid.statement as fullyqualifiednames,

    '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

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

    this query gives me around 490 indexes, i think i cant create blindly all the indexes, please advice some parameter on what basis i can choose most required indexes only.

    and there must be some indexes already created, i dont want to create duplicate one. any idea how can filter already created indexes in this script.

  • Biggest piece of advice, don't do this. You really need to look at each index and decide if it needs to be created or not. The missing index DMV can also provide you with multiple indexes that are virtually the same, but with some investigation can be combined or possibly eliminated by a slight modification to an existing index.

    Do not blindly add indexes just because the DMV or DTA say you need them. Really check them out and test each possible change.

  • but we can not test as its production environment, there's a long process to create or drop any index on server. so by having an idea only i have to suggest about which index need to create in the server. so please just give me slight idea about an important parameter in this DMV.

  • I would not automate this. Each index suggested needs to looked at with a critical eye, not just at the individual index but at al the other indexes suggested on the same table and already on the table.

    I have gone through this process myself, and yes it is time consuming but in the end you will have a better set of recommendations to provide regarding what indexes should exist on each table.

  • ok then please give me an idea regarding what to see when keeping an critical eye on each index, what is the best part to look into it.

  • I would start by putting the indexes into a spread sheet grouped by tables. Start by comparing the order of the equality columns (your WHERE or ON clauses would be using Column = value for these), then look at the inequality columns (column >= value or such in a WHERE clause), then look at the included columns.

    I have had the missing index DMV and DTA suggest adding an index with 40+ included columns from a table with around 50 columns.

    This is just a quick jump start. Even after you narrow down the indexes, you really need to test them in a test or development environment before putting them in production.

Viewing 6 posts - 1 through 5 (of 5 total)

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