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.