How can one identify if FK’s needed to be indexed apart from the NC indexes already residing

Posted on

Question :

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!

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.

You can download and install the Microsoft SQL Server 2012 Performance Dashboard Reports from

On that dashboard, you will see a link for Missing Indexes. This report will break down details for a set of Proposed Indexes determined by the SQL Server Engine. You should right-click and Export the results so you can analyze them in detail – In many cases, you may have entries that are overlapping, where only one index would cover each case.

Also keep in mind that since these are internal statistics, this information is reset whenever SQL Server restarts.

Leave a Reply

Your email address will not be published. Required fields are marked *