Given two Microsoft SQL servers:

Say ServerA has a view titled "View1": SELECT * FROM ServerB.database.dbo.Table1 WHERE Column1 IN (1,2,3)

and a stored procedure "sp_Foo": SELECT * FROM View1 WHERE Column1=1

When I execute EXEC sp_Foo, which query is executed on which server? Are they all executed on ServerB? All on ServerA? Or is the view query executed on ServerB and the SP query executed on ServerA?

  • Looking at the execution plan, it only shows cost, not which server it is costing. – lukiffer Jun 10 '13 at 16:15
  • 1
    The main chunk of the work will be done on ServerA. i.e. the CPU to run the query/plan and the IO to retrieve the data from ServerA's disk. The resultant data set will be passed back to ServerB which is basically network traffic. – StanleyJohns Jun 10 '13 at 16:34

When sp_foo executes there is only one query:

  FROM ServerB.database.dbo.Table1 
  WHERE Column1 IN (1,2,3))
WHERE Column1=1;

This query can execute locally against a remote scan or it can execute remotely. What happens cannot be foretold, because it depends on the capabilities advertised by driver in use by the linked server. Typically a query like above over a linked server that uses SQLNCLI driver it will execute remotely. Local execution backed by a remote scan shows up on the execution plan as Remote Scan Showplan operator.

More complex plan can execute somehow mixed, partially remote partially locally, eg. see Linked Server behavior when used on JOIN clauses.

| improve this answer | |

Since you have ServerB.database.dbo.Table1 meaning you are using Linked server, the server will used link server configuration to send the query to the linked server and retrieve the relevant results.

Rest everything will be on the server that you are running the query on.

Using SSMS, the real execution of the query will take place on the remote server itself along with Parsing and reusing the plan if it already exists or creating one if it does not eixst. SSMS is just a tool - it has no build in query engine.

Now if you have joins between your local machine and remote machine (using linked server), sql server will pull in rows to the local server to process the result set.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.