Data-Load Optimization on Tables with Multiple Indexes

Data-load optimization on a target table that has multiple indexes efficiently loads data pages and maintains multiple indexes in parallel.

To perform maintenance on multiple indexes in parallel, you must configure Adaptive Server with resources (for example, threads), by setting:

• number of worker processes to the maximum that Adaptive Server can use at any one time for all simultaneously running queries.
• max parallel degree to the maximum for a query. This parameter determines the maximum number of threads Adaptive Server uses when processing a given query.

See Query Processing and Abstract Plans in Performance and Tuning Series.

• Viewing Parallel Update Index Queries in showplan
  set showplan uses the update index operator to display parallel update index statements.
• Viewing Queries with UpdateIndex
  show_cached_plan_in_xml displays the cached execution plan using UpdateIndex to indicate that multiple indexes are applied in parallel.