sys.dm_exec_query_parallel_workers (Transact-SQL)

APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Returns worker availability information per node.

Name Data type Description
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.

Permissions

On SQL Server, requires VIEW SERVER STATE permission.
On SQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database. On SQL Database Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account.

Examples

A. Viewing current parallel worker availability

SELECT * FROM sys.dm_exec_query_parallel_workers;  

See Also

Dynamic Management Views and Functions (Transact-SQL)
Execution Related Dynamic Management Views and Functions (Transact-SQL)
sys.dm_os_workers (Transact-SQL)