Performance kiss of death factors in transactional replication in Visual C#

Printing USS Code 128 in Visual C# Performance kiss of death factors in transactional replication

example, the subscription database transaction log filling up). This can mean that a lengthy period of time is required to apply large batch updates. While these batch updates are being applied, the Distribution Agent will wrap them in a transaction so that it can roll them back on errors, and Subscriber resources are consumed to hold this transaction open. Latencies that were previously several seconds can quickly grow to many minutes, and occasionally to hours (for large numbers of modified rows). SQL Server will get bogged down when replicating transactions that affect large numbers of rows typically in the tens or hundreds of thousands of rows. Strategies for improving performance in this regard are presented in the sections that follow.

REPLICATING THE EXECUTION OF STORED PROCEDURES Replication involves doing your batch DML through a stored procedure and then rep-

licating the execution of the stored procedure. If you choose to replicate the execution of a stored procedure, every time you execute that stored procedure its name and its parameters will be written to the log, and the Log Reader Agent will pick it up and write it to the distribution database, where the Distribution Agent will pick it up and apply it on the Subscriber. The performance improvements are due to two reasons: Instead of 100,000 commands (for example) being replicated, only one stored procedure statement would be replicated. The Log Reader Agent has to read only the stored procedure execution statement from the log, and not the 100,000 constituent singleton commands. Naturally this will only work if you have a small number of parameters to pass. For example, if you re doing a batch insert to 100,000 rows, it will be difficult to pass the 100,000 rows to your stored procedure.

Another trick is to stop your Distribution Agent before you begin your batch update. Use the sp_browsereplcmds stored procedure to extract commands that have not been applied to the Subscriber and issue them on the Subscriber to bring it up to date with the Publisher. Then perform the batch update on your Publisher and Subscriber. The Log Reader Agent will pull all the commands from the Publisher into the distribution database, but make sure that they are not replicated (and hence applied twice at the Subscriber). Use sp_browsereplcmds to determine the transaction identifier (xact_ seqno) for the last batch update command that the Log Reader Agent writes into the distribution database. Note that you can select where to stop and start sp_browsereplcmds as it will take a long time to issue unqualified calls to sp_browsereplcmds. You may have to wait awhile before the Log Reader Agent reads all the commands from the Publisher s log and writes them to the distribution database. When you detect the end of the batch update using sp_browsereplcmds, note the value of the last transaction identifier (xact_seqno) and then use sp_setsub scriptionxactseqno to tell the subscription database that all the batch updates have arrived on the Subscriber. Then restart your Distribution Agent, and the agent will write only transactions that occurred after the batch update.

Take care to note any transactions that may be in the distribution database and occurred after the batch update started and before it stopped. You ll need to ensure that these commands are also applied on the subscription database. The problem with this approach is that the Log Reader Agent still has to process all of the batch update commands that are written to the log. This approach will eliminate the lengthy time required for the Distribution Agent to apply the commands to the Subscriber, but will not address the time that it takes for the Log Reader Agent to read the batch commands from the log and write them to the distribution database.