Suponha que eu tenha 2 tabelas, produtos e categorias de produtos. Ambas as tabelas têm relação no CategoryId. E esta é a consulta.

SELECT p.ProductId, p.Name, c.CategoryId, c.Name AS Category
FROM Products p
INNER JOIN ProductCategories c ON p.CategoryId = c.CategoryId
WHERE c.CategoryId = 1;

Quando crio o plano de execução, a tabela ProductCategories executa a busca de índice de cluster, que é a expectativa. Mas para a tabela Products, ele executa a verificação de índice de cluster, o que me faz duvidar. Por que o FK não ajuda a melhorar o desempenho da consulta?

Então, eu tenho que criar um índice em Products.CategoryId. Quando crio o plano de execução novamente, as duas tabelas executam a busca de índice. E o custo estimado da subárvore é bastante reduzido.

Minhas perguntas são:

Ao lado de FK ajuda na restrição de relacionamento, ela tem outra utilidade? Melhora o desempenho da consulta?

Devo criar um índice em todas as colunas do FK (como Products.CategoryId) em todas as tabelas?