Sql-server – How to one identify if FK’s needed to be indexed apart from the NC indexes already residing

indexperformancequery-performancesql server

I was going through many articles, which said indexing the FK's is beneficial for good performance, specially for the environment where most of the reporting is done.

I have a similar env where there lots of reads for this one particular database. As a good process, I am trying to find the Foreign Keys which can be indexed , even though NC' indexes are there for those tables.

So, is there a way I can identify those FK's which needs to be indexed, making sure it does not overlap with NC indexes, already existing on those tables for that particular database.

The database in question has quite a number of FK's and queries with lots of joins, comprising a good no of tables. This DB is 95% involved in read and activity and once in a month a file is uploaded which updates the tables.

+Info- We are using SQL server 2012.

Please suggest for any such script or a better way to approach this, thanks!

Best Answer

There are benefits to indexing foreign keys as they provide better join performance as described by SQLSkill's team here and here.

It is generally recommended to create an index which leads on the foreign key column(s), to support not only joins between the primary and foreign keys, but also updates and deletes.

A script can be found here or Indexes Supporting Foreign Keys.

Review any index recommendation that the scripts give and make sure you dont end up created a lot of indexes since there is always a cost of maintaining them.

Also, make sure that your FK's are trusted.

Related Question