Linked server performances with push and pull

I red this article https://technet.microsoft.com/en-us/library/dd263442(v=sql.100).aspx and http://sqlblog.com/blogs/linchi_shea/archive/2010/12/01/linked-server-and-performance-impact-direction-matters.aspx .

1)Is blog valid explanation for push/pull performances? Why is row by row implemented in push?

2)Are same rules of push and pull buffer/pipeline applied all over tsql or those rules are only for replications? What about linked servers?

3)Why is there buffer on push, why is limited on 40k and is it possible to change it?

4)If linked server uses some other rule, can you please tell me which one and why is difference in performance? What is the reason for that rule (if any)?


  • Edited by Marko_O 17 hours 23 minutes ago
August 20th, 2015 9:44am

Hi Marko,

The following article deals with linked server performance:

http://www.sqlusa.com/articles2005/linkedserver/

Free Windows Admin Tool Kit Click here and download it now
August 20th, 2015 10:11am

Now, this is comedy! Marko posts a link to a blog from Linchi Shea, who certainly knows his stuff. Whereupon Milan answers with a link from Kalman Toth, who, well, I would not expect any answers raised from reading a blog post from Linchi in his material.

I will need to find the time to night to read Linch's post to see if I can answer the questions.

August 20th, 2015 10:33am

1)Is blog valid explanation for push/pull performances?

I think so.

Why is row by row implemented in push?

There is not really much alternative. There exists two ways in OLE DB to sent many rows of data at once:

1) Table-valued parameter
2) Bulk load.

But none of these are usable here. TVP requires a predefined type on the remote side, and the provider would have to generate commands to copy from the TVP to the target table. Bulk load requires server-level permission which the user does not have.

So the only way the provider can do it, is row by row.

2)Are same rules of push and pull buffer/pipeline applied all over tsql or those rules are only for replications? What about linked servers?

Not all over T-SQL. Linked servers work the way they do, because SQL Server communicates with the linked server over OLE DB, and it is largely agnostic to what is on the other end.

I don't know replication well enough to comment on push and pull subscriptions, but I believe OLE DB is involved there as well, so it could be a similar case.

However, generally, you should see linked servers and replication as two quite different beasts.

Free Windows Admin Tool Kit Click here and download it now
August 20th, 2015 6:10pm

Thanks for answer :) 

Can you just clarify few more thing for me. Is it possible to add bulk permission and use push as pull? During my testing of pull and push i could not see that great time difference that would confirm 'row by row' theory, is there any optimization or speedup happens sometime? Is row by row delay equal to one round trip per row or they are sent in on blocks/groups? 

August 21st, 2015 2:48am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics