When Adaptive Server is configured for parallel query processing, the optimizer evaluates each query to determine whether it is eligible for parallel execution. If it is eligible, and if the optimizer determines that a parallel query plan can deliver results faster than a serial plan, the query is divided into components that are processed simultaneously. The results are combined and delivered to the client in a shorter period of time than it would take to process the query serially as a single component.
Parallel query processing can improve the performance of the following types of queries:
select statements that scan large numbers of pages but return relatively few rows, such as:
Table scans or clustered index scans with grouped or ungrouped aggregates
Table scans or clustered index scans that scan a large number of pages, but have where clauses that return only a small percentage of the rows
select statements that include union, order by, or distinct, since these queries can populate worktables in parallel, and can make use of parallel sorting
select statements that use merge joins can use parallel processing for scanning tables and for performing the sort and merge steps
select statements where the reformatting strategy is chosen by the optimizer, since these can populate worktables in parallel, and can make use of parallel sorting
create index statements, and the alter table...add constraint clauses that create indexes, unique and primary key
The dbcc checkstorage command
Join queries can use parallel processing on one or more tables.
Commands that return large, unsorted result sets are unlikely to benefit from parallel processing due to network constraints—in most cases, results can be returned from the database faster than they can be merged and returned to the client over the network.
Commands that modify data (insert, update, and delete), and cursors do not run in parallel. The inner, nested blocks of queries containing subqueries are never executed in parallel, but the outer block can be executed in parallel.
Decision support system (DSS) queries that access huge tables and return summary information benefit the most from parallel query processing. The overhead of allocating and managing parallel queries makes parallel execution less effective for online transaction processing (OLTP) queries, which generally access fewer rows and join fewer tables. When a server is configured for parallel processing, only queries that access 20 data pages or more are considered for parallel processing, so most OLTP queries run in serial.
