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

sebeid
  • 1,381
  • 2
  • 16
  • 26
  • increase the number of threads in the threadpool? – Hannah Vernon Sep 18 '15 at 16:57
  • @MaxVernon the max number of threads is 704 for 16 CPU i am already pass this value, in the first query i am getting 783 – sebeid Sep 18 '15 at 16:58
  • you can manually configure it to be higher than that. exec sp_configure 'max worker threads', 1000; – Hannah Vernon Sep 18 '15 at 16:59
  • How high is the CPU utilization on the box? – Hannah Vernon Sep 18 '15 at 17:02
  • @MaxVernon i added the graph for the CPU Utilization average 50% – sebeid Sep 18 '15 at 17:07
  • 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
  • @kin power option is balanced and MAXDOP is 50 – sebeid Sep 18 '15 at 17:40
  • 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
  • @kin sorry MAXDOP is 8 , 50 in the cost threshold for parallelism .. sorry about that – sebeid Sep 18 '15 at 18:59
  • @sebeid, either more CPUs or more efficient queries. 500 requests/sec. is doable with 16 cores if the queries are light weight (e.g. OLTP) but not for large queries. – Dan Guzman Sep 19 '15 at 02:33
  • 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 09: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
  • @DanGuzman I see where you're coming from. – usr Sep 19 '15 at 13:16

1 Answers1

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.

Amit Banerjee
  • 430
  • 3
  • 6