sp_sysmon shows how many times a request for worker processes was denied due to a lack of worker processes and how many times the number of worker processes recommended for a query was adjusted to a smaller number. The following sections of the report provide information:
“Worker process management” on page 220 of the Performance and Tuning: Monitoring and Analyzing for Performance describes the output for the number of worker process requests that were requested and denied and the success and failure of memory requests for worker processes.
“Parallel query management” on page 222 of the Performance and Tuning: Monitoring and Analyzing for Performance describes the sp_sysmon output that reports on the number of runtime adjustments and locks for parallel queries.
If insufficient worker processes in the pool seems to be the problem, compare the number of worker processes used to the number of worker processes configured. If the maximum number of worker processes used is equal to the configured value for number of worker processes, and the percentage of worker process requests denied is greater than 80 percent, increase the value for number of worker processes and re-run sp_sysmon. If the maximum number of worker processes used is less than the configured value for number of worker processes, and the percentage of worker thread requests denied is 0 percent, decreases the value for number of worker processes to free memory resources.
