Maybe because they often bring more harm than good?

A very common (not the only!) scenario for a foreign key constraint is a large table with data that has foreign keys into several other, smaller ("lookup") tables. Let's look at a few scenarios.

1. A query that joins the tables - in most cases, the large table will "drive" the query. Ideally through a seek that uses a filter to limit the number of rows read from the large table. The data from the smaller data will then be added by a seek on the primary key of that table. The index on the large table will not be used in this case.

You may also see a plan that starts to read the smaller table and then joins to the large table. If that plan uses a lookup, an index on the foreign key column will definitely be beneficial for a loop join or merge join; less so for a hash join.

2. Modification in the large table are often very frequent in this scenario. For inserts and updates, the existence of the value in the small table has to be checked; this uses the index on the smaller table's primary key. The index on the foreign key column will not be used, but has to be updated for every insert, update, and delete.

3. Modifications to the small table are far less frequent. For inserts and updates that don't affect the primary key, no check against the large table is required. For deletes, the alrge table has to be checked to verify that the value is not used. This check will benefit from an index on the foreign key.

So - for querying, the most usual query pattern may in the majority of cases not use the foreign key index at all. For modifications, the most common modification will be slowed down by the index on the foreign key column, and only a less frequent modification will benefit.

Please don't take the above to mean that I think you should never have an index on a foreign key column. In many cases, you should. But not in all. And that's why I think it's a good thing that SQL Server does not add them automatically.