How to: Optimize Parameterized Row Filters (Replication Transact-SQL Programming)

When you use parameterized filters, you can control how the filters are processed by merge replication by specifying either the use partition groups option or the keep partition changes option when you create a publication. These options improve the synchronization performance for publications with filtered articles by storing additional metadata in the publication database. You can control how the data is shared among Subscribers by setting partition options when you create an article.

For more information about these requirements, see Parameterized Row Filters. For definitions of the filtering options for @keep_partition_changes and @use_partition_groups, see sp_addmergepublication.

To specify merge filter optimizations when creating a new publication

1. At the Publisher on the publication database, execute sp_addmergepublication. Specify @publication and a value of true for one the following parameters:

   • @use_partition_groups: - the highest performance optimization, provided that the articles conform to the requirements for precomputed partitions. For more information, see Optimizing Parameterized Filter Performance with Precomputed Partitions.
   • @keep_partition_changes - use this optimization if precomputed partitions cannot be used.

2. Add a snapshot job for the publication. For more information see How to: Create a Publication (Replication Transact-SQL Programming).

3. At the Publisher on the publication database, execute sp_addmergearticle, specifying the following parameters:

   • @publication - the name of the publication from step 1.
   • @article - a name for the article
   • @source_object - the database object being published.
   • @subset_filterclause - the optional parameterized filter clause used to horizontally filter the article.
   • @partition_options - the partition options for the filtered article.

4. Repeat step 3 for each article in the publication.

5. (Optional) At the Publisher on the publication database, execute sp_addmergefilter to define a join filter between two articles. For more information, see How to: Define and Modify a Join Filter Between Merge Articles (Replication Transact-SQL Programming).

To view and modify merge filter behaviors for an existing publication

1. (Optional) At the Publisher on the publication database, execute sp_helpmergepublication, specifying @publication. Note the value of keep_partition_changes and use_partition_groups in the result set.

2. (Optional) At the Publisher on the publication database, execute sp_changemergepublication. Specify a value of use_partition_groups for @property and either true or false for @value.

3. (Optional) At the Publisher on the publication database, execute sp_changemergepublication. Specify a value of keep_partition_changes for @property and either true or false for @value.

   Note

   When enabling keep_partition_changes, you must first disable use_partition_groups and specify a value of 1 for @force_reinit_subscription.

4. (Optional) At the Publisher on the publication database, execute sp_changemergearticle. Specify a value of partition_options for @property and the appropriate value for @value. See sp_addmergearticle for definitions of these filtering options.

5. (Optional) Start the Snapshot Agent to regenerate the snapshot if necessary. For information about which changes require a new snapshot to be generated, see Changing Publication and Article Properties.