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.