Home Dashboard Directory Help
Search

Powershell Max work threads hardcoded to 2 by Tinkering 101


Status: 

Closed


2
0
Sign in
to vote
Type: Bug
ID: 802415
Opened: 9/26/2013 4:01:00 PM
Access Restriction: Public
0
Workaround(s)
view
2
User(s) can reproduce this bug

Description

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
Details
Sign in to post a comment.
Posted by Pedro Azevedo Lopes 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
Hi,
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)
Sign in to post a workaround.