Before you enable parallelism, configure the number of worker processes (also referred to as threads) available for Adaptive Server by setting the configuration parameter number of worker processes. Sybase recommends that you set the value for number of worker processes to one and a half times the total number required at peak load. You can calculate an approximate number using the max parallel degree configuration parameter, which indicates the total number of worker processes that can be used for any query. Depending on the number of connections to the Adaptive Server and the approximate number of queries that are run simultaneously, you can use this rule to roughly estimate the value for the number of worker processes that may be needed at any time:
[number of worker processes] = [max parallel degree] X [the number of concurrent connections wanting to run queries in parallel] X [1.5]
For example, to set the number of worker processes to 40:
sp_configure "number of worker processes", 40
Any runtime adjustment for the number of threads may have a negative effect on query performance. Adaptive Server always tries to optimize thread usage, but it may have already committed to a plan that needs increased resources, and therefore does not guarantee a linear scaledown when it runs with fewer threads.
If the query processor has insufficient worker processes, the processor tries to adjust the query plan during runtime. If a minimal number of worker processes are required but unavailable, the query aborts with this error message:
Insufficient number of worker processes to execute the parallel query. Increase the value of the configuration parameter ‘number of worker processes’
