Timeline for Threadpool Wait
Current License: CC BY-SA 3.0
20 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Sep 19, 2015 at 23:34 | vote | accept | sebeid | ||
| Sep 19, 2015 at 13:16 | comment | added | usr | @DanGuzman I see where you're coming from. | |
| Sep 19, 2015 at 12:46 | comment | added | Dan Guzman | @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). | |
| Sep 19, 2015 at 9:47 | comment | added | usr | @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. | |
| Sep 19, 2015 at 2:33 | comment | added | Dan Guzman | @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. | |
| Sep 19, 2015 at 0:28 | history | tweeted | twitter.com/#!/StackDBAs/status/645031729242243072 | ||
| Sep 18, 2015 at 19:12 | answer | added | Amit Banerjee | timeline score: 4 | |
| Sep 18, 2015 at 18:59 | comment | added | sebeid | @kin sorry MAXDOP is 8 , 50 in the cost threshold for parallelism .. sorry about that | |
| Sep 18, 2015 at 18:35 | comment | added | Hannah Vernon♦ | @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. | |
| Sep 18, 2015 at 18:34 | comment | added | Kin Shah |
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.
|
|
| Sep 18, 2015 at 17:40 | comment | added | sebeid | @kin power option is balanced and MAXDOP is 50 | |
| Sep 18, 2015 at 17:32 | comment | added | Kin Shah |
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 ?
|
|
| Sep 18, 2015 at 17:31 | history | edited | sebeid | CC BY-SA 3.0 |
added 53 characters in body
|
| Sep 18, 2015 at 17:07 | comment | added | sebeid | @MaxVernon i added the graph for the CPU Utilization average 50% | |
| Sep 18, 2015 at 17:05 | history | edited | sebeid | CC BY-SA 3.0 |
added 125 characters in body
|
| Sep 18, 2015 at 17:02 | comment | added | Hannah Vernon♦ | How high is the CPU utilization on the box? | |
| Sep 18, 2015 at 16:59 | comment | added | Hannah Vernon♦ |
you can manually configure it to be higher than that. exec sp_configure 'max worker threads', 1000;
|
|
| Sep 18, 2015 at 16:58 | comment | added | sebeid | @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 | |
| Sep 18, 2015 at 16:57 | comment | added | Hannah Vernon♦ | increase the number of threads in the threadpool? | |
| Sep 18, 2015 at 16:50 | history | asked | sebeid | CC BY-SA 3.0 |