By: Mike Eastland | Updated: 2016-05-26 | Comments (1) | Related: > 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.
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.
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.
In addition, the output from STATISTICS IO for the DELETE against the parent table shows a high number logical reads against the child table.
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.
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.
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.
About the author
Mike Eastland has been a SQL Server DBA since 1999. His main areas of interest are monitoring and the automation of administrative tasks.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2016-05-26
