Supongo que tengo 2 tablas, Productos y Categorías de productos. Ambas tablas tienen relación en CategoryId. Y esta es la 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;

Cuando creo un plan de ejecución, la tabla ProductCategories realiza la búsqueda de índice de clúster, lo cual es una expectativa. Pero para Table Products, realiza una exploración de índice de clúster, lo que me hace dudar. ¿Por qué FK no ayuda a mejorar el rendimiento de las consultas?

Así que tengo que crear un índice en Products.CategoryId. Cuando creo nuevamente el plan de ejecución, ambas tablas realizan la búsqueda de índice. Y el costo estimado del subárbol se reduce mucho.

Mis preguntas son:

Beside FK ayuda en la restricción de la relación, ¿tiene alguna otra utilidad? ¿Mejora el rendimiento de la consulta?

¿Debo crear un índice en todas las columnas FK (me gustó Products.CategoryId) en todas las tablas?