SQL Server Agent Job waiting for a worker thread

Recently one of my teammates experienced a quite interesting issue. He was deploying new PowerShell maintenance SQL Server Agent Jobs on a new SQL Server instance. During the final test run, he noticed that some of the jobs were executing fine and one of them was waiting for a worker thread. In this blog post, I will describe what do to if you encounter a similar issue.

PowerShell Job steps

Due to the various circumstances, we had to rewrite some of our maintenance jobs from T-SQL to PowerShell. Created scripts are now executed by Agent Jobs with PowerShell steps. Every job tested separately works fine without any issues. However, when jobs are started simultaneously, only two of them are running, the rest is waiting for worker threads. Usually, you don’t see such things with other job step’s types.

Steps to reproduce

If your SQL Server Agent runs with default configuration values you should be able to easily reproduce this issue.

First of all, you need to create at least 3 SQL Server Agent jobs with PowerShell steps. These steps cannot be the quick ones because you won’t notice this problematic behavior. For testing purpose, we will use the PowerShell Start-Sleep command that waits for a given time. In our case, 60 seconds should be enough.

We will use a temporary stored procedure to make jobs creation easier  (and to make sure we won’t leave any unnecessary objects after we finish our exercise). If you’re not familiar with temporary stored procedures I invite you to read this post: Temporary Stored Procedures – good, old and little-known feature.

USE [msdb]
GO

CREATE PROCEDURE #temp_CreateJob @jobName sysname
AS

    DECLARE @jobId BINARY(16)
    EXEC  msdb.dbo.sp_add_job @job_name=@jobName, 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=2, 
        @notify_level_page=2, 
        @delete_level=0, 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT;

    EXEC msdb.dbo.sp_add_jobserver @job_name=@jobName, @server_name = @@SERVERNAME;


    EXEC msdb.dbo.sp_add_jobstep @job_name=@jobName, @step_name=N'example', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_fail_action=2, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'PowerShell', 
        @command=N'Start-Sleep -Seconds 60', 
        @database_name=N'master', 
        @flags=0;

    EXEC msdb.dbo.sp_update_job @job_name=@jobName, 
        @enabled=1, 
        @start_step_id=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=2, 
        @notify_level_page=2, 
        @delete_level=0, 
        @description=N'',  
        @notify_email_operator_name=N'', 
        @notify_page_operator_name=N'';
GO

To create three new jobs run below query.

EXEC #temp_CreateJob @jobName = N'test1'
EXEC #temp_CreateJob @jobName = N'test2'
EXEC #temp_CreateJob @jobName = N'test3'
GO

SELECT * FROM msdb.dbo.sysjobs WHERE [name] LIKE 'test_'
GO

Three new SQL Server jobs should now appear in SSMS Object Explorer.

Let’s run all at once now.

EXEC msdb.dbo.sp_start_job @job_name=N'test1';
EXEC msdb.dbo.sp_start_job @job_name=N'test2';
EXEC msdb.dbo.sp_start_job @job_name=N'test3';
GO

Now you have 60 seconds to check the current execution status for these jobs. To do that you can use the msdb.dbo.sp_help_job procedure or open SSMS Job Activity Monitor.

In the Job Activity Monitor you can very easily spot that two jobs have the ‘Executing’ status and the third one is waiting for a worker thread.

If you use msdb.dbo.sp_help_job procedure, then you will find execution status in current_execution_status column.

SQL Server Agent Subsystems configuration

To check why only two PowerShell jobs are running you need to take a look on SQL Server Agent Subsystems configuration. Use the below query to do that.

SELECT  s.subsystem_id, 
        s.subsystem, 
        m.[text] AS [description], 
        s.max_worker_threads
FROM msdb.dbo.syssubsystems s
INNER JOIN msdb.sys.messages m 
    ON  s.description_id = m.message_id 
        AND m.language_id = 1033 --English

Results:

That explains everything. It turns out that by default it is configured to have only two worker threads for PowerShell jobs’ steps.

The Solution

To fix this issue and allow SQL Server Agent to run more PowerShell steps at the same time you need to change this configuration. You can do it very easily with a simple UPDATE statement. I will change the maximum worker threads threshold to 5. That should be enough for my needs. You need to choose the threshold that will work for you.

UPDATE msdb.dbo.syssubsystems
SET max_worker_threads = 5
WHERE subsystem_id = 12

To bring this change to life you have to restart the SQL Server Agent service. After that, you can test if this works.

EXEC msdb.dbo.sp_start_job @job_name=N'test1';
EXEC msdb.dbo.sp_start_job @job_name=N'test2';
EXEC msdb.dbo.sp_start_job @job_name=N'test3';
GO

Last look at SSMS Job Activity Monitor.

Cleanup

USE [msdb]
GO

-- DROP Temporary Store Procedure
DROP PROCEDURE #temp_CreateJob;
GO

-- DROP SQL Server Agent Jobs
EXEC msdb.dbo.sp_delete_job  @job_name=N'test1';
EXEC msdb.dbo.sp_delete_job  @job_name=N'test2';
EXEC msdb.dbo.sp_delete_job  @job_name=N'test3';
GO

-- Restore default value
UPDATE msdb.dbo.syssubsystems
SET max_worker_threads = 2
WHERE subsystem_id = 12
GO

Thanks for reading!

-Marek

Share it: