question

sakuraime avatar image
0 Votes"
sakuraime asked TomPhillips-1744 answered

sys.dm_os_schedulers current_worker_count

sys.dm_os_schedulers has a column call current_worker_count .
May I know what's the situation that this current_worker_count will be reset .?

as I know active_workers_count < this number will be changed dynamically by SOS . thanks

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @sakuraime,

The Scheduler is responsible for creating and destroying Workers according to requirements. Workers use Scheduler directly. Each Worker can only be associated with one Scheduler. Workers cannot be transferred from one Scheduler to another.

The unit of work processed by the Worker can be a Request or a Task. For example, batch processing Request may be decomposed into multiple Tasks. When the Scheduler receives a new Request or Task request, if there is no idle Worker (Idle Worker) currently, it will start to create a new Worker according to the configuration, and the Request or Task will be bound to the Worker.

I find a blog explain this, but the blog is in Chinese, please refer to SQL Server scheduler.


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.



· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

May I know what's the situation that this current_worker_count will be reset .?

0 Votes 0 ·

Maybe you could explain your actual problem instead? Do you see this column be "reset"? What other problems are you experiencing on the system?

0 Votes 0 ·

so the max of the worker thread setting is controlling the current_worker_thread or active_worker_thread ?

0 Votes 0 ·

Hi @sakuraime,

From my understanding, the value of the current_worker_thread column is related to the request and task. When the Scheduler receives a new Request or Task request, if there is no idle Worker currently, it will start to create a new Worker. And if the worker has been idle for at least 15 minutes or more, or when SQL Server detects memory pressure, the idle worker may be destroyed. For this two situation, the value of current_worker_count will be change.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

I do not ever suggest changing the max worker threads on a system (since SQL 2000). The default value is good for 99.99% of installations. This is not something you normally need to worry about.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.