patternsqlMinor
Threadpool Wait
Viewed 0 times
threadpoolwaitstackoverflow
Problem
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?
CPU Utilization graph for 24 hours
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
Solution
The first thing that you would want to do is to find out which queries are consuming the maximum amount of CPU. This would help you get the information on whether this is a specific type or types of queries which are spawning too many parallel threads than required. Or if it is actually a concurrent connection/session issue. If you have more concurrent executions than the number of maximum worker threads, then you definitely need more CPUs. But if it's more parallel threads or queries running longer which are keeping the worker threads occupied, then that is a SQL query tuning problem.
Context
StackExchange Database Administrators Q#115518, answer score: 4
Revisions (0)
No revisions yet.