SQL Server Performance Tuning Tip - Index Foreign Key Columns


By:   |   Updated: 2016-05-26   |   Comments (1)   |   Related: More > Indexing

Problem

I received a complaint of a slow-running DELETE statement on one of my production SQL Server instances.  An examination of the execution plan showed that a table scan was being performed on a completely separate table.  Further research revealed that the separate table contained a foreign key constraint referencing the table against which the DELETE statement was being executed.  This foreign key was created to take NO ACTION on DELETE.  Why is the table scan being performed on the child table, and what should I do to prevent this?

Solution

This tip shows the importance of creating nonclustered indexes on the foreign key columns in child tables.  The code listed below is for demo purposes only and should not be executed against a production instance.

Setup SQL Server Test Tables for Foreign Key Example

Listing 1 creates the two tables that will be used in the rest of the demo.

-- Listing 1
      
SET NOCOUNT ON
      
-- Drop test tables if they already exist
IF OBJECT_ID('dbo.child', 'U') IS NOT NULL
  DROP TABLE dbo.child;
      
IF OBJECT_ID('dbo.parent', 'U') IS NOT NULL
 DROP TABLE dbo.parent;
GO
      
-- Create test tables
CREATE TABLE dbo.parent (
 parent_id INT IDENTITY(1,1) NOT FOR REPLICATION
NOT NULL CONSTRAINT PK_Parent PRIMARY KEY,
   parent_data UNIQUEIDENTIFIER NOT NULL
CONSTRAINT DF_Parent DEFAULT (NEWID())
);
      
CREATE TABLE dbo.child (
  child_id BIGINT IDENTITY(1,1) NOT FOR
REPLICATION NOT NULL CONSTRAINT PK_Child PRIMARY KEY,
   parent_id INT NOT NULL,
   child_data UNIQUEIDENTIFIER NOT NULL CONSTRAINT
DF_Child DEFAULT (NEWID())
);
GO

Generate Test Data for SQL Server Tables

The code in Listing 2 populates the test tables with sample data.

-- Listing 2



-- Populate parent table
DECLARE @p  INT = 256;
      
WHILE @p > 0
BEGIN
 INSERT INTO dbo.parent (parent_data) VALUES
 (NEWID());
    
 SET @p -= 1;
END
      
GO
      
-- Populate child table
DECLARE @c INT = 262144,  @p_id INT;
      
WHILE @c > 0
BEGIN
 -- Get random, existing parent_id values
  SELECT TOP 1 @p_id = parent_id 
  FROM dbo.parent
  ORDER BY NEWID();
      
  INSERT INTO dbo.child (parent_id, child_data)
  VALUES (@p_id, NEWID());
      
  SET @c -= 1;
END      
GO

Create SQL Server Foreign Keys

Listing 3 creates a foreign key on the child table that references the parent table.  It will not CASCADE for DELETEs or UPDATEs.

 -- Listing 3
      
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [name] =
'FK_child_parent' AND [type] = 'F' AND parent_object_id =
OBJECT_ID('dbo.child'))



ALTER TABLE dbo.child WITH NOCHECK ADD
 CONSTRAINT FK_child_parent FOREIGN KEY (parent_id) REFERENCES
 dbo.parent (parent_id)
       ON DELETE NO ACTION 
       ON UPDATE NO ACTION;
      
ALTER TABLE dbo.child CHECK CONSTRAINT FK_child_parent;
GO

Missing SQL Server Indexes on Foreign Keys

The script in Listing 4 lists any foreign keys in the current database that are not indexed.  The code was adapted from this blog post.

-- Listing 4
      
SELECT o.name [table], s.name [schema], fk.name [foreign_key_no_index]
FROM sys.foreign_keys fk
INNER JOIN sys.objects o 
 ON o.[object_id] = fk.parent_object_id
INNER JOIN sys.schemas s 
 ON s.[schema_id] = o.[schema_id]
WHERE o.is_ms_shipped = 0
AND NOT EXISTS ( SELECT *
         FROM sys.index_columns ic
         WHERE EXISTS ( SELECT *
    FROM sys.foreign_key_columns fkc
           WHERE fkc.constraint_object_id = fk.[object_id]
    AND fkc.parent_object_id = ic.[object_id]
           AND fkc.parent_column_id = ic.column_id )
         GROUP BY ic.index_id
         HAVING COUNT(*) = MAX(index_column_id)
         AND COUNT(*) = ( SELECT COUNT(*)
    FROM sys.foreign_key_columns fkc
           WHERE fkc.constraint_object_id = fk.[object_id] ) )
