up vote 0 down vote favorite
share [g+] share [fb]

I have an MS SQL 2000 server that uses transactional replication and a request to user System Monitor and check if replication is doing ok and not affecting performance of the server.

Any tips on what counters to chose from, how to interpret them, any good links in this direction ?

Thank you

link|improve this question
83% accept rate
feedback

2 Answers

up vote 1 down vote accepted

Replication should have a huge number of specific counters to choose from. Several of the agents have a Delivery Latency counter which will tell you if the replication is behind or not. You want that number as low as possible. The higher the number (you'll have to see what your environment produces) the further behind your subscribers are.

As for effecting performance on the server if your distributor is running on the publisher that can cause performance issues, especially if you have a lot of Subscribers that will impact the load on the publisher. If they are separate then there shouldn't be a lot of load on the publisher. If you have them on the same server, consider moving the distributor to it's own server.

link|improve this answer
Distributor's running on the same machine as the publisher but there's only one subscriber. Thank you for your answer! – Paul Oct 7 '09 at 15:47
Unless you are pushing tens of thousands of transactions per second (or more depending on your hardware) you really shouldn't have any performance problems from replication then. If you have large transactions all being triggered via stored procedures you can look into replicating the stored procedures, then the stored procedure execution is replicated instead of the table changes which can reduce replication latency and improve overall performance. Replication (especially in SQL 2000) is a beast and you'll have to try different things to see what works best. – mrdenny♦ Oct 7 '09 at 17:55
feedback
up vote 1 down vote

We've had replication performance issues caused by too many VLFs (virtual log files) within the Publisher's transaction log causing internal fragmentation. The high number of VLFs were caused by the log expanding over time using too small a growth increment (so we probably had external file fragmentation issues too).

You can check the number of VLFs by running DBCC LOGINFO against the Publisher database.

Check these articles for further details and how to resolve the issue if your environment is affected:

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.