One feature that many of my clients are unaware of is that the batched execution of a stored procedure can be replicated under certain circumstances. This can result in significant improvement in performance for stored procedures on replicated databases.

This MSDN article does a good job of breaking down the basics, but here's a demonstrable example you can use.

The rules about batching up the stored procedure executions are fairly severe, and require the SERIALIZABLE isolation level for the stored procedure's transaction. The goal is to see a single 100-row transaction, not 100 single-row transactions. The latter, inefficient behavior is the default behavior that we want to avoid. We'll prove it later.

Step one: Execute the below script block in an existing replicated database. (Not production, obviously.) Or, set up a new simple transactional replication between two databases, then run the below script block.

Step two: Add both the table and the stored procedure to a new publication as articles. The stored procedure must be added as an article with Replicate option = 'Execution in a serialized transaction of the SP'.

Step three: Set up a default "TSQL" trace on the Subscriber instance.

Step four: Execute the below on the Publisher instance so that you can see the replicated stored procedure execution in action.

This is the default behavior, and it's a bit shocking to see for the first time.

Remember our rules about the SERIALIZABLE isolation level, which means this isn't necessarily appropriate for all environments. This change could lead to an increase in locking during the execution of your procedure, so educate yourself on heightened isolation levels and why they are important.