Replication needs better performance can anyone help or suggest 
Author Message
 Replication needs better performance can anyone help or suggest

We have implemented a transactional push replication set between our
staging and production server. The reason this is in place is because
we load 1+ million rows daily including inserts, updates, and deletes.
When we did this on production alone it brought it to its knees !! and
we had to shut down the site each night due to performance cost. Now
we load the rows on a staging system and let replication push them up.
we found this to have a MUCH smaller impact........HOWEVER :-) we now
see times of 10-24 hours for this stuff to replicate. We added double
NICS to allow for bigger pipe. we' have max recognized cpu's and RAM.
we have these two boxes on there own private network. We tried to
currsor the records in and tried bulk loading them.. but we always get
the same results. How can we make it faster... we should be able to
load alot more then 1 million rows a day... and replicate as well!!!!
If more info is needed i will glady provide.

Joshua Goodstein



Sat, 06 Dec 2003 20:11:19 GMT
 Replication needs better performance can anyone help or suggest


Quote:
> We have implemented a transactional push replication set between our
> staging and production server. The reason this is in place is because
> we load 1+ million rows daily including inserts, updates, and deletes.
> When we did this on production alone it brought it to its knees !! and
> we had to shut down the site each night due to performance cost. Now
> we load the rows on a staging system and let replication push them up.
> we found this to have a MUCH smaller impact........HOWEVER :-) we now
> see times of 10-24 hours for this stuff to replicate. We added double
> NICS to allow for bigger pipe. we' have max recognized cpu's and RAM.
> we have these two boxes on there own private network. We tried to
> currsor the records in and tried bulk loading them.. but we always get
> the same results. How can we make it faster... we should be able to
> load alot more then 1 million rows a day... and replicate as well!!!!
> If more info is needed i will glady provide.

On suggestion, in the stored procs on the subscriber, use paglock hints.

We found that often in our case the subscriber was spending a lot of its
time escelating row locks to extent locks to page locks.  Since we had a
good idea of how our data was moving about the systems, we were able to
force it to just do a pagelock first.  This was much faster for us.

Quote:

> Joshua Goodstein



Sat, 06 Dec 2003 21:57:46 GMT
 Replication needs better performance can anyone help or suggest
I would like to see a diagram of your replication as well as the scripts for
your DTS.

Please contact me further to discuss.

Thomas J. Raef
e-Based Solutions, Inc.



Wed, 10 Dec 2003 20:58:20 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Anyone know of some canned (cheap or free) DB performance testing software

2. anyone suggest a good sql book?

3. Can anyone suggest a good book on advanced sql server programming

4. Can anyone suggest better SQL syntax

5. Can anyone suggest the best method of distributing Open Client

6. Can anyone suggest any good books on Ingres

7. Urgent: Help Needed for better performance

8. Need Merge Replication Performance Help (w/340 subscribers!)

9. Anyone has some info about replication performance?

10. OLAP Server Error *Can anyone suggest anything to me*

11. Can anyone suggest a database?

12. OLAP Server Error *Can anyone suggest anything to me*


 
Powered by phpBB® Forum Software