“Total Parallel Queries” reports the total number of queries eligible to be run in parallel. The optimizer determines the best plan, deciding whether a query should be run serially or in parallel and how many worker processes should be used for parallel queries.
“WP Adjustments Made” reports how many times the number of worker processes recommended by the optimizer had to be adjusted at runtime. Two possible causes are reported:
“Due to WP Limit” indicates the number of times the number of worker processes for a cached query plan was adjusted due to a session-level limit set with set parallel_degree or set scan_parallel_degree.
If “Due to WP Limit” is a nonzero value, look for applications that set session-level limits.
“Due to No WPs” indicates the number of requests for which the number of worker processes was reduced due to lack of available worker processes. These queries may run in serial, or they may run in parallel with fewer worker processes than recommended by the optimizer. It could mean that queries are running with poorly-optimized plans.
If “Due to No WPs” is a nonzero value, and the sample was taken at a time of typical load on your system, you may want to increase the number of worker processes configuration parameter or set session-level limits for some queries.
Running sp_showplan on the fid (family ID) of a login using an adjusted plan shows only the cached plan, not the adjusted plan.
If the login is running an adjusted plan, sp_who shows a different number of worker processes for the fid than the number indicated by sp_showplan results.
