Optimizing Distributed Queries

Microsoft® SQL Server™ distributed queries allow users to reference remote tables and rowsets as though they are local tables by using SELECT, INSERT, UPDATE, and DELETE statements. Distributed queries cause data to be retrieved across the network when data sources are located on remote computers; therefore, SQL Server performs two types of optimization, specific to distributed queries, to improve performance:

• Remote query execution used with OLE DB SQL Command Providers.
• Indexed access used with OLE DB Index 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 at Entry level, or higher, the SQL-92 dialect, or Core level, or higher, the ODBC dialect. The provider should expose this dialect level through the DBPROP_SQLSUPPORT OLE DB property.

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 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 (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 attempts 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 provider’s data source is extracted from the original distributed query and executed against the provider. This 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 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, SQL-92 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, then it is also delegated to all higher levels.

  Queries involving the following are never delegated to a provider and are always evaluated locally:

  • bit
  • uniqueidentifier
  • timestamp constants
  • Data modification operations
  • TOP clause
  • Data conversion operations

  The following operations/syntactic elements are delegated to the dialect level indicated (and all higher levels):

  • SQL Server: Outer join, CUBE, ROLLUP, modulo operator (%), bit-wise operators, string functions, and arithmetic system functions.
  • SQL-92 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 involving CUBE, ROLLUP, outer join, modulo operator (%), bit-wise operators, string functions, and arithmetic system functions are delegated to a SQL-92 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 SQL Server. SQL Server can delegate comparisons and ORDER BY operations on character columns to a provider only if it can determine that:

  • The underlying data source uses the same collation sequence and character set.
  • The character comparison semantics follow the SQL-92 (and SQL Server) standard.

  If the above conditions are true, then the data source is defined as collation compatible with the local SQL Server. SQL Server determines collation compatibility automatically for other computers running SQL Server. However, for linked servers defined against other data sources, SQL Server cannot make this determination. Therefore, SQL Server delegates character comparisons and ORDER BY operations on character columns only to other computers running SQL Server.

  For linked servers defined against database sources other than SQL Server, it is the responsibility of the system administrator to determine if the remote data source corresponding to the linked server is collation compatible with SQL Server. If so, the administrator should set the collation compatible linked server option to true using sp_serveroption or using SQL Server Enterprise Manager. For more information, see sp_serveroption.

• 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), then 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, then SQL Server can delegate parameterized query execution to the provider. Because nested query 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 nested 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. Set the IndexAsAccessPath provider option to enable indexed access against a provider.

Additionally, when using indexes involving character columns, set the collation compatible linked server configuration option to true for the corresponding linked server. For more information, see sp_serveroption.

Note Graphically display the execution plan using SQL Server Query Analyzer 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.

See Also