Have you ever come across a situation where you have some Powershell task within a SQL Agent job and it appears to be taking WAY longer than you feel like it should? If you have, you are not alone and if your situation is like mine, this is by design!
Now the question becomes, what can you do? The answer is pretty simple, you just have to tell SQL to allow Powershell to use more than 2 threads at a time. The number 2 comes from the msdb.dbo.syssubsystems table, which you can view all of the settings by running the following:
SELECT * FROM msdb.dbo.syssubsystems
And as it turns out, you can very easily change this by running SQL similar to this (pick your own number…pick it wisely):
Update msdb.dbo.syssubsystems set max_worker_threads = 10 where subsystem = 'Powershell'
And after restarting the SQL Agent, you can have up to that many jobs with Powershell steps all running simultaneously.
I have not personally come across any repercussions of changing this setting, but if you do please fill me in. I would love to hear those stories before I have to live through them myself,