The Data Integration Service optimizes SQL data services based on the optimizer level that you configure. Configure the optimizer level when you want the SQL data service to use an optimizer level other than normal. By default, each SQL data service uses the normal optimizer level.
To understand how the optimizer level creates an optimized query for an SQL data service, view the query plan for an SQL Data Service. When you view the query plan, the Developer tool displays a graphical representation of the optimized query based on the optimizer level and a graphical representation of the original query.
You can configure the following optimizer levels:
0 (None)
The Data Integration Service does not apply any optimization.
1 (Minimal)
The Data Integration Service applies the early projection optimization method.
2 (Normal)
The Data Integration Service applies the early projection, early selection, branch pruning, push-into, global predicate optimization, and predicate optimization methods.
3 (Full)
The Data Integration Service applies the cost-based, early projection, early selection, branch pruning, predicate, push-into, semi-join, and dataship-join optimization methods.
Default is 2 (Normal).
You can use one or more of the following methods to configure the optimizer level for an SQL data service:
Configure the optimizer level for data preview of SQL data services.
Configure the optimization level for deployed SQL data services.
Configure the optimizer level in the connection string of queries that you run against a deployed SQL data services.