The set options let you restrict the degree of parallelism on a session basis, in stored procedures, or in triggers. These options are useful for tuning experiments with parallel queries and can also be used to restrict noncritical queries to run in serial, so that worker processes remain available for other tasks.
Parameter |
Function |
|---|---|
parallel_degree |
Sets the maximum number of worker processes for a query in a session, stored procedure, or trigger. Overrides the max parallel degree configuration parameter, but must be less than or equal to the value of max parallel degree. |
scan_parallel_degree |
Sets the maximum number of worker processes for a hash-based scan during a specific session, stored procedure, or trigger. Overrides the max scan parallel degree configuration parameter and must be less than or equal to the value of max scan parallel degree. |
resource_granularity |
Overrides the global value max resource granularity and sets it to a session-specific value, which influences whether Adaptive Server uses memory-intensive operations. |
repartition_degree |
Sets the value of max repartition degree for a session. This is the maximum degree to which any intermediate data stream is be repartitioned for semantic purposes. |
If you specify a value that is too large for any of the set options, the value of the corresponding configuration parameter is used, and a message reports the value that is in effect. While set parallel_degree, set scan_parallel_degree, set repartition_degree, or set resource_granularity is in effect during a session, the plans for any stored procedures that you execute are not placed in the procedure cache. Procedures executed with these set options in effect may produce less than optimal plans.
