Parallel Query Optimization

This chapter describes the basic strategies that Adaptive Server uses to perform parallel queries and explains how the optimizer applies those strategies to different queries. Parallel query optimization is an automatic process, and the optimized query plans created by Adaptive Server generally yield the best response time for a particular query.

However, knowing the internal workings of a parallel query can help you understand why queries are sometimes executed in serial, or with fewer worker processes than you expect. Knowing why these events occur can help you make changes elsewhere in your system to ensure that certain queries are executed in parallel and with the desired number of processes.

What is parallel query optimization?

Optimizing for response time versus total work

When is optimization performed?

Overhead costs

Factors that are not considered

Parallel access methods

Parallel partition scan

Parallel clustered index partition scan (allpages-locked tables)

Parallel hash-based table scan

Parallel hash-based index scan

Parallel range-based scans

Additional parallel strategies

Summary of parallel access methods

Selecting parallel access methods

Degree of parallelism for parallel queries

Upper limit

Optimized degree

Nested-loop joins

Examples

Runtime adjustments to worker processes

Parallel query examples

Single-table scans

Multitable joins

Subqueries

Queries that require worktables

union queries

Queries with aggregates

select into statements

Runtime adjustment of worker processes

How Adaptive Server adjusts a query plan

Evaluating the effect of runtime adjustments

Recognizing and managing runtime adjustments

Reducing the likelihood of runtime adjustments

Checking runtime adjustments with sp_sysmon

Diagnosing parallel performance problems

Query does not run in parallel

Parallel performance is not as good as expected

Calling technical support for diagnosis

Resource limits for parallel queries


	Expand Search