Creating index on multiple columns separately or should be avoided to gain performance for add/update and select cases

  • I used following query to identify missing indexes:

    SELECT mid.statement , mid.included_columns, mid.equality_columns, mid.inequality_columns,

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

    'CREATE INDEX [NCIX_' + 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 + ')', '') + ' GO' 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

    and mid.database_id = 10

    order by mid.statement

    its showing lost of indexes to be created on same table with similar columns.

    CREATE INDEX [NCIX_21974_21973_TL_SRV_Role_Menu] ON [TL_SRV_Role_Menu] ([RoleId], [MenuId], [isActive]) GO

    CREATE INDEX [NCIX_21960_21959_TL_SRV_Role_Menu] ON [TL_SRV_Role_Menu] ([RoleId], [isActive]) INCLUDE ([MenuId]) GO

    CREATE INDEX [NCIX_21962_21961_TL_SRV_Role_Menu] ON [TL_SRV_Role_Menu] ([isActive]) INCLUDE ([RoleId], [MenuId]) GO

    I think I need to only create few if an index is covering all columns then I do not need to create more indexes for separate columns or should I create separate index as suggested?

    Similarly, there is one another suggestion with following case:

    CREATE INDEX [NCIX_20187_20186_TL_SRV_Stationary_Stock_Transact] ON [TL_SRV_Stationary_Stock_Transaction] ([SerialNo],[StationaryStatus]) GO

    CREATE INDEX [NCIX_20189_20188_TL_SRV_Stationary_Stock_Transact] ON [TL_SRV_Stationary_Stock_Transaction] ([StationaryStatus]) INCLUDE ([SerialNo]) GO

    CREATE INDEX [NCIX_14943_14942_TL_SRV_Stationary_Stock_Transact] ON [TL_SRV_Stationary_Stock_Transaction] ([StationaryStatus]) INCLUDE ([StationaryStockDetailId], [SerialNo]) GO

    CREATE INDEX [NCIX_14946_14945_TL_SRV_Stationary_Stock_Transact] ON [TL_SRV_Stationary_Stock_Transaction] ([StationaryStockDetailId], [StationaryStatus]) INCLUDE ([SerialNo]) GO

    What would be best suggestion, should I create all indexes above or use minimum number of indexes which covers all columns as mentioned in above create index statements?

    Shamshad Ali

  • The missing index DMV is a place to start looking at indexes, not the final answer. You need to take the recommendations, evaluate them, test each one and see what effect it has, and then only implement the indexes which 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
  • And remember, the order of the keys on the indexes matters quite a bit, especially for the first column since that determines what is in the histogram of the statistics. This means in a case where you have ID1 & ID2 in one index, ID2 & ID1 in a different index may work better for some queries, differently than the first index. As Gail says, testing is the only way to be sure.

    "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 3 posts - 1 through 2 (of 2 total)

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