ReplTip – Foreign Key Check Constraints
I recently worked with a customer seeing very slow transfer rate with Distribution Agent. Looking at RPC:Completed events we saw each sp_Msdel_ took .5 second generating over 200,000 reads.
What, 200,000 reads to delete 1 row?
Query plan should so small reads with 1 delete like shown below.
StmtText -------- Clustered Index Delete(OBJECT:([AW2012_SUB].[SalesLT].[SalesOrderDetail].[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID])
However, looking at the query plan shows many tables being referenced
In example below a DELETE in SaleOrderHeader results in a DELETE on the subscriber also reading in SalesOrderDetail
tmtText ------- Sequence |--Index Delete(OBJECT:([AW2012_SUB].[SalesLT].[SalesOrderHeader].[AK_SalesOrderHeader_SalesOrderNumber])) | |--Table Spool | |--Compute Scalar(DEFINE:([AW2012_SUB].[SalesLT].[SalesOrderHeader].[SalesOrderNumber]=isnull(N'SO' | |--Clustered Index Delete(OBJECT:([AW2012_SUB].[SalesLT].[SalesOrderHeader].[PK_SalesOrderHead |--Clustered Index Delete(OBJECT:([AW2012_SUB].[SalesLT].[SalesOrderDetail].[PK_SalesOrderDetail_SalesOrderID |--Nested Loops(Inner Join, OUTER REFERENCES:([AW2012_SUB].[SalesLT].[SalesOrderHeader].[SalesOrderID])) |--Table Spool |--Clustered Index Seek(OBJECT:([AW2012_SUB].[SalesLT].[SalesOrderDetail].[PK_SalesOrderDetail_Sale
Turns out tables were defined with Foreign Key Check Constraints that were active for Replication. This requires SQL engine to check each related child table before deleting parent row. For this customer there were over 20 child tables for this 1 parent table. However, as the data was coming from Publisher via Replication, the checking to ensure no child rows would have already been performed on the Publisher. There is no need to check the child table again on the Subscriber.
To correct the problem we changed the Subscriber Foreign Key Check Constraints to “not for replication”=True. As they setup Subscribers using Backup\Restore, we also made same change on the Publisher.
ALTER TABLE [SalesLT].[SalesOrderDetail] WITH NOCHECK ADD CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID] FOREIGN KEY([SalesOrderID]) REFERENCES [SalesLT].[SalesOrderHeader] ([SalesOrderID]) ON DELETE CASCADE NOT FOR REPLICATION GO
Now when the Distribution Agent applies changes it receives a “skip check pass” by SQL engine and once again direct DELETE without child-table check is performed. If application connects directly to Subscriber, it is checked, just Replication gets a “skip check pass”.
