SQL Server Performance

New index

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by mkal, Oct 28, 2009.

  1. mkal New Member

    I've been looking at the following query:
    CONVERT (varchar, @runtime, 126) AS Runtime
    , CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure
    , mid.statement as 'Database.Schema.Table'
    , migs.Avg_Total_User_Cost
    , migs.Avg_User_Impact
    , migs.User_Seeks
    , migs.User_Scans
    , migs.Avg_Total_User_Cost, mid.Database_ID
    , mid.[Object_ID]
    , 'CREATE INDEX ix_mi_' + OBJECT_NAME(mid.OBJECT_ID, mid.database_id) + '_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + ' 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
    sys.dm_db_missing_index_groups AS mig INNER JOIN
    sys.dm_db_missing_index_group_stats AS migs ON migs.group_handle = mig.index_group_handle INNER JOIN
    sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
    CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
    -- AND mid.statement LIKE '%profileOptions_%'
    ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC--, mid.statement ASC
    The top suggestion looks something similiar to this:
    CREATE INDEX ix_mi_tableName_123_122 ON [dbName].[dbo].[tableName] (isShop]) INCLUDE ([id])
    Since I know that the column called isShop is a bit field would there be any benefit from creating this index (the id field is a clustered PK datatype int) I'm not all that familiar yet with the INCLUDE option when creating indexes but I do know that the data in the bit field is not unique enough to warrant an index.
  2. FrankKalis Moderator

    This is hard to tell from only the result of that query. It may be even more beneficial to have the IsShop column as an included column in some other index on that table to generate a covering index, but you should easily be able to test if there is an improvement in your test environment.
  3. mkal New Member

    I'll look into seeing if it can become part of a covering index, that sounds like the better plan.
    Best Regards

Share This Page