Returns worker availability information per node.
|node_id||int||NUMA node ID.|
|scheduler_count||int||Number of schedulers on this node.|
|max_worker_count||int||Maximum number of workers for parallel queries.|
|reserved_worker_count||int||Number of workers reserved by parallel queries, plus number of main workers used by all requests.|
|free_worker_count||int||Number of workers available for tasks.
Note: every incoming request consumes at least 1 worker, which is subtracted from the free worker count. It is possible that the free worker count can be a negative number on a heavily loaded server.
|used_worker_count||int||Number of workers used by parallel queries.|
On SQL Server, requires
VIEW SERVER STATE permission.
On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account or the Azure Active Directory admin account is required. On all other SQL Database service objectives, the
VIEW DATABASE STATE permission is required in the database.
A. Viewing current parallel worker availability
SELECT * FROM sys.dm_exec_query_parallel_workers;