Remote Query
Introduction
The Remote Query operator sends a query to a remote server for execution, and then returns the results of that query to its parent operator.
Cases where Remote Query are used are, based on my observations so far, are:
- Reading data from a remote table or view using four-part naming (remote server name, database name, schema name, and table or view name).
- Reading data from a remote table or view using an OPENDATASOURCE expression.
If you find other cases where the Remote Query operator is used, please let me know!
According to Microsoft’s documentation, a Remote Query on the lower input of a Nested Loops operator differentiates between rebinds and rewinds, to reproduce the same results without re-executing the query on a rewind. However, I have not been able to get it to actually expose this behavior. While Remote Query does indeed count rewinds and rebinds, it seems to actually re-execute the query in both cases. So either the documentation is misleading, or the different rewind behavior only occurs in some specific cases that I have not found yet.
Until I find proof that a rewind can actually reuse old results without executing the query on the remote server again, I will assume that Remote Query does not actually have rewind functionality. So there is no description of the rewind functionality on this page.
Visual appearance in execution plans
Depending on the tool being used, a Remote Query operator is displayed in a graphical execution plan as shown below:
SSMS and ADS |
Legacy SSMS |
Plan Explorer |
Paste The Plan |
Algorithm
The basic algorithm for the Remote Query operator is as shown below:
Note that this flowchart is a simplification. It doesn’t show that execution stops whenever a row is returned, and resumes where it was upon next being called.
Send query
The Send query action sends the statement to be executed (as specified in the Remote Query property) to the remote server where it has to be executed (the Remote Source). If the Remote Query is parameterized, then statement is prepared and executed as a prepared statement.
If the query uses an OPENDATASET expression, then the Remote Source property is listed as “<UNKNOWN>”. I assume that the actual information (provider name and connection string) is stored in the internal execution plan, but not included in the export to XML.
Read first / next row
After the Remote Query has been sent to the Remote Source for execution, Remote Query fetches the rows from the result set one by one, and then returns them to its parent operator.
Operator properties
The properties below are specific to the Remote Query operator, or have a specific meaning when appearing on it. For all other properties, see Common properties. Properties that are included on the Common properties page but are also included below for their specific meaning for the Remote Query operator are marked with a *.
Property name | Description |
---|---|
Remote Query | The text of the query that gets sent to the Remote Source for execution. This can be a parameterized query, using unnamed parameters. The execution plan does not expose the values that are passed in at runtime for those parameters. (I assume that this value is present in the actual internal representation of the execution plan, but cannot be represented in the XML representation used when exporting execution plans). |
Remote Source | The name of the server where the Remote Query needs to be executed. When Remote Query is used to read from an OPENDATASET expression, this property shows “ |
Implicit properties
This table below lists the behavior of the implicit properties for the Remote Query operator.
Property name | Description |
---|---|
Batch Mode enabled | The Remote Query operator supports row mode execution only. |
Blocking | The Remote Query operator is non-blocking. |
Memory requirement | The Remote Query operator does not have any special memory requirement. |
Order-preserving | The Remote Query operator is fully order-preserving: it returns data in the order it is produced by the Remote Source, and the optimizer is aware of this. |
Parallelism aware | The Remote Query operator can only be used in serial execution plans. |
Segment aware | The Remote Query operator is not segment aware. |