MS SQL Server, Replication needs better performance can anyone help or suggest

Replication needs better performance can anyone help or suggest

Author	Message
Joshua Goodste #1 / 3	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

Stride #2 / 3	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

TRaef #3 / 3	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

Page 1 of 1

[ 3 post ]