SQL Server Performance

Linked Server Query

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by SQLDBAS, Feb 27, 2007.

  1. SQLDBAS New Member

    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
  2. thomas New Member

    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.
  3. Roji. P. Thomas New Member

  4. SQLDBAS New Member

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

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

    What sort of tables? Temporary tables?
  7. SQLDBAS New Member

    they are kind of PERM temp tables. They will get create in Database
  8. MohammedU New Member

    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...

  9. SQLDBAS New Member

    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?
  10. eloop New Member

    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...



    "Follow the join tree" - Dan Tow

Share This Page