Powershell Max work threads hardcoded to 2 - by Tinkering 101

Status : 


Sign in
to vote
ID 802415 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 9/26/2013 4:01:00 PM
Access Restriction Public


The Powershell max worker threads value in subsystems is hardcoded to 2.
The value can be changed using:

update msdb.dbo.syssubsystems
Set max_worker_threads=20
Where subsystem='PowerShell'

But restarting SQL Agent reverts this back to 2.

Please update restart of SQL Agent to not overwrite this value as per 2008 R2 behaviour
Sign in to post a comment.
Posted by sravani saluru on 6/6/2016 at 7:00 AM
this issue got fixed in the below KB article , if anyone still encountering the problem , apply the fix

FIX: Installing SQL Server 2008 Service Pack 1 may reset the "max_worker_threads" column value for a SQL Server Agent subsystem

Cumulative Update 2 for SQL Server 2014 SP1

Cumulative Update 7 for SQL Server 2012 SP2

Cumulative Update 8 for SQL Server 2014

Posted by Randy in Marin on 12/9/2014 at 5:04 PM
I'm not sure I follow the logic of the argument against configuration of the number of subsystem threads. I built a SQL system dedicated to running a special set of CmdExec jobs. A special "master" job automatically creates and controls 100s of jobs that run continuously. The exe run by these jobs is idle most of the time and will do some small bulk inserts once in while. I was hoping to test the load on a single CPU system. The load is very light even with all those threads. I was a bit surprised when I deployed to production and my design failed because of the thread limit. I now have to look at a more complex alternative that might need to be manually configured. I really don't want to use Tidal for this. Perhaps a PSH 4 CmdExec job that control windows task scheduler...so much more work than I needed.    
Posted by Pedro Lopes (PL) on 7/3/2014 at 10:59 AM
"Powershell Max work threads hardcoded to 2", arguing that the Powershell subsystem is hard coded because it might reflect on available resources makes little sense when cmdexec has a scaled value, for example. This can also lead to the same potential issues. Either have a scaled value as cmdexec, or allow change as was allowed back in SQL 2008R2.
Posted by Tinkering 101 on 10/7/2013 at 1:31 AM
Yes, changed my username.

OK, I think the confusion with this stems from that in 2008 R2 this works and this value could be changed to something that was appropriate to the system (there was an explicit release done to make sure this works). See http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f622caaa-1b06-4d6f-aa0f-356f5300f7c2/setting-powershell-max-worker-threads-in-sql-2012?forum=sqlsetupandupgrade

In 2012 this value can't be changed, but at the same time it doesn't appear to scale up to reflect the resources available to SQL Server.
So as far as I can tell (and as other people are reporting on the forums), no matter how many cores are available there will only ever be two threads for running powershell.
So really the question is why is powershell hardcoded and the other values aren't? And why wasn't it in 2008 R2 but is in 2012?
Posted by Microsoft on 10/4/2013 at 1:20 PM
Hello Tinkering. (Did your username recently change?)
Apologies for the lack of clarity in my reply. sp_verify_subsystems scales the number of threads based on available cores for DTS & ANALYSISCOMMAND (at 100 * @processor_count), but PowerShell is set to a hard 2. Changing this value would place you in an unsupported scenario, and we cannot recommend it, as it represents an untested setting. (A value of 100 * @processor_count would also be pretty high. 10 * would probably be plenty, but we still can't suggest such a thing.)
-Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)
Posted by Tinkering 101 on 10/4/2013 at 1:23 AM
Hi Walter,

Thanks for the response.

This doesn't appear to be working though. I have numerous agent jobs running simultaneously with 16 cores available, yet only two powershell jobs run at once resulting in queuing.
Am I missing something?
Posted by Microsoft on 10/3/2013 at 12:03 PM
Hello Gary.
The behavior in question is by design: this thread number is scaled based on the available number of processors. Hardcoding is necessary for the ability to restore to other locations.
-Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)