Optimizing Distributed Queries
To improve performance, SQL Server performs the following types of optimization specific to distributed queries:
Remote query execution used with OLE DB SQL Command providers.
An OLE DB provider is considered to be a SQL Command provider if the OLE DB provider meets the following minimum requirements:
Supports the Command object and all its mandatory interfaces.
Supports DBPROPVAL SQL SUBMINIMUM Syntax, or ISO at Entry level or higher, or ODBC at Core level or higher. The provider should expose this dialect level through the DBPROP_SQLSUPPORT OLE DB property.
Indexed access used with OLE DB Index providers.
An OLE DB provider is considered to be an Index provider if the OLE DB provider meets the following minimum requirements:
Supports the IDBSchemaRowset interface with the TABLES, COLUMNS, and INDEXES schema rowsets.
Supports opening a rowset on an index by using IOpenRowset by specifying the index name and the corresponding base table name.
The Index object should support all its mandatory interfaces: IRowset, IRowsetIndex, IAccessor, IColumnsInfo, IRowsetInfo, and IConvertTypes.
Rowsets opened against the indexed base table, by using IOpenRowset, should support the IRowsetLocate interface for positioning on a row based off a bookmark retrieved from the index.
Remote Query Execution
SQL Server tries to delegate as much of the evaluation of a distributed query to the SQL Command provider as possible. An SQL query that accesses only the remote tables stored in the data source of the provider is extracted from the original distributed query and executed against the provider. This behavior reduces the number of rows returned from the provider and allows the provider to use its indexes in evaluating the query.
Considerations that affect how much of the original distributed query gets delegated to the SQL Command provider include the following:
The dialect level supported by the SQL Command provider
Collation compatibility
Dialect Level Supported by the SQL Command Provider
SQL Server delegates operations only if they are supported by the specific dialect level. The dialect levels from highest to lowest are: SQL Server, ISO Entry level, ODBC core, and Jet. The higher the dialect level, the more operations SQL Server can delegate to the provider.
Note
The SQL Server dialect level is used when the provider corresponds to a SQL Server linked server.
Each dialect level is a superset of the lower levels. Therefore, if an operation is delegated to a particular level, the operation is also delegated to all higher levels.
Queries that involve the bit and uniqueidentifer data types are never delegated to a provider and are always evaluated locally.
When the SET option CONCAT_NULL_YIELDS_NULL is OFF, string concatenation is always done locally.
The following operations/syntactic elements are delegated to the dialect level indicated, and to all higher levels:
SQL Server: Outer join, CUBE, ROLLUP, modulo operator (%), bitwise operators, string functions, and arithmetic system functions.
ISO Entry Level: UNION, and UNION ALL.
ODBC Core: Aggregation functions with DISTINCT, and string constants.
Jet: Aggregate functions without DISTINCT, sorting (ORDER BY), inner joins, predicates, subquery operators (EXISTS, ALL, SOME, IN), DISTINCT, arithmetic operators not mentioned in higher levels, constants not mentioned in higher levels, and all logical operators.
For example, all operations except those that involve CUBE, ROLLUP, outer join, modulo operator (%), bitwise operators, string functions, and arithmetic system functions are delegated to a ISO Entry level provider that is not also SQL Server.
Collation Compatibility
For a distributed query, the comparison semantics for all character data is defined by the character set and sort order of the local instance of SQL Server. SQL Server supports multiple collations. Collations can be different for each column, and each character value has an associated collation property. SQL Server interprets the collation property of character data from a remote data source and treats it accordingly. For more information on the collation of remote columns, see Collations in Distributed Queries.
SQL Server can delegate comparisons and ORDER BY operations on character columns to a provider only if it can determine the following:
The underlying data source uses the collation sequence and character set of the column.
The character comparison semantics follow the ISO and SQL Server, standard.
The topic Collations in Distributed Queries summarizes how SQL Server determines a collation for each column. If the remote data source supports that collation, the provider is considered collation compatible.
Other SQL Support Considerations
The following SQL syntax elements are not dictated by the SQL dialect levels:
Nested query support
If the provider supports nested queries (subqueries), SQL Server can delegate these operations to the provider. Because nested query support cannot be automatically determined from OLE DB properties, the system administrator should set the NestedQueries provider option to indicate to SQL Server that the provider supports nested queries.
Parameter marker support
If the provider supports parameterized query execution by using the ? parameter marker in a query, SQL Server can delegate parameterized query execution to the provider. Because parameter marker support cannot be automatically determined from OLE DB properties, the system administrator should set the DynamicParameters provider option to indicate to SQL Server that the provider supports parameter markers.
LIKE support
If the provider supports the LIKE operator as implemented in SQL Server syntax and semantics, the SqlServerLike provider option can be set to indicate support.
For more information on setting these provider options, see Configuring OLE DB Providers for Distributed Queries.
Indexed Access
SQL Server can use execution strategies that involve using the indexes of the Index provider to evaluate predicates and perform sorting operations against remote tables. To enable indexed access against a provider, set the IndexAsAccessPath provider option.
Additionally, when you use indexes that involve character columns, set the collation compatible linked server configuration option to true for the corresponding linked server. For more information, see sp_serveroption (Transact-SQL).
Note
Graphically display the execution plan by using SQL Server Management Studio to determine the execution plan for a given distributed query. When remote query execution is employed in the execution plan, it is represented using the Remote Query logical and physical operator. The argument of this operator shows the remotely executed query.