select
  'CREATE NONCLUSTERED INDEX [IX_' + table_name + '_' + name +'] ON [dbo].[' + table_name + '] ([' + name + '] ASC)',
  *
from
     (
     select
       object_name(object_ID) Table_Name,
       object_name(Referenced_object_ID) Referenced_Table_Name,
       (SELECT max(si.row_count) FROM sys.dm_db_partition_stats si 
        WHERE si.object_ID = T.object_ID and si.index_id < 2) Table_Count,
       (SELECT max(si.row_count) FROM sys.dm_db_partition_stats  si 
        WHERE si.object_ID = Referenced_object_ID and si.index_id < 2) Referenced_Table_Count,
       *
     from
           (
           select
             (select max(referenced_object_ID) from sys.foreign_key_columns FKC
                 where FKC.parent_object_ID = C.object_ID and 
                     FKC.Parent_Column_ID = C.Column_ID) referenced_Object_ID,
             case 
                when exists
                  (select 1 from sys.index_columns IC
                   where C.Object_ID = IC.Object_ID and
                     C.Column_ID = IC.column_ID and Index_Column_ID = 1) then 1
                else 0
                end Indexed,
             *
           from
             sys.columns c
           where
             exists
                (select 1 from sys.foreign_key_columns FKC
                 where FKC.parent_object_ID = C.object_ID and 
                     FKC.Parent_Column_ID = C.Column_ID)
           ) T
     ) T
where
  Indexed = 0 and Table_Count > 1000 and Referenced_Table_Count > 1000
order by
  Table_Count * power(Referenced_Table_Count, 0.7) desc

quote:

---

Originally posted by tkizer

I use this a lot: http://sqlserverpedia.com/wiki/Find_Missing_Indexes

You have to be very careful with the output though. Only consider those with an impact over 1000000. Make sure you don't add "duplicate" indexes, and make sure that SQL Server has been up for a while when running the report since the DMVs get cleared after a restart.

Tara Kizer

---

quote:

---

Originally posted by tkizer

I agree it has disadvantages, especially the variations of the same index (that's why I mentioned the "duplicate" issue in my last post). It really takes a good DBA to decide which indexes to actually add based upon the DMV output.

---