Initial Merge Synchronization not using Bulk Insert


Initial Merge Synchronization not using Bulk Insert

Chris Skorlinski
Microsoft SQL Server Escalation Services

Working with a customer to setup a large subscriber we noticed the Merge Agent was NOT doing a BULK INSERT, but instead was doing row-by-row insert.  This had significant overhead for the Merge Agent resulting in longer then expected initial/reinit synchronization times.

  Downloaded 33 change(s) in 'Customer' (33 inserts): 33 total
  Applied the snapshot and merged 33 data change(s) (33 insert(s), 0 update(s), 0 delete(s), 0 conflict(s)).

SQL Server Profiler trace shows Merge Agent inserting rows in a batch using MSmerge_ins_sp_{guid}_BATCH or inserting rows individually using MSmerge_ins_sp_{guid}.  Merge Agent was not calling the Bulk Insert command as expected.

   Bulk copied data into table 'Customer' (33 rows)

We discovered the customer was using a Dynamic filter based on Host_Name(). To take advantage of BULK INSERT, the Dynamic Snapshot must be:

1) pre-generated by executing the Dynamic Snapshot job.  The Dynamic snapshot job appear under SQL Server Agent, Jobs, and start with name “dyn_”.

or

2) Merge Agent allowed to dynamically generate a new snapshot (see “Data Partitions” page on the Publication Properties. 

image

If the Data Partition was pre-created and the dynamic snapshot generated before Merge synchronization the Merge Agent inserted data using Bulk Insert.


Comments (1)

  1. Kevin says:

    I'm having this same issue, but it's complicated by the fact that we use FTP to distribute the snapshot and it's compressed (pull).  In merge, if you use FTP, then the merge agent will always generate a new snapshot on initialization, you can't pre-create the snapshot.

    So I'm running into this issue sporadically, sometimes it will bulk insert, and sometimes it will not.  Bulk insert brings initialization time in at 10 minutes, where batch or row inserts take several hours.  We end up starting the process, waiting 10 minutes to see if it will bulk insert, and if it doesn't then we stop it -> recreate snapshots -> reinitialize and wait again.  Sometimes it bulk inserts first try, sometimes (like this morning) we've had to redo the process 4 times.

    We've had this issue since we started our merge system in SQL 2008R2, and it continues in SQL 2012.  Any ideas or suggestions?