Finding Missing Index

The first thing I usually do when optimizing queries is to check if there is at least one index on all tables referred in the query. However, having the right index is important as well. I found this query on the net that finds what index is missing on most frequently used queries. I would thank them but I can't remember

SELECT  
Avg_Estimated_Impact = dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans),
TableName = OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id),
Create_Statement = 'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
   + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
   CASE
      WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
      ELSE ''
   END
   + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
   + ']'
   + ' ON ' + dm_mid.statement
   + ' (' + ISNULL (dm_mid.equality_columns,'')
   + CASE 
         WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' 
         ELSE ''
      END
   + ISNULL (dm_mid.inequality_columns, '')
   + ')'
   + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '')
FROM sys.dm_db_missing_index_groups dm_mig
   INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle
   INNER JOIN sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY last_user_seek DESC

Here's the result set:

Avg_Estimated_Impact        TableName    Create_Statement
-------------------------   ------------ ------------------------------------------------
2052.6                      MyTable      CREATE INDEX [IX_MyTable_Col1_Col2] ON [DBName].[dbo].[MyTable] ([Col1],[Col2]) INCLUDE ([Col3], [Col4])

2066.24                     MyOtherTable CREATE INDEX [IX_MyOtherTable_Col3_Col5] ON [DBName].[dbo].[MyOtherTable] ([Col3],[Col5]) INCLUDE ([Col4])

Comment?


~~ CK

List tables, index and keys

Here's a code that list the columns used in each index for each table.

with table_indexes
as
(
   select 
   row_num = row_number() over(partition by t.name, i.name order by t.name, i.name, ci.key_ordinal),
   t.name as table_name, i.name as table_index, c.name as index_keys
   from sys.tables t
      left join sys.indexes i on t.object_id = i.object_id and i.index_id > 0
      left join sys.index_columns ci on i.object_id = ci.object_id and i.index_id = ci.index_id
      left join sys.columns c on c.object_id = ci.object_id and c.column_id = ci.column_id
)  
select 
   table_name = case when row_num = 1 then table_name else '' end,
   table_index = case when row_num = 1 then table_index else '' end,
   index_keys
from table_indexes

As always, comments, suggestions, objections :) are all welcome...


~~ CK