Thursday, May 10, 2018

DMV To List Foreign Keys With No Index

I was entrusted with a new database for a critical application that was having general performance issue and the CPU usage constantly over 70%.

It turned out that I did not have to sweat much to quickly and significantly improve the database performance and also bring down CPU % way below the threshold.

The solution? For one thing, the database was missing many much needed indexes.  After making sure that it was the right thing to do, I added the indexes that would give the biggest performance boost. It instantly and greatly improved the performance. I was the hero!

Secondly, the foreign key columns were not indexed and because of their relative small size, they would not easily be captured by the the famous sys.dm_db_missing_index_details DMV.

Lack of indexes in foreign keys was not only causing minutely noticeable  performance impact but also leading to lock escalations that during heavy loads cause dead locks and exacerbating the situation.

So I wrote the below query to get list of all foreign keys in the database with no index on them. Among other things,   it also lets me know if the CASCADE DELETE/UPDATES options are being used as those could be more cause of concern for performance.

WITH    fk_cte
          AS ( SELECT   OBJECT_NAME(fk.referenced_object_id) pk_table ,
               pk_column ,
               pk_index_name ,
                        OBJECT_NAME(fk.parent_object_id) fk_table ,
               fk_column ,
               fk_name ,
                        CASE WHEN i.object_id IS NOT NULL THEN 1 ELSE 0 END does_fk_has_index ,
                        i.is_primary_key is_fk_a_pk_also ,
                        i.is_unique is_index_on_fk_unique ,
               FROM     sys.foreign_keys fk
                        INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
                        INNER JOIN sys.columns c ON c.object_id = fk.parent_object_id AND c.column_id = fkc.parent_column_id
                        LEFT  JOIN sys.columns c2 ON c2.object_id = fk.referenced_object_id AND c2.column_id = fkc.referenced_column_id
                        LEFT JOIN sys.key_constraints kc ON kc.parent_object_id = fk.referenced_object_id AND kc.type = 'PK'
                        LEFT JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                        LEFT JOIN sys.indexes i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    SELECT  * FROM    fk_cte
       LEFT JOIN sys.dm_db_partition_stats ps on ps.object_id = fk_cte.parent_object_id and ps.index_id <= 1
    WHERE   does_fk_has_index = 0 -- and fk_table = 'LineItems'
    ORDER BY used_page_count desc

Based on one of the questions in the comments section, I thought maybe it would be helpful to add an actual example that is reproducible with minimum efforts for all of us.

EXAMPLE 1 - How having index on a FK field can speed up deletes in PK table

In the AdventureWorks2014 sample database sales.SalesOrderDetail table references  Sales.SpecialOfferProduct (SpecialOfferID, ProductID). But currently there is no index on the FK fields.

The following DELETE produces estimated execution plan with a suggestion to create a missing index on the FK table sales.SalesDetails 

USE AdventureWorks2014

DELETE sales.SpecialOfferProduct WHERE ProductID = 764


I realize that the impact/improvement % is not that great on its face value (and that's also the reasons why the missing indexes DMV can easily miss it) but on a busy system and depending on the application design, it can produce very meaningful improvements.

So here I oblige to the recommendation and add index to the FK fields

USE [AdventureWorks2014]

CREATE NONCLUSTERED INDEX idx_FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID
ON [Sales].[SalesOrderDetail] ([ProductID],[SpecialOfferID])

And here is the execution plan for the same DELETE statement after adding the index.

And here are the statistics IO numbers

-- before index
Table 'SalesOrderDetail'. Scan count 1, logical reads 1246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SpecialOfferProduct'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

-- after index
Table 'SalesOrderDetail'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SpecialOfferProduct'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)


  1. Thank you for this. It's very helpful. Since I have table names that are the same with different schema names, I added:
    OBJECT_SCHEMA_NAME(fk.referenced_object_id) pk_table_schema
    OBJECT_SCHEMA_NAME(fk.parent_object_id) fk_table_schema,

  2. Does it make sense to put an index on a foreign key column which is 100% not unique?

    1. Hi Ciayko,

      That really depends on several factors.

      If there are deletes happening in PK table, then I would say its a good idea to add the index on the FK field, even if the FK field is not 100% unique.

      If the FK field is used in the WHERE clause and/or JOIN conditions, that would be another good use case.

      Highly normalized databases would also benefit more from having indexes on FK fields.


  3. Thank you, I find this interesting and looking at the results brings me additional questions.

    I have many table that have FK references to other tables.
    I get having an index on the data that you will be using in a where clause, but to just put indexes on the FK fields I don't see the benefit.

    In my tables the fk tables I would consider mostly look up data and when I pull data it will bring in the reference data by key for the rows I am interested in, but not searching or filtering by that data.

    I am just wondering if there is still a benefit that I am not seeing to have indexes that way.


    1. Hi Art,

      Especially when records are deleted from PK table is when having FK field indexed can be very helpful. If a record is being deleted from PK table and there are records in the FK table that references that value, the delete will error out and transaction rolled back. How does SQL Server knows this? It runs implied query against the FK table and if the FK field is not indexed there, it will have to do a scan and take on shared/intent/read locks at higher level that can lead to blocking.

      When inserting a new record into the FK table, SQL checks the PK to make sure the parent record already exists. PKs are always indexed so SQL server takes advantage of that.

      If your database doesn't do much deletes and updates then it may not worth it. if it ain' broke, why fix it!

      The DMV is informational and you might be surprised by what tables show up in the results!

      For an existing database I would not do nor recommend blindly adding indexes based on the output of this query.

      Now if I am designing a new database for a brand new application, I usually do index all foreign keys. In fact, nowadays in some data modelling tools its the default setting.

  4. Drupal,

    Thank you for the comments.

    My concern here is the balance between too many indexes and inserts. In my case, the tables that I have the most FK relationships in, are the heaviest insert tables.

    In my world it is rare to have to delete this data based on any of those values, if we are doing that it is normally because of some error and all bets are off.

    And as you said, it is not a good idea to blindly add indexes, but this does give me a new perspective to consider.

    Thank you,