There is a perfmon counter "Queued jobs" in perfmon object "SQLAgent:Jobs".
How to create a job queue that will reflect in this perfmon counter?
Can I get this queued job info from somewhere else like DMV?
Sql-server – How to create SQL Server Agent Queued jobs
perfmonsql server
Related Question
- Sql-server – How to measure Key Lookups per Second on SQL Server
- Sql-server – How to extract the perfmon counter “Memory: Pages/Sec” using t-sql
- SQL Server – Using Resource Governor with SQL Agent Jobs
- Sql-server – Why “Data File(s) Used Size (KB)” PerfMon counter doesn’t exist
- SQL Server – How to Limit Simultaneous Jobs in SQL Server Agent
- SQL Server Background Job Queue – How to Create a Background Job
- SQL Server Performance – Log Send Queue Size and Redo Queue Size in Availability Groups
- SQL Server – Redo Rate on AG Dashboard vs Perfmon Counter
Best Answer
The SQL Server Agent has a maximum worker thread setting that limits the total number of threads that can run jobs at any given time. When you hit this limit, you will get a warning message that the thread limit has been hit and the job queued for execution. When this occurs, that counter increments to record the fact that there is a queued job.
This is also true for job steps, which have their own worker thread limit for each subsystem and a performance counter under SQLAgent:JobSteps to record the queued steps.
This queueing mechanism is not something you can manually initiate or control, it is an in-built queueing mechanism to manage the worker threads used by SQL Agent.
See here for more info. The max worker thread count is # of CPUS*100. Run sp_enum_sqlagent_subsystems to see the max worker threads for individual subsystems.
You can test this out yourself, though it is easier to test the subsystem queue behaviour than the SQL Agent job queue behaviour.
Create a number (6 or more, depends on the limit for the PowerShell subsystem) of one-step SQL Agent jobs to run a PowerShell command (
Start-Sleep -Seconds 60) and execute them simultaneously. Job Activity Monitor will show something similar to belowAnd if you're monitoring the performance counter SQLAgent:JobSteps - Queued Steps, you will see it increase: