5

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())

enter image description here

CPU Utilization graph for 24 hours

enter image description here

14
  • 3
    I would not just blindly go and increase the max worker threads. Since you have 16 CPUs, you will have 704 worker thread available. Suggest you to look into tuning max dop setting away from default. Analyze blocking using - sys.dm_exec_requests and sys.dm_exec_sessions DMVs A prolong blocking will lead to worker thread starvation. Is the power option set to high performance on the server ?
    – Kin Shah
    Sep 18 '15 at 17:32
  • 2
    Maxdop can't be 50 ! check using sp_configure 'max degree of parallelism' based on your CPU info .Also, power option should be high performance not balanced.
    – Kin Shah
    Sep 18 '15 at 18:34
  • 1
    @kin agreed, clearly power option should be "gimme all you got"! Clearly, with 20,000+ user connections, 700 threads is just not going to cut it.
    – Hannah Vernon
    Sep 18 '15 at 18:35
  • 1
    @DanGuzman depends on how much load the queries are generating. If it's just a select by primary key I'd expect thousands per second per CPU core. Impossible to make general statements here.
    – usr
    Sep 19 '15 at 9:47
  • 1
    @usr, that's the same point I was trying to make with my comment; basically, "it depends". I have seen 16 core servers easily sustain thousands of requests per second with trivial queries that return no more than a few rows and no parallelism. The fact that worker threads get exhausted here suggests more demanding queries and/or larger result sets. Remediation may include a combination of query tuning, app changes to consume result sets more quickly, or additional hardware. Not enough information here to suggest the best solution(s).
    – Dan Guzman
    Sep 19 '15 at 12:46
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.