The following is one of 15 replication tips in 15 minutes. Click here to return to the full list of tips.
3 of 15 replication tips: Replicate stored procedure execution
If at all possible, replicate the execution of stored procedures. This option will work best if the bulk of the updates occurring on the publishers effects more than one row at a time. If the bulk of the updates effects a single row, or if they are singletons, there will be no performance gain by replicating the execution of stored procedures.
To get an idea whether your update patterns are batch activity or singleton activity, expand the Replication Monitor folder for your Publisher/Distributor or Distributor; expand the Agents folder; expand the Distribution Agents folder; and click on your Distribution Agent and observe the values for # Trans and # cmds. If the value of # Trans/#cmds is close to 1 the majority of your DML only affects one row (or is singletons) and you will not benefit from replicating the execution of stored procedures. The lower this ratio, the better the performance improvement will be when you start replicating the execution of stored procedures.
You can have completely different logic in your stored procedures on both sides of your replication solution as long as the stored procedures have the same name.
Click here to return to 15 replication tips in 15 minutes.
About the author: Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and subsequently studied both economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.
