SQL Server Agent PowerShell Jobs Waiting for Worker Thread
My normal Data Warehouse load process runs daily in the early hours of the morning so I'm not used to sitting watching it - this morning however through lack of sleep I happened to be there and I noticed something odd: there seemed to be a queue. What seemed to be happening is that two jobs would run simultaneously whilst other jobs sat showing a status of "Waiting for Worker Thread". After digging around I found that...
... showed that the server was configured to automatically manage the max_worker_threads setting (value of 0), additionally...
SELECT max_workers_count FROM sys.dm_os_sys_info; ... showed that the value being used (based on the 32/64 bit status and number of cores) was indeed 512. A quick check of... SELECT COUNT(*) FROM sys.dm_os_threads; SELECT COUNT(*) FROM sys.dm_os_workers;
... showed that the actual running values (54 & 46 respectively) were within expectations and nowhere near the 512 thread limit so what was happening?
Well, it turned out that whilst my jobs are a mix of OS admin tasks, legacy DTS jobs and SSIS packages most of them involve some form of PowerShell script to initialise them (usually moving files around, setting permissions, etc.). The problem was down to an obscure setting that limits the max_worker_threads to just 2 for the PowerShell subsystem, the setting can be seen here:
SELECT subsystem,max_worker_threads FROM msdb.dbo.syssubsystems;
In my case I just upped the running value to 40 (the same value set for CmdExec) and restarted SQL Server Agent:
UPDATE msdb.dbo.syssubsystems SET max_worker_threads = 40 WHERE subsystem = 'PowerShell';
I'm yet to spot whether this has any other long term effect but things seem to be behaving fine, I will say that as part of my research I found other people saying that this setting did not persist through a restart on certain versions - I'm running 2008 R2 and that appears to be fine but it's worth double-checking if you try this.