ORDER BY o.[name], fk.[name];

In this test database, the only foreign key currently missing an index is the one created in Listing 3.

fk_no_index

Choose Parent Values for Later Use

Listing 5 contains a simple SELECT statement to show parent_id values that can be used in Listings 6 and 8 below.

-- Listing 5
      
SELECT p.parent_id, COUNT(c.child_id) [child_count]
FROM dbo.parent p
INNER JOIN dbo.child c ON c.parent_id = p.parent_id
GROUP BY p.parent_id
ORDER BY [child_count] DESC;

The result set will be different due to how the tables are populated by the code in Listing 2.  I prefer to choose two values with the same child_count value for the sake of consistency.  In this example, parent_id values of 76 and 187 will be used.

get_parent_ids

Example SQL Server DELETE with No Indexes on Foreign Keys

The code in Listing 6 will perform simple delete statements against both the parent and child tables.  Since the foreign key is not configured to CASCADE changes, the child records are deleted first.  The code uses STATISTICS IO to output the IO actions performed by the DELETE query against the parent table.  Review this tip for more information on STATISTICS IO.  It is also recommended to include the actual execution plan within SQL Server Management Studio.  For more information on execution plans, check out these tips.

-- Listing 6
      
DECLARE @p1_id INT = 76;
-- Replace this value with one returned by the script in Listing 5
      
BEGIN TRAN
      
SET NOCOUNT OFF
      
DELETE FROM dbo.child WHERE parent_id = @p1_id;
      
SET STATISTICS IO ON
      
DELETE FROM dbo.parent WHERE parent_id = @p1_id;
      
SET STATISTICS IO OFF
SET NOCOUNT ON
      
IF @@TRANCOUNT > 0
 COMMIT TRAN
GO

The resulting execution plan shows a clustered index scan, which is the equivalent of a table scan, on the child table for the delete statement against the parent table.

no_index_plan

In addition, the output from STATISTICS IO for the DELETE against the parent table shows a high number logical reads against the child table.

no_index_statistics_io

Index the SQL Server Foreign Key Column

Listing 7 creates a nonclustered index on the foreign key column in the child table.

-- Listing 7
      
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE [name] =
'IX_child_parent_id' AND [object_id] = OBJECT_ID('dbo.child'))
CREATE NONCLUSTERED INDEX [IX_child_parent_id]
ON dbo.child (parent_id);
GO

DELETE with Indexed SQL Server Foreign Key Column

The code in Listing 8 executes the same delete statements described in Listing 6.  Only the parent_id value has been changed.

-- Listing 8
      
DECLARE @p2_id INT = 187;
-- Replace this value with one returned by the script in Listing 5
      
BEGIN TRAN
      
SET NOCOUNT OFF
      
DELETE FROM dbo.child WHERE parent_id = @p2_id;
      
SET STATISTICS IO ON
      
DELETE FROM dbo.parent WHERE parent_id = @p2_id;
      
SET STATISTICS IO OFF
SET NOCOUNT ON
      
IF @@TRANCOUNT > 0
 COMMIT TRAN
      
GO

The execution plan now shows an index seek on the child table for the delete statement against the parent table.

index_plan

The output from STATISTICS IO for the DELETE against the parent table with the index in place shows a significant reduction (1210 to 6) in logical reads against the child table with the new index in place.

index_statistics_io

Conclusion

Even when a foreign key is not configured to CASCADE, SQL Server must still check the child table(s) to ensure referential integrity is maintained. This tip contains a simple example illustrating the importance of indexing the foreign key columns on child tables. In our example the number of logical reads was more than 1200 for the non indexed foreign key on the child tables as compared to 6 logical reads for the indexed foreign key on the child table.  The example was created using SQL Server 2014 SP1.

Next Steps
  • Review your databases for foreign key columns that are not currently indexed using the script in Listing 4.
  • Determine the performance improvements that are possible with indexing your foreign keys.
  • Check out other tips related to SQL Server indexes.
  • Check out other tips related to SQL Server constraints.


Last Updated: 2016-05-26


get scripts

next tip button



About the author
MSSQLTips author Mike Eastland Mike Eastland has been a SQL Server DBA since 1999. His main areas of interest are monitoring and the automation of administrative tasks.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, March 02, 2017 - 1:10:28 PM - Jeff Moden Back To Top

Nice job, Mike.  This should be required reading and Microsoft should build Script #4 into SQL Server.  Me?  I'm going to add it to my collection of scripts that I deploy to the Master database on each of my servers.

Well done and thank you for taking the time to post this.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools