Replication Performance Counters
I've showed you how to monitor replication agents using the Enterprise Manager. Although the Replication Monitor is a useful tool, you might want to supplement its output with Performance Monitor counters specific to replication. If you haven't used the Windows Performance Monitor yet, you need to learn how (I don't have enough room in this article to discuss details of the tool). The Performance Monitor can be activated from Control Panel, Administrative Tools, Performance. It has numerous counters, depending on the software running on your server. For SQL Server transactional replication, there are four groups of counters you might want to watch:
SQLServer:Replication AgentsThis group monitors the number of Log Reader and Snapshot agents currently running on the server.
SQLServer:Replication DistThis group monitors Distribution agents' performance, including the following counters:
Dist:Delivered Cmds/SecNumber of commands delivered to subscribers per second. Dist:Delivered Trans/SecNumber of transactions delivered to subscribers per second Dist:Delivery LatencyNumber of milliseconds it takes to deliver transactions from distributor to subscribers.
SQLServer:Replication LogreaderThis group monitors Log Reader agents' performance. The counters are identical to those monitoring the distribution agent's performance. Note that the Latency counter measures number of milliseconds it takes to deliver transactions from publisher to distributor.
SQLServer:Replication SnapshotThis group monitors the snapshot agent. Counters in this group include the following:
Snapshot:Delivered Cmds/SecNumber of seconds delivered to the distributor
Snapshot:Delivered Trans/SecNumber of transactions delivered to the distributor.
Performance is a relative term; how you measure and tune performance depends on your users' expectations. Some users might expect transactions to replicate within seconds; others might not care if data is refreshed only once a day. The point is that the amount of replication performance monitoring and tuning might differ greatly from one environment to the next. Make sure that you are aware of users' expectations and manage them accordingly. SQL Server is capable of delivering transactions within seconds, given appropriate resources (network, memory, dedicated distributor server, and so on). I'll talk more about factors affecting replication performance in the next article.