here's the script I mentioned that blindly builds the SQL statements to create indexes on any foreign keys that do not have indexes on them yet;

any peer review would be appreciated.

--select * from sys.index_columns

WITH MyIndexes

AS (SELECT

DISTINCT

idxz.name AS IndexName,

idxz.object_id,

OBJECT_NAME(idxz.object_id) AS TableName,

ixcolnamez.name AS IndexColumnName

FROM

sys.indexes idxz

INNER JOIN sys.index_columns idxcolz

ON idxz.index_id = idxcolz.index_id

INNER JOIN sys.columns ixcolnamez

ON idxcolz.object_id = ixcolnamez.object_id

AND idxz.object_id = ixcolnamez.object_id

AND idxcolz.column_id = ixcolnamez.column_id

WHERE

index_column_id = 1 --because only the lead column will help for the join

)

SELECT

SCHEMA_NAME(chldz.schema_id) AS SchemaName,

objz.name AS FKName,

parenz.name AS ParentTable,

pcolz.name AS ParentColumn,

chldz.name AS ChildTable,

ccolz.name AS ChildColumn,

MyIndexes.*,

CASE

WHEN MyIndexes.object_id IS NULL

THEN 'CREATE INDEX [IX_FK_' + chldz.name + '_' + ccolz.name + '] ON ' + QUOTENAME(SCHEMA_NAME(chldz.schema_id)) + '.' + QUOTENAME(chldz.name) + '(' + QUOTENAME(ccolz.name) + ');'

ELSE ''

END AS PotentialIndex

FROM

sys.sysforeignkeys fkeyz

INNER JOIN sys.objects objz

ON fkeyz.constid = objz.object_id

INNER JOIN sys.objects parenz

ON fkeyz.rkeyid = parenz.object_id

INNER JOIN sys.columns pcolz

ON parenz.object_id = pcolz.object_id

AND fkeyz.rkey = pcolz.column_id

INNER JOIN sys.objects chldz

ON fkeyz.fkeyid = chldz.object_id

INNER JOIN sys.columns ccolz

ON chldz.object_id = ccolz.object_id

AND fkeyz.fkey = ccolz.column_id

LEFT OUTER JOIN MyIndexes

ON chldz.object_id = MyIndexes.object_id

AND ccolz.name = MyIndexes.IndexColumnName