txsql_max_parallel_worker_threads
and txsql_parallel_degree
parameters to a value other than 0
via the console or command line. Parameters and suggested settings are as follows:
Parameter informationParameter | Variable Type | Scope | Default Value | Value Range | Description |
txsql_max_parallel_worker_threads | Integer | Global | 0 | 0–1024 | The total number of threads of the instance node that can be used for parallel query. If it is set to 0 , no parallel thread is available, indicating to disable the parallel query feature. |
txsql_parallel_degree | Integer | Global/session | 0 | 0–1024 | The maximum number of threads (default parallelism) that can be used during the parallel query of a single statement. 0 indicates to disable the parallel query feature. |
txsql_parallel_degree
indicates the maximum number of threads for the parallel query of a single statement, i.e., the default parallelism. We recommend you limit this value to half of the CPU core quantity of the instance. To ensure the stability, the parallel query feature is disabled for small clusters with fewer than four CPU cores, and you cannot adjust parallel query parameters via the console or command line.txsql_parallel_degree
will be used by default, which can be adjusted through the HINT statement. For more information, see HINT Statement Control.txsql_max_parallel_worker_threads
indicates the number of threads of the instance that can be used for parallel query, and txsql_max_parallel_worker_threads / txsql_parallel_degree
indicates the maximum number of SQL statements allowed in a parallel query.txsql_max_parallel_worker_threads
and txsql_parallel_degree
control the status of the parallel query feature. When either of them is 0
, the feature is disabled.Parameter | Variable Type | Scope | Default Value | Value Range | Description |
txsql_parallel_cost_threshold | Numeric | Global/Session | 50000 | 0–9223372036854775807 | The threshold of parallel execution cost. Only statements with an execution cost higher than this threshold will be executed parallelly. |
txsql_parallel_table_record_threshold | Integer | Global/Session | 5000 | 0–9223372036854775807 | The threshold of parallel table row quantity. Only tables with a row quantity higher than this threshold will be selected as parallel tables. |
txsql_optimizer_context_max_mem_size | Integer | Global | 8388608 | 0–9223372036854775807 | The maximum memory size that a single statement can apply for in the parallel query plan environment. |
txsql_parallel_execution_max_lob_size | Integer | Global/Session | 65536 | 128–9223372036854775807 | The memory limit of a single overflow field. |
txsql_max_parallel_worker_threads
and txsql_parallel_degree
to a value other than 0
to enable parallel query.txsql_max_parallel_worker_threads
or txsql_parallel_degree
to 0
to disable parallel query.Parameter | Variable Type | Scope | Default Value | Value Range | Description |
txsql_parallel_partition_verbose | Boolean | Global Session | OFF | ON/OFF | Supports using EXPLAIN ANALYZE to print more detailed partition information when it is set to ON. |
txsql_parallel_limit_enabled | Boolean | Global Session | ON | ON/OFF | Supports parallel query for ordered data streams (no additional sorting required) with LIMIT constraints when it is set to ON. |
txsql_parallel_rollup_pushdown_threshold | Integer | Global Session | 4 | 0-100 | Threshold for enabling the ROLL UP pushdown algorithm. Parallel computing is used when the number of GROUP BY fields exceeds this threshold. |
txsql_parallel_force_scan_enabled | Boolean | Global Session | OFF | ON/OFF | Enables or disables parallel query for full table scans and full index scans. Parallel query is supported for the two scan modes only when it is set to ON. |
txsql_parallel_force_range_enabled | Boolean | Global Session | ON | ON/OFF | Enables or disables parallel query for index range scans (RANGE/REF). Parallel query is supported for the two scan modes only when it is set to ON. |
txsql_parallel_subselect_cost_threshold | Numeric | Global Session | 50000 | 0-DBL_MAX | Parallel Execution of cost threshold. Only subqueries and derived tables with an estimated execution cost higher than this threshold can be executed in parallel. |
txsql_parallel_partition_threshold | Integer | Global Session | 10000 | 0-ULONG_MAX | NDV threshold for parallel inner tables in Nested Loop Join. Only tables with the number of distinct values in the join field higher than this threshold can be used as parallel tables. |
txsql_parallel_force_probe_enabled | Boolean | Global Session | OFF | ON/OFF | Parallel query threshold for a probe table in Hash Join. Parallel query for the probe table is considered when the hash table is larger than the join buffer size. |
txsql_parallel_derived_enabled | Boolean | Global Session | ON | ON/OFF | Supports separate parallel query for derived tables when it is set to ON. |
Was this page helpful?