Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

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 Agents—This group monitors the number of Log Reader and Snapshot agents currently running on the server.

  • SQLServer:Replication Dist—This group monitors Distribution agents' performance, including the following counters:

  • Dist:Delivered Cmds/Sec—Number of commands delivered to subscribers per second.
    Dist:Delivered Trans/Sec—Number of transactions delivered to subscribers per second
    Dist:Delivery Latency—Number of milliseconds it takes to deliver transactions from distributor to subscribers.
  • SQLServer:Replication Logreader—This 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 Snapshot—This group monitors the snapshot agent. Counters in this group include the following:

  • Snapshot:Delivered Cmds/Sec—Number of seconds delivered to the distributor

    Snapshot:Delivered Trans/Sec—Number 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.

  • + Share This
  • 🔖 Save To Your Account