Are my actual worker threads exceeding the sp_configure ‘max worker threads’ value?






select scheduler_id,current_tasks_count,
current_workers_count,active_workers_count,work_queue_count   
from sys.dm_os_schedulers  
where status = 'Visible Online'
go
select is_preemptive,state,last_wait_type,count(*) as NumWorkers
from sys.dm_os_workers
WHERE last_wait_type IN ('HADR_WORK_QUEUE','HADR_NOTIFICATION_DEQUEUE','PREEMPTIVE_HADR_LEASE_MECHANISM')  
Group by state,last_wait_type,is_preemptive   
order by count(*) desc 
go
/*
from this stored procedure check count of "maxworkers" and "WorkersCreated"
*/
Sp_server_diagnostics

select last_wait_type, count(*) as NumRequests from sys.dm_exec_requests   
group by last_wait_type   
order by count(*) desc
go
select  is_user_process,count(*) as RequestCount from sys.dm_exec_sessions s   
inner join sys.dm_exec_requests r   
on s.session_id = r.session_id   
group by is_user_process

https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/07/01/are-my-actual-worker-threads-exceeding-the-sp_configure-max-worker-threads-value/
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option?view=sql-server-2017

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Always On FailOver Events

Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 2714, state 6, severity 25.