I've been looking at the following query: SELECT 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 FROM 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 WHERE 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. Thanks
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.
I'll look into seeing if it can become part of a covering index, that sounds like the better plan. Best Regards