Problem
My database has many foreign key constraints to enforce referential integrity. Many of them are composite indexes and often used as covered index to optimize application performance. We review indexes periodically, add or remove columns from existing indexes definition over time depending on application requirement. We have experienced a noticeable degradation in performance after a particular change deployment. The application is performing only a single record deletion or update at a time, but takes many seconds to complete. We do not use table triggers. I have checked server disk I/O, CPU and memory are optimal. What else should I look at to correct the issue? Check out this tip to learn more.
Solution
When you delete a record in a parent table involved in referential integrity, SQL needs to check that the foreign key constraints aren’t violated to avoid orphaning rows on the child table. When you change or drop index definition on child table used by the foreign key, you also possibly change SQL search method for the related rows in the foreign table.
The impact of this mismatch could cause a single record deletion or updates to take many seconds to complete. It is not a requirement for a child table containing a foreign key to have an index. So be wary that a child table containing foreign key constraint will allow changes to any of its index definition or even its index definition being dropped without warning. Be prudent to check if foreign key exists between parent-child tables to avoid the scenario of SQL having to perform extra load on DELETE and UPDATE operation.
In order to understand the solution statement, the steps below will guide you to reproduce and learn the SQL Server behavior.
Setting up the SQL Server database
I am running SQL 2012 SP1 using AdventureWorks2012 database.
Below are typical steps to create a foreign key constraint in SQL Server Management Studio:
1. Foreign key constraints do not work on a temporary tables. Instead of meddling with an existing table, let’s use a permanent table instead.
USE AdventureWorks2012
GO
-- drop the child table before parent due to foreign key constraint if table already exists
IF OBJECT_ID('tmpSalesDetail', 'U') IS NOT NULL DROP TABLE tmpSalesDetail
IF OBJECT_ID('tmpSalesHeader', 'U') IS NOT NULL DROP TABLE tmpSalesHeader
SELECT * INTO tmpSalesHeader FROM Sales.SalesOrderHeader
GO
SELECT * INTO tmpSalesDetail FROM sales.SalesOrderDetail
GO2. Define primary key for both tables. You cannot create foreign key constraint without a primary key or unique constraint defined on the parent table. Let’s opt to create clustered primary key in both tables and create the foreign key constraint on SalesOrderID in both table.
ALTER TABLE dbo.tmpSalesHeader ADD CONSTRAINT PK_tmpSalesHeader PRIMARY KEY CLUSTERED (SalesOrderID) GO ALTER TABLE dbo.tmpSalesDetail ADD CONSTRAINT PK_tmpSalesDetail PRIMARY KEY CLUSTERED (SalesOrderID, SalesOrderDetailID) GO ALTER TABLE dbo.tmpSalesDetail ADD CONSTRAINT FK_tmpSalesDetail_tmpSalesHeader FOREIGN KEY (SalesOrderID) REFERENCES dbo.tmpSalesHeader (SalesOrderID) ON UPDATE NO ACTION ON DELETE NO ACTION GO
3. For this test, let’s create a dummy row with SalesOrderID 99999 into the parent table SalesHeader. Because SELECT INTO propagates IDENTITY column property into the destination table, we will have to enable IDENTITY_INSERT in order to insert this dummy record successfully with this table. This is a perfectly valid scenario where you can have a parent record without a related record in the child table. The reason to create this record is to show the same SQL Server impact even without the CASCADE deletion option turned on for the foreign key constraint property.
SET IDENTITY_INSERT tmpSalesHeader ON GO INSERT INTO tmpSalesHeader (SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, Status, OnlineOrderFlag, SalesOrderNumber, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, TotalDue, Comment, rowguid, ModifiedDate) SELECT TOP 1 99999 AS SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, Status, OnlineOrderFlag, SalesOrderNumber, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, TotalDue, Comment, rowguid, ModifiedDate FROM tmpSalesHeader GO SET IDENTITY_INSERT tmpSalesHeader OFF GO
4. Turn On the Include Actual Execution Plan option in SSMS.
5. Let’s try to delete this dummy record and review the I/O statistics and the execution plan. I have wrapped a BEGIN TRAN and ROLLBACK TRAN in order to preserve and re-use this dummy record repeatedly.
SET STATISTICS IO ON BEGIN TRAN DELETE FROM tmpSalesHeader WHERE SalesOrderID = 99999 ROLLBACK TRAN SET STATISTICS IO OFF
6. Over time, someone decided that the PRIMARY KEY on tmpSalesDetail requires a change, and they somehow decided to change the index column ordering.
ALTER TABLE dbo.tmpSalesDetail DROP CONSTRAINT PK_tmpSalesDetail GO ALTER TABLE dbo.tmpSalesDetail ADD CONSTRAINT PK_tmpSalesDetail PRIMARY KEY CLUSTERED (ProductID, SalesOrderDetailID, SalesOrderID) GO
Command(s) completed successfully.
The script will execute successfully without indicating any warning or error.
OK, maybe you’ve noticed that tmpSalesDetail table is present in the execution plan when deleting a record from tmpSalesHeader. But the index seek on tmpSalesDetail did not impose much logical reads even though it costs 20% in the overall DELETE operation.
Table 'tmpSalesDetail'. 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 'tmpSalesHeader'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
7. Now, re-run the DELETE operation in Step 5 and review the I/O statistics output.
Table 'tmpSalesDetail'. Scan count 1, logical reads 1502, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'tmpSalesHeader'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
The Clustered Index Seek has now become Clustered Index Scan on the tmpSalesDetail table, essentially meaning a table scan on tmpSalesDetail against each record output from tmpSalesHeader. Note tmpSalesDetail node now costs 21% of the overall execution plan cost.
8. Let’s bump up the number of records in tmpSalesDetail by 32 times. Originally tmpSalesDetail table contains 121,317 rows. After executing the script below, tmpSalesDetails will contain 3,882,144 rows. Execute the query below in a new query window in SSMS to avoid displaying the execution plan. It takes about a minute or so to complete, so be patient.
USE AdventureWorks2012 GO SET STATISTICS IO OFF SET NOCOUNT ON SET IDENTITY_INSERT tmpSalesDetail ON DECLARE @i int = 1, @bigint BIGINT WHILE @i <= 5 BEGIN SELECT @bigint = MAX(SalesOrderDetailID) FROM tmpSalesDetail INSERT INTO tmpSalesDetail (SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid, ModifiedDate) SELECT SalesOrderID,SalesOrderDetailID + @bigint, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid, ModifiedDate FROM tmpSalesDetail SET @i += 1 END SET IDENTITY_INSERT tmpSalesDetail OFF
9. Let’s try to delete this dummy record and review the I/O statistics and the execution plan again.
Table 'tmpSalesDetail'. Scan count 1, logical reads 48426, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'tmpSalesHeader'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
The DELETE operation has increased from sub-second to an obvious 3 seconds on my laptop. But the costs for tmpSalesDetail barely budge, only increased by 1% albeit the rows in tmpSalesDetail have increased by 32 times. The bulk of execution plan costing in the DELETE operation is 78% on tmpSalesHeader which incurs only 3 logical reads.
10. Perform clean-up and drop the 2 permanent temp tables
DROP TABLE tmpSalesDetail GO DROP TABLE tmpSalesHeader GO
Example solution
As the number of records in tmpSalesDetail grow, so will the DELETE load operation against tmpSalesHeader. Reverting the index changes applied on the child table would fix the issue. Alternatively, another solution you can employ would be to create an appropriate index on the child table, which involve creating an index on single column SalesOrderID.
CREATE NONCLUSTERED INDEX NCI_tmpSalesDetail_SalesOrderID ON tmpSalesDetail (SalesOrderID)
Just looking at the costing within an execution plan might not always highlight the root of issue; in this case it might sway you to check the Clustered Index Delete operation instead since it costs 78%. I/O statistics is a very useful performance tuning technique. The abnormally high logical read counts would trigger an alarm for further checks.
I hope the article provided a different perspective in SQL performance tuning. Thank you for reading and I hope this tip has been interesting.
Next Steps
- As you make indexing changes in your SQL Server databases, be sure to understand the index impacts with foreign key constraints. It is just not a matter of having an index, but having the columns in the correct order.
- Check out these related tips:
