| Author |
Topic |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2004-03-18 : 09:19:33
|
| I have a proc that runs in my BETA environment (single processor) in about 50 minutes. The same proc in my PROD environment (4 way) runs about 3.5 hours.There is a query in the proc that gets exectured thousands of times. This query in PROD has a 34.5 subtree cost and takes about 16ms to run. The same in PROD has a 21.2 subtree cost, but takes about 900ms to run. The difference in execution plans between BETA and PROD is that PROD uses Parallelism.If I set the Cost Threshold for Parallelism, on my PROD machine, to say 100 (from the default 5), my PROD execution plan becomes exactly like BETA and the proc executes quickly.That sucks, don't it? Does anyone have any experience with poor run times due to multiple processors? Are there any tips and tricks about query writting to avoid this situation?Jay White{0} |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-03-18 : 20:42:21
|
| We had problems with excessive parallelism at work and ending up setting our 4 CPU server to max degree of 2. It immediately brought the CPUs down to 20-30% utilization from an AVERAGE of 75%. Sometimes they just get so hung up on working in parallel that they can't do anything else.If you don't want to change the server setting, you can use the MAXDOP hint in your queries. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-03-19 : 06:52:17
|
| Damn ... MAXDOP ... didn't see that. Thanks Rob.I was actually able re-write query to get a much better subtree cost and avoid the parallelism.Jay White{0} |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-03-19 : 12:23:03
|
quote:
Originally posted by robvolk
We had problems with excessive parallelism at work and ending up setting our 4 CPU server to max degree of 2. It immediately brought the CPUs down to 20-30% utilization from an AVERAGE of 75%. Sometimes they just get so hung up on working in parallel that they can't do anything else.If you don't want to change the server setting, you can use the MAXDOP hint in your queries.
That's amazing....When did parallelism arrive to sql server?Are they still just getting the kincks out?I would have though the more parallelism the btter...That's what I shoot for in DB2 OS/390....Works like a champ.....Good to know though....what about Yukon?Brett8-) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-03-19 : 21:32:34
|
| I think it was available with 6.0 or 6.5, but it wasn't as good as it became with 7.0. Books Online, Inside SQL Server, and Ken Henderson's latestThe Guru's Guide To SQL Server Architecture and Internalshave a great deal of information on how SQL Server process work in parallel. Yukon will enhance the abilities of parallel execution, but ultimately I think there's a limit on how much benefit it can provide, and there are instances where it can make things worse. That's why there are options to limit or deactivate parallel execution. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-08-10 : 03:42:08
|
| Just to confirm, in SQL Server 2005 with 4 processors (+hyper threading), yesterday we changed max degree of parallelism from 0 to 1 and experienced a noticeable performance boost.I would say more than noticeable. It's been MASSIVE!!!Our systems are faster now than they have been for 18 months! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-10 : 08:31:18
|
select ...from ...where ...OPTION (MAXDOP 1)
E 12°55'05.25"N 56°04'39.16" |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-08-10 : 08:35:52
|
| or just set it for the server and you dont have to go change hundreds of queries... |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-08-10 : 09:28:33
|
| I wonder if you could have achieved the same results by disabling hyper-threading?===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-08-12 : 04:56:45
|
| I wouldn't have thought so. I have more faith in hyper-threading making the right decisions.And if you switch hyper-threading off, you're not just taking something away from SQL Server, you're taking something away from Windows. So that can't be as good an idea.Seriously, if you haven't switched unlimited Parallelism off (so to a number lower than the amount of physical processors) in your organization, I highly suggest that you all at least give it a try. You stand to gain a lot. Don't be scared! |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2007-08-14 : 00:39:38
|
quote:
There is a query in the proc that gets exectured thousands of times.
Setting MAXDOP to 1 is a nice patch... but not a fix. The reason you need to do that is because you've written RBAR (pronounced "ree-bar" and is a "Modensim" for "Row By Agonizing Row") instead of set-based code. Set based code would be able to take advantage of a multi-processor environment. RBAR probably won't.--Jeff Moden |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-08-14 : 05:51:22
|
| Jeff, do you realise that you answered a question that was presented 3 years ago?We've sort of moved onto a different subject. I'd be surprised if Jay is still following this. :-) |
|
|
|
|
Cost Threshold for Parallelism