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

Improve this question
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
    Commented Sep 18, 2015 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
    Commented Sep 18, 2015 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 ♦
    Commented Sep 18, 2015 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
    Commented Sep 19, 2015 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
    Commented Sep 19, 2015 at 12:46

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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