By: Mike Eastland | Updated: 2016-05-26 | Comments (1) | Related: More > Indexing
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?
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.
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.
- 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
About the author
View all my tips