Adaptive Server traditionally classifies Transact-SQL statements into two groups:
Optimizable. For example, this query is optimizable because it has many relations (tables):
select * from t1, t2, t3, t4 where t1.c1 = t2.c1 and . . . order by t3.c4
The query processor requires the join order, type of join, search arguments, and ordering to be optimized.
Nonoptimizable. Utility commands like update statistics and dbcc are not optimized.
In Adaptive Server version 15.0 and later the optimizer and most of the execution engine was rewritten. The utility commands currently generate nearly identical showplan output compared with earlier versions. However, versions 15.0 and later generate new showplan output for optimizable statements.
Some of the new features of the query plans that showplan must display include:
Plan elements – query plans can be composed from over thirty different operators.
Plan shape – query plans are upside-down trees of operators. In general, more operators in a query plan result in more combinations of possible tree shapes. Query plans in version 15.0 and later can be more complex than those found in earlier versions. Nested indentation is provided to assist in visualizing the tree shape of these query plans.
Subplans that are executed in parallel.
