DMV Query To List Foreign Keys With No Index

Find Unindexed Foreign Keys Killing SQL Server Performance

Here's a classic DBA story, one so basic it almost feels embarrassing to share. A critical app database was spiking CPU over 70%, performance tanking. Turns out I didn't need heroics, just indexes.

I was entrusted with a new database for a critical application that was having general performance issues and 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 usage well 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.

Download SQL Query From GitHub

SET NOCOUNT ON;
-- USE [Database Name]
-- =====================================================================================
-- Query: Find Foreign Keys WITHOUT Indexes - Potneitial Performance Killers
-- Purpose: Identifies unindexed foreign key columns causing delete/update performance issues,
--          lock escalations, and deadlocks during referential integrity checks.
--          Without FK indexes, SQL Server scans ENTIRE child tables 
--          during parent deletes/updates. Logical reads drop from 1000s to single digits.
--
-- Key Benefits:
--  - Finds what sys.dm_db_missing_index_details misses (small FK columns)
--  - Prioritizes by table size (used_page_count DESC)
--  - Flags CASCADE DELETE/UPDATE risks
--
-- Compatibility: SQL Server 2008+ (sys.foreign_key_columns introduced 2008)
-- Scope: Current database only
-- =====================================================================================

WITH fk_cte AS (
    SELECT 
        -- PARENT Table (PK side)
        OBJECT_NAME(fk.referenced_object_id) AS pk_table,
        c2.name AS pk_column,
        kc.name AS pk_index_name,
        
        -- CHILD Table (FK side - NEEDS INDEX!)
        OBJECT_NAME(fk.parent_object_id) AS fk_table,
        c.name AS fk_column,
        fk.name AS fk_name,
        
        -- CRITICAL: Does FK column have an index?
        CASE WHEN i.object_id IS NOT NULL THEN 1 ELSE 0 END AS does_fk_has_index,
        
        i.is_primary_key AS is_fk_a_pk_also,
        i.is_unique AS is_index_on_fk_unique,
        
        fk.object_id, fk.parent_object_id, 
        fk.referenced_object_id,
        fk.delete_referential_action,      -- 1=CASCADE, 2=SET NULL, etc.
        fk.update_referential_action,
        fk.is_not_trusted, 
        fk.is_not_for_replication, 
        fk.is_disabled
        
    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
)
-- FINAL RESULT: ONLY unindexed FKs, prioritized by table size
SELECT * 
FROM fk_cte
    
    -- Add table size for impact prioritization (heap + clustered index)
    LEFT JOIN sys.dm_db_partition_stats ps 
        ON ps.object_id = fk_cte.parent_object_id 
        AND ps.index_id <= 1  -- Heap (0) + Clustered (1)
WHERE does_fk_has_index = 0  -- SHOW UNINDEXED FKs ONLY
  
ORDER BY used_page_count DESC, fk_table, fk_column;  -- BIGGEST TABLES FIRST

---

---

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

GO

BEGIN TRAN

DELETE sales.SpecialOfferProduct WHERE ProductID = 764

ROLLBACK

I realize that the impact/improvement percentage may not seem significant at face value (which is also why the missing indexes DMV can easily overlook it). However, on a busy system and depending on the application design, it can produce very meaningful improvements.

So, I decided to follow the recommendation and add indexes to the foreign key fields.

USE [AdventureWorks2014]

GO

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)

How To Audit Data Changes In SQL Server?

Scenario:

I was at a new client, with their previous and only DBA / DEVELOPER/ MASTER OF ALL of 8 years all suddenly gone with no documentation.

Their business critical application was having data integrity issues. Some data were updated incorrectly while some others were not updated at all, showing old or incorrect data to their users/customers/vendors etc...

After going through their stored procedures, triggers, view, ETLs, SQL trace etc... I was able to track down and fix most of the data issues.

But there was one particular table that I could not figure out.  I thought maybe that table is never updated? How can I be sure what is going on with it?

I decided I need to setup something to automatically monitor this table for few days.  Here, SQL server offers few options:

• Profiler trace
• Server side trace
• Extended events
• Audits
• Triggers
• CDC/CTC
• Service brokers
• ..........

I knew that I only need to know how this particular table is being updated. I don't need to know the actual data per column that is being updated, which is where triggers, CDC, service brokers, trace etc... can come handy.

I decided that using the Audit feature would be the best option here for me. It would tell me exactly what I needed to know with as little overhead as possible, it was quick and easy to setup as well.

In the following sample script I am using it to audit data changes in a particular table. You could however use it to audit multiple tables and/or also audit actions against  Views, Stored procedures, Inline functions, Scalar functions, Table-valued functions, Aggregate functions, Sequences etc.

-- FIRST , WE NEED TO DEFINE THE AUDIT FILE WHERE THE LOG ENTRIES WILL BE STORED

-- TO CREATE SERVER AUDIT, YOU NEED TO BE IN THE MASTER DATABASE

USE [master]

GO

CREATE SERVER AUDIT [Audit-Data-Changes]

TO FILE

(      FILEPATH = N'E:\MSSQL\Audit\'

       ,MAXSIZE = 100 MB

       ,MAX_FILES = 5

       ,RESERVE_DISK_SPACE = OFF

)

WITH

(      QUEUE_DELAY = 1000

       ,ON_FAILURE = CONTINUE

)

ALTER SERVER AUDIT [Audit-Data-Changes] WITH (STATE = ON)

GO

-- NOW SWITCH TO THE DATABASE WHERE THE TABLE IS

-- HERE WE ARE GOING TO DEFINE WHICH OBJECT AND APPLICABLE ACTIONS WE WANT TO LOG

USE [DB_NAME]

GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-Data-Changes]

FOR SERVER AUDIT [Audit-Data-Changes]

ADD (DELETE ON OBJECT::[dbo].[Table1] BY [public]),

ADD (INSERT ON OBJECT::[dbo].[Table1] BY [public]),

ADD (UPDATE ON OBJECT::[dbo].[Table1] BY [public])

WITH (STATE = ON)

GO

-- After running the Audit for two days, I ran the following query and there was the information I needed.

-- READING THE LOG ENTRIES

DECLARE @audit_name NVARCHAR(1000) = 'Audit-Data-Changes';

DECLARE @audit_log_file NVARCHAR(4000)

SELECT @audit_log_file  = CONCAT(log_file_path, name,'*') FROM     sys.server_file_audits WHERE    name = @audit_name AND is_state_enabled = 1 ORDER BY audit_id DESC

                                        

SELECT  ac.name ,ac.class_desc ,alf.* FROM    sys.fn_get_audit_file(@audit_log_file, DEFAULT, DEFAULT) alf

        LEFT JOIN sys.dm_audit_actions ac ON ac.action_id = alf.action_id AND ac.action_in_log = 1

              ORDER BY event_time DESC;

GO