Share via

  • Article

How to: Optimize Parameterized Row Filters (SQL Server Management Studio)

The following settings can be used to optimize parameterized row filters:

  • Partition Options

    Set this option on the Properties page of the Article Properties - <Article> dialog box, or in the Add Filter dialog box. Both dialog boxes are available in the New Publication Wizard and the Publication Properties - <Publication> dialog box. The Article Properties - <Article> dialog box allows you to specify additional values for this option that are not available in the Add Filter dialog box.

  • Precompute Partitions

    This option is set to True by default if the articles in your publication adhere to a set of requirements. For more information about these requirements, see Optimizing Parameterized Filter Performance with Precomputed Partitions. Modify this option on the Subscription Options page of the Publication Properties - <Publication> dialog box.

  • Optimize Synchronization

    This option should be set to True only if Precompute Partitions is set to False. Set this option on the Subscription Options page of the Publication Properties - <Publication> dialog box.

For more information about using the New Publication Wizard and accessing the Publication Properties - <Publication> dialog box, see How to: Create a Publication and Define Articles (SQL Server Management Studio) and How to: View and Modify Publication and Article Properties (SQL Server Management Studio).

To set Partition options in the Add Filter or Edit Filter dialog box

  1. On the Filter Table Rows page of the New Publication Wizard or the Filter Rows page of the Publication Properties - <Publication> dialog box, click Add, and then click Add Filter.

  2. Create a parameterized filter. For more information, see How to: Define and Modify a Parameterized Row Filter for a Merge Article (SQL Server Management Studio).

  3. Select the option that matches how data will be shared among Subscribers:

    • A row from this table will go to multiple subscriptions

    • A row from this table will go to only one subscription

    If you select A row from this table will go to only one subscription, merge replication can optimize performance by storing and processing less metadata. However, you must ensure that the data is partitioned in such a way that a row cannot be replicated to more than one Subscriber. For more information, see the section "Setting 'partition options'" in the topic Parameterized Row Filters.

  4. Click OK.

  5. If you are in the Publication Properties - <Publication> dialog box, click OK to save and close the dialog box.

To set Partition Options in the Article Properties - <Article> dialog box

  1. On the Articles page of the New Publication Wizard or the Publication Properties - <Publication> dialog box, select a table, and then click Article Properties.

  2. Click Set Properties of Highlighted Table Article or Set Properties of All Table Articles.

  3. In the Destination Object section of the Properties tab of the Article Properties - <Article> dialog box, specify one of the following values for Partition Options:

    • Overlapping

    • Overlapping, disallow out-of-partition data changes

    • Nonoverlapping, single subscription

    • Nonoverlapping, shared between subscriptions

    For more information about these options and how they relate to the options available in the Add Filter and Edit Filter dialog boxes, see the "Setting 'partition options'" section of Parameterized Row Filters.

  4. Click OK.

  5. If you are in the Publication Properties - <Publication> dialog box, click OK to save and close the dialog box.

To set Precompute Partitions

  1. On the Subscription Options page of the Publication Properties - <Publication> dialog box, select a value for the Precompute Partitions option. The property is read-only if:

    • The publication does not meet the requirements for precomputed partitions.

    • A snapshot has not yet been generated for the publication. In this case, the option displays a value of Set automatically when a snapshot is created.

  2. Click OK.

To set Optimize Synchronization

  1. On the Subscription Options page of the Publication Properties - <Publication> dialog box, select a value of True for the Optimize Synchronization option.

  2. Click OK.