Performance impact of Transactional Push Replication

Hi,
I have a transactional push replication from our Publisher on server MM1 and subscriber + distributor on server MM2. We have had performance slowdowns on the MM1 publisher server and we removed the replication to see if this was causing the problem. I b
elieve the only replication process is the sp_replcmd (logreader agent) on the publisher. Are there any statistics that I can use to determine if replication was causing the slowdowns? Any help would be appreciated.
Thanks
Don
Don,
you could use performane monitor and correlate
SQLServer:Replication Dist
SQLServer:Replication Logreader
(Dist:Delivered Cmds/Sec,Dist:Delivered Trans)
with Process:%Processor Time for the sqlserver and sqlserver agent
processes.
Ideally you'd measure the threads used in the replication process and their
contribution, but the above technique should show any large trends.
HTH,
Paul Ibison

Performance degradation after replication rolled out

Hi all.

Any assistance would be greatly appreciated.

We recently created transactional replication to hopefully improve performance issues we were expereincing. The replication is between 2 SQL Servers (2000), and since we have introduced the replication, the performance has degraded considerably.

I will try and explain the scenario.

We have a primary db that our internal users use and we also have the newly replicated db that our website and another application use. The users are complaining that the website and the internal application is extremely slow and I was just wondering if it is possible to do an Index Tuning on both the primary db and replicated db based on trace files so as to create new indexes or would this have an impact on the replication?

Thanks in advance.

Running the index tuning wizard is a good idea. Also, generate some trace files and look through them..see if there are any CPU spikes..check out your most frequently used stored procs and look for any missing indexes. Look for the columns in all the where conditions in your T-SQL statements. Do you have indexes on those columns? Index Tuning Wizard can identify most such things. You might have to stop replication during this. You could do it on a weekend maintenance window.

|||

There's also a basic whitepaper on transactional replication performance you should read through to see if there's anything that you can take advantage of - "Transactional Replication Performance Tuning and Optimization" - http://msdn2.microsoft.com/en-us/library/aa902656(sql.80).aspx.

Do you replicate a lot of updates? And do the tables on the subscriber have more than one index on it? If so, I highly recommend reading up on sp_scriptdynamicupdproc.