Yes, you should index every foreign key column, unless you have a very good reason not to.

To understand why, let’s take a look at the execution plans of following scenarios:

  • deleting records using FK column as the filter:

FK filter scenario

  • deleting from primary key table:

PK deletion scenario

In both scenarios, the worse performing plan (by far) is the one without an available index.

Furthermore, notice that in the second scenario SQL Server doesn’t give an index recommendation. Without taking a closer look at the execution plan, it will take you a while to figure out why a simple delete statement is taking forever to execute.

Another major reason why you should index foreign keys is that they are often used in JOIN and WHERE clauses. SQL Server might manage without an index, but most often it will be much faster with an available index. If you have many queries in your database wasting resources, soon enough you’ll start experiencing slowdowns and blocks.

Case against indexes

So, when would an index be a waste? If the column has low selectivity, a table scan might be more efficient than an index seek, but query engine can figure that one out by itself (if you give him the option).

A good reason not to have an index on a FK column is if a table has more Create/Update/Delete operations than Reads, but that’s rarely the case and you’d have to carefully plan such table anyways.

Another valid reason not to blanket the table with indexes is if you’re using a clustered columnstore index on the table (eg. data warehouse). Each new index will increase the size of the table by basically 100% of the base size, while performance gains might be negligible.

On final note, you can always monitor DMV sys.dm_db_index_usage_stats to decide which indexes are unnecessary or counter-productive, but you can’t monitor an index if it doesn’t exist.