Waiting for worker thread: Configuring SQL Server subsystem values

SQL Server waiting for worker thread - JJ Prinsloo

Waiting for worker thread. The message every DBA loves to see in their job activity monitor. Yeah right! Although seldom noticed, these do tend to pop up from time to time. Especially when using PowerShell steps within your jobs. I will demonstrate the issue using PowerShell jobs.

What does this message mean and why do you see it in the job activity monitor? It basically means that the job is sitting in a queue, waiting to execute. Microsoft implemented limitations for the various subsystems which SQL Server uses. This was done for a good reason. It would be a mess if queries were waiting to execute due to a bunch of PowerShell jobs all running at the same time and hogging the worker threads. As we know, however, default configuration is not always the best configuration and this depends on your specific needs. I tend to run all my jobs and ETL processes on a separate server set up specifically for that purpose. This is a standard practice for me. On this server, no production queries are run and I can increase the worker threads for my jobs whether they are SSIS ETL processes, PowerShell backup jobs or normal database maintenance jobs. In this event, I can alter the subsystem limitations to fit my requirements.

Reproducing the problem

Under the default subsystem configuration you can create 3 PowerShell jobs to see how this issue occurs. Each job should have a PowerShell step with the following code:

Start-Sleep -s 600

It’s a simple line which tells the PowerShell subsystem to wait for 10 minutes.

After creating the jobs, you can execute them one after the other from the SQL Server Agent. In the activity windows, you see that all three jobs are running. They should look like the screenshots below

If you open the job activity monitor, you will notice that PowerShell Job 1 and PowerShell Job 2 is executing as expected. Take note the Status message of PowerShell Job 3. As predicted, waiting for worker thread. This is what the message will look like in a production environment.

As you can imagine, this could have serious ramifications if you have jobs that kick of close to each other where your processes will be delayed heavily if these limitations of simultaneous execution is reached.

The obvious question: Why?

As mentioned earlier, this is due to the subsystem limitations implemented by default. You can check the subsystem limitations on your server by running the following query:

SELECT [subsystem_id]
,[subsystem]
,[max_worker_threads]
FROM [msdb].[dbo].[syssubsystems]

If you check the values of the PowerShell subsystem, you will see the limitation is set to 2 by default. This ties up with the previous experiment where PowerShell Job 1 and PowerShell Job 2 were both executing and PowerShell Job 3 was sitting and waiting in the queue (or waiting for a worker thread if you will) to execute. This subsystem limits you to run only 2 PowerShell processes simultaneously.

How do you change the limitation?

To change the subsystem limitation, you can update the table in which the subsystem limitations reside directly and restart the SQL Server Agent. That’s all there is to it. As an example, you can use the following statement to update the subsystem limitations for PowerShell to 3.

UPDATE msdb.dbo.syssubsystems
SET max_worker_threads = 3 --Increase this value to increase the subsystem limitation
WHERE subsystem = N'PowerShell'

After performing this change, you will be able to run all three of the test PowerShell jobs, which was created above, simultaneously and none of them will be waiting for a worker thread.

As with any SQL server change, I will again mention, be careful and take note in what you are doing. You may have to do some proper planning when changing these values and this should not be done just because it “may” solve your problem. Always make sure and make sure you update the right values. SQL does not ask questions. It assumes you know what you are doing.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *