SQL Server Performance Forum – Threads Archive

Linked Server Query

Hello ,
I have 2 servers . I have setup linked server between both of them. I am running a query from Server A to access the data from Server B . I would like to know whether it uses resources from Server A or Server B?
Otherwise, What would be the best option to do some kind of load (HIGH reource usage SQL QUERIES Can’t do more optimization.) balance of the queries.
IF server A fully utilized we would like to route some queries to SERVER b.
any one implemented that kind of solution? any ideas?
Thanks in Advance

---

I think it depends. the optimizer makes some kind of decision based on data volumes on the respective servers.
Problem is, you can’t see remote query execution plans. Also beware blocking issues if they’re data modifications.

---

You can perform the joins in the linked server by specifying REMOTE JOIN Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

---

I want to know whether the query users SERVER A resouces or server B resources.

---

Also, If I ran a remote stored proc does it create tables (on fly) on server A or server B

---

What sort of tables? Temporary tables?

---

they are kind of PERM temp tables. They will get create in Database

---

Yes, tables can be created but depends on the code how it is written…
You can execute a procedure on the destination server which creates the tables or you can use dynamic sql to create tables…
MohammedU.
Moderator
SQL-Server-Performance.com

---

We are using Begin Tran /Commit Tran in our stored procs. Do We need to modify them ? like BEGIN DISTRIBUTED TRAN /COMMIT TRAN?
Could some one confirm?

---

Hi there.. The best way to do this, is to take manual control of what the optimizer does… Make the best join you can make on the remote server. Insert this record into a temporal table on the "local" machine. Perform relevant joins locally and return you result… regs, Eventloop
"Follow the join tree" – Dan Tow

---

]]>