Surprisingly, SQL Server doesn’t automatically create indexes just because you have a foreign key. So at the end of a development project, I just used the script below to generate the SQL needed.
select 'create index indx_'+t.name +'_' + c.name + ' on ' + t.name +
'(' + c.name + ');'
from sys.foreign_key_columns as fk
inner join sys.tables as t
on fk.parent_object_id = t.object_id
inner join sys.columns as c
on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
where not exists (select 1
from sys.index_columns ic
where ic.object_id = t.object_id and ic.column_id = c.column_id)
order by 1This returns the commands that I can then run to create the indexes.
create index indx_ActivityLog_activityTypeId on ActivityLog(activityTypeId);
create index indx_ActivityLog_userId on ActivityLog(userId);
create index indx_Areas_managerId on Areas(managerId);
create index indx_Categories_areaId on Categories(areaId);
create index indx_CriteriaItems_criteriaColorId
on CriteriaItems(criteriaColorId);