I am facing a very high wait time on the threadpool, is there a way to figure out what is the cause of this? We currently have SQL Server 2012 standard running on windows server 2008, 16 CPU. Number of connections is ~20k , 30k Request per min. Do i need more CPU or this is an application problem?
select COUNT(*) from sys.dm_os_workers SELECT * FROM sys.dm_os_wait_stats WHERE wait_type = 'threadpool' SELECT Count(* ) AS [UserSessions] FROM sys.dm_exec_sessions WHERE is_user_process = 1 SELECT Count(* ) AS [SessionsOver60] FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND last_request_end_time < Dateadd(mi,-15,Getdate())
CPU Utilization graph for 24 hours
sys.dm_exec_sessionsDMVs A prolong blocking will lead to worker thread starvation. Is the power option set to high performance on the server ?
sp_configure 'max degree of parallelism'based on your CPU info .Also, power option should be high performance not balanced.