Replication: Merge Filters – optimize optimize optimize

I noticed that many people overlook optimizing their filters in a merge replication topology. This is crucial to performance and attempting to tweak the replication itself (example: using pre-computed partitions) may not be sufficient.

An example of such:

All the join filters specified against the published articles in merge publication would be evaluated during DML operations when Pre-computed partitions is turned on and during the merge sync process when Pre-computed partitions is turned OFF.

I had an issue in which the DML queries where taking a long time to execute when pre-computed partitions was enabled. When I reduce the join filter nesting level by 1 (i.e. nesting level of join filters to 4), the DML operations (INSERT,UPDATE and DELETE) executed pretty quick as expected.

Hence please take time to look at the filters of your replication topology and follow the guide lines:

Join Filters:

https://msdn.microsoft.com/en-us/library/ms152486.aspx

Parameterized Row Filters:

https://technet.microsoft.com/en-us/library/ms152478.aspx 

** please view the “general considerations” and “additional considerations” sections of this article

More information can also be found in BOL.