I saw this, DMV To List Foreign Keys With No Index, via Brent Ozar’s weekly links email.
Unindexed foreign key columns might not be captured by the sys.dm_db_missing_index_details DMV because of their relatively small size. Lack of indexes on foreign keys might only have a small performance impact during reads but can lead to lock escalations during heavy write loads causing excessive blocking and possibly dead locks.
I’ve updated the original posted query to generate TSQL to create the missing indexes (which you should compare to the existing index landscape to see if any indexes can be consolidated before running in).
[Note: if you are unfortunate enough to have spaces in your table/column names, then you’ll need to replace them with an underscore ‘_’ (or other character) in the index name.]
;with cte_fk as
(
select
fk_table_schema = OBJECT_SCHEMA_NAME(fk.parent_object_id),
fk_table = OBJECT_NAME(fk.parent_object_id),
fk_column = c.name,
fk_name = fk.name,
fk_has_index = CASE WHEN i.object_id IS NOT NULL THEN 1 ELSE 0 END,
is_fk_a_pk_also = i.is_primary_key,
is_index_on_fk_unique = i.is_unique,
index_def = 'create index NC_' + OBJECT_NAME(fk.parent_object_id) + '_' + c.name +
' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + '(' + QUOTENAME(c.name) + ')',
pk_table_schema = OBJECT_SCHEMA_NAME(fk.referenced_object_id),
pk_table = OBJECT_NAME(fk.referenced_object_id),
pk_column = c2.name,
pk_index_name = kc.name,
fk.*
from
sys.foreign_keys fk
join sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
join sys.columns c ON c.object_id = fk.parent_object_id AND c.column_id = fkc.parent_column_id
left join sys.columns c2 ON c2.object_id = fk.referenced_object_id AND c2.column_id = fkc.referenced_column_id
left join sys.key_constraints kc ON kc.parent_object_id = fk.referenced_object_id AND kc.type = 'PK'
left join sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
left join sys.indexes i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
)
select
*
from
cte_fk c
left join sys.dm_db_partition_stats ps on ps.object_id = c.parent_object_id and ps.index_id <= 1
where
fk_has_index = 0
-- and fk_table = 'mytablename'
order by
used_page_count desc