Tag: Performance

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
Read More »

SQL 2005 Merge Agent Blocking in MSmerge_generation_partition_mappings

If you observe blocking in the SQL Server 2005 Merge Agent script out the “nc1MSmerge_generation_partition_mappings” index and verify the “INCLUDE (changecount)” option exists.  TO verify, generate the create index script by expanding the system tables in the published database.  Then expand “MSmerge_generation_partition_mappings” table. Right click the “nc1MSmerge_generation_partition_mappings” index and script to new query windows.  If
Read More »