INF: How to Use sp_scriptdynamicupdproc to Improve the Performance of Update Custom Procedures in SQL Server 2000 Transactional Replication

SQL Server 2000 Service Pack 1 (SP1) adds a new stored procedure, sp_scriptdynamicupdproc, which may improve the performance of Transactional replication when replicating update statements that use custom stored procedures if some of the following conditions are meet:

• The stored procedure is executed by using the MCALL method (this is the default).
• The subscriber table has a non-primary composite clustered index.
• The subscriber table has several indexes (usually more than 5).
• There are text or image columns published.
• A small number of columns are updated.

The subscriber must be Microsoft SQL Server 7.0, or later, to execute dynamic SQL statements by using the sp_executesql stored procedure, which the update custom procedure uses.

The sp_scriptdynamicupdproc stored procedure generates the CREATE PROCEDURE statement to create a dynamic update stored procedure. The update statement within the custom stored procedure is built dynamically based on the MCALL syntax and indicates which columns to change. This approach becomes attractive as the number of indexes on the subscribing table grows and the number of columns actually being changed is small. This stored procedure is executed at the Publisher on the publication database.

The syntax that you use is: The result set is a single nvarchar(4000) column that forms the complete CREATE PROCEDURE statement necessary to create the custom stored procedure on the subscriber.

The default MCALL scripting logic includes all the columns within the update statement that use a bitmap to determine columns that actually changed. If a column did not change, the column is set back to itself. If the column happens to be indexed, extra processing may occur due to index maintenance. If you have several indexes on the subscribing table for which only a few column values are changing, the index maintenance overhead may cause performance problems. The dynamic approach only includes the columns that have changed providing an optimal update string. However, the dynamic update statement may result in extra processing to build the dynamic update statement. Therefore, determining when to use the dynamic approach depends on the users environment. You should prototype the two approaches, and then choose the custom procedure scripting logic that best suits your environment.