In case someone is still wondering about this. Adding index to every foreign key is considered a bad practice.
- Don’t index every column of the table.
- Don’t create more than 7 indexes per table (clustered and non-clustered)
- Don’t leave a table as Heap (create a clustered index).
- Don’t create an index on every column involved in every foreign key
- Don’t rebuild an index too frequently (monthly once is good enough)
- Don’t create an index with more than 5 to 7 key columns
- Don’t create an index with more than 5 to 7 included columns
- Don’t add your clustered index key in your non-clustered index
- Don’t change server fill factor, change it at an index level (as needed)
- Don’t ignore index maintenance
https://blog.sqlauthority.com/2020/02/13/sql-server-poor-indexing-strategies-10-donts-for-indexes/
Having too many nonclustered indexes can cause numerous problems. First, unneeded indexes take up space. This impacts storage costs, backup and recovery times, and index maintenance times. Indexes must be kept up to date whenever data changes. The performance of inserts, updates, and deletes is impacted by nonclustered indexes. Have you ever heard of a SELECT query that runs more slowly because there are too many indexes on a table? I have seen it happen. When the optimizer comes up with a plan for a query, it must consider the available indexes, three types of joining, order of joins, etc. The number of plan choices increases exponentially. The optimizer won’t take long to come up with a plan, however, and will sometimes stop with a “good enough plan”. It’s possible that the optimizer didn’t have enough time to figure out the best index because there were too many to consider.
SQL Server Indexes are not always as much as helpful as we like to think. Often they work against our overall server performance. In this session, we will see some troublemaking scenarios and their workarounds. Slow Running Queries are the most common problem that developers face while working with SQL Server. While it is easy to blame SQL Server for unsatisfactory performance, the issue often persists with the way queries have been written, and how Indexes has been set up. The session will focus on the ways of identifying problems that slow down SQL Server, and Indexing tricks to fix them. We will see a few different scenarios where indexes will actually negatively affect the performance of queries. By removing some of the indexes, we can easily improve the performance of overall system. This is a very unique session and will explore the dark side of indexes and its resolutions. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately.
https://www.youtube.com/watch?v=pdS7UZ-mAnA
This issue is inbaked in every relational database. It's just inside the fundamentals how they work. Choose your battles, you cannot make every query fast.
Starting from EF Core 7 here's how you remove this feature.
protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
configurationBuilder.Conventions.Remove(typeof(ForeignKeyIndexConvention));
}
Or by using generics
protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
configurationBuilder.Conventions.Remove<ForeignKeyIndexConvention>();
}