Hi Steve. Over recent months I've been adding indexes missing on foreign keys as well as searching for missing indexes where I see table scans occurring. Since there are quite a few, I started by limiting my search to tables with over 10 million records.
For your query I searched for cases where IsParentIndexedForFK is NO or IsReferenceBoundToGoodIndex is NO which came up with 260 records. I'm leaning towards filtering this down to larger tables for starters,but I'm not sure whether to apply the filter to the parent tables or the referenced tables.
QA requires me to apply new indexes very gradually in their environments and then push to prod weeks later, so I want to grab the biggest bang for the buck first.
WHERE (CASE WHEN EXISTS (SELECT * FROM ParentIndexGood
WHERE ParentIndexGood.constraint_object_id = k.object_id) THEN
'Yes' ELSE 'No' END='no' OR CASE
WHEN EXISTS (SELECT * FROM ReferencedBoundIndexGood WHERE ReferencedBoundIndexGood.constraint_object_id = k.object_id)
THEN 'Yes' ELSE 'No' END ='no')