May 5, 2008 at 3:20 pm
We can set max threads. We can turn on parallism.
But I still find, some times, a process run wih single thread, some times with multiple threads. I noticed once, one process ran with over 40 threads.
I noticed that, most time, a process only runs with several threads. CPU and memory usages were low, say couple percents.
My question is:
Is it possible to enforce higher number threads to enforce a process to use more CPU time and more memory and get results in a shorter period?
Any input will be greatly appreciated.
May 6, 2008 at 6:23 am
The only thing I can think of would be to lower the cost threshold for parallelism. But then you're likely to see multiple threads in queries that will not benefit from them. I'd test like crazy in a non-production environment. We generally set our cost threshold higher than the default. I've yet to see anyone set it lower.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 6, 2008 at 8:31 am
Most of the time, SQL Server picks the number of threads pretty intelligently. It may vary the number based on how busy the CPU is, how likely the query is to benefit from parallel processing, etc. If you fiddle around with the settings on that, make sure to test like crazy, because it's more likely to slow everything down than to speed everything up.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 7, 2008 at 7:38 am
Grant Fritchey (5/6/2008)
The only thing I can think of would be to lower the cost threshold for parallelism. But then you're likely to see multiple threads in queries that will not benefit from them. I'd test like crazy in a non-production environment. We generally set our cost threshold higher than the default. I've yet to see anyone set it lower.
1) you cannot FORCE the optimizer to use a specific number of threads (other than 1 by setting maxdop = 1).
2) Grant, I did see a former Oracle admin who didn't know enough about sql server set cost threshold for parallelism to 0. That didn't work out very well for them. :w00t: I looked like I walked on water when I fixed that and a few other oopsies the company had done. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 7, 2008 at 7:48 am
Oooh! I may have to try that just to see what happens. I'll bet you looked great on that one. But here's a question, how long did it take you to figure it out?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2008 at 8:31 am
Grant Fritchey (5/7/2008)
Oooh! I may have to try that just to see what happens. I'll bet you looked great on that one. But here's a question, how long did it take you to figure it out?
About 1.3 minutes after I first connected to the server, since server configuration is the first thing I look at when doing a performance review. 🙂
Man, the CPUs were getting beat to DEATH both from the optimizer evaluating a kajillion parallel query plans it had no business messing with AND trying to execute all of the parallel plans it did decide to give a go at. 🙂
Believe it or not, that wasn't the single worst problem that client had either! :Whistling:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 7, 2008 at 2:18 pm
TheSQLGuru (5/7/2008)
...Believe it or not, that wasn't the single worst problem that client had either! :Whistling:
Actually, when I see one totally assinine thing in the server, I assume there's a legion of them. Haven't been wrong on that assumption yet.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply