Linked server perfmance tuning.

 We have two SQL servers, one is the production server, and the other one is reporting server.  Reporting server keepts pulling records from production server and deletes expired record from production server. There are over 10M records, and there is a performance issue: sync operation compeletes in a few minutes, but the purge operation (deleting expired data) takes nearly two hours! It takes too much time.

 

We tried to schedule the job hourly, daily and weekly and found out that It takely nearly the same time! Then we tried to execute the purging script locally, it took no more than a minute! We can get two inductions here: 1. The execution time depends on the overall records. 2 The bottleneck is the network.

 

The total records cannot be reduced and so cannot the bandwidth be increased. The only way  to solve the problem is to execute the script locally. We searched the internet and found out that OPENQUERY should be able to solve this problem. However, the syntax is really weird for me -_-!. All of a sudden I got a spark, how about encapsulate the purging script into a stored procedure and invoke it remotely? Things turns to be clear. After the "RPC OUT" option of the linked server been turned on, the new created SP executed successfully in about half a minute.