Cost Threshold For Parallelism - Your opinion

Question

Cost Threshold For Parallelism - Your opinion

NicHopper

SSCrazy Eights

Points: 9090
More actions
September 11, 2018 at 7:49 am

#362682

Hi,
I'm not going to ask what its for or anything, I understand it and its whole history back to Nick's PC.
What I am going to ask is how you go about changing it, but I don't mean actually changing it, I know how to do that (sp_configure), what I mean is what do you set it to.
From research it seems to be a bit of trial and error and I get that but I wondered where's a good starting point, I'd heard 50 but is that to high or perhaps to low for todays hardware.
Then once changed is there any set of metrics best to monitor? Anything that may stand out above others or should I just be comparing to my machine baseline?
Any thoughts on this?
Thanks,
Nic
Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

coolchaitu SSChampion Points: 10334 More actions · Answer 1

NicHopper - Tuesday, September 11, 2018 7:49 AM
Hi,
I'm not going to ask what its for or anything, I understand it and its whole history back to Nick's PC.
What I am going to ask is how you go about changing it, but I don't mean actually changing it, I know how to do that (sp_configure), what I mean is what do you set it to.
From research it seems to be a bit of trial and error and I get that but I wondered where's a good starting point, I'd heard 50 but is that to high or perhaps to low for todays hardware.
Then once changed is there any set of metrics best to monitor? Anything that may stand out above others or should I just be comparing to my machine baseline?
Any thoughts on this?
Thanks,
Nic

Bump it to 25

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 2

coolchaitu - Tuesday, September 11, 2018 7:59 AM
NicHopper - Tuesday, September 11, 2018 7:49 AM
Hi,
I'm not going to ask what its for or anything, I understand it and its whole history back to Nick's PC.
What I am going to ask is how you go about changing it, but I don't mean actually changing it, I know how to do that (sp_configure), what I mean is what do you set it to.
From research it seems to be a bit of trial and error and I get that but I wondered where's a good starting point, I'd heard 50 but is that to high or perhaps to low for todays hardware.
Then once changed is there any set of metrics best to monitor? Anything that may stand out above others or should I just be comparing to my machine baseline?
Any thoughts on this?
Thanks,
Nic
Bump it to 25

Hi,
Thanks for the reply. Can I ask how you validated that 25 was right for your kit, did you see an improvement in any particular stats or alike?
Nic

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

Great question - I think this is a good topic that isn't talked about enough.

Per Books Online:

"While the default value of 5 is retained for backwards compatibility, it is likely that a higher value is appropriate for current systems. Many SQL Server professionals suggest a value of 25 or 50 as a starting point, and to perform application testing with higher and lower values to optimize application performance."

... and that's it. Not much guidance there.

What I do (and I'm not saying this is the beat way to approach this but it's worked for me) is regularlary check our slowest queries. We capture query data incuding execution plans. If they are slow because they're badly written I fix them. If they are well written according to our best practices and are just slow because they do a lot of complex stuff I check to see if they are getting serial or parallel plans. I then run them with a serial plan if they ran parallel (using MAXDOP 1) or with a parallel plan if they ran serial (using TRACEFLAG 8649 or Adam machanic's Make_parallel function.)

When I see queries that get serial plans when a parallel plan was better and possible but not chosen due to maxdop settings - that's an indication that that it's too low. The opposite is also true.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 4

Thanks both for the responses.

I'm going to use 25 as a start and then review slowest query/application performance over the next few weeks and then follow the process you defined Alan, makes sense to me.

Thanks,

Nic

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

CC-597066 SSCommitted Points: 1637 More actions · Answer 5

Alan.B - Tuesday, September 11, 2018 8:57 AM
When I see queries that get serial plans when a parallel plan was better and possible but not chosen due to maxdop settings - that's an indication that that it's too low. The opposite is also true.

Didn't you mean 'Cost Threshold For Parallelism' and 'too high'?

Alan Burstein SSC Guru Points: 61141 More actions · Answer 6

CC-597066 - Thursday, September 13, 2018 8:02 PM
Alan.B - Tuesday, September 11, 2018 8:57 AM
When I see queries that get serial plans when a parallel plan was better and possible but not chosen due to maxdop settings - that's an indication that that it's too low. The opposite is also true.
Didn't you mean 'Cost Threshold For Parallelism' and 'too high'?

Gosh. Yes, good catch - thanks!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 7

coolchaitu - Tuesday, September 11, 2018 7:59 AM
NicHopper - Tuesday, September 11, 2018 7:49 AM
Hi,
I'm not going to ask what its for or anything, I understand it and its whole history back to Nick's PC.
What I am going to ask is how you go about changing it, but I don't mean actually changing it, I know how to do that (sp_configure), what I mean is what do you set it to.
From research it seems to be a bit of trial and error and I get that but I wondered where's a good starting point, I'd heard 50 but is that to high or perhaps to low for todays hardware.
Then once changed is there any set of metrics best to monitor? Anything that may stand out above others or should I just be comparing to my machine baseline?
Any thoughts on this?
Thanks,
Nic
Bump it to 25

Based on what evidence??? :blink:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Travis Halsell SSC Eights! Points: 896 More actions · Answer 8

It obviously depends on your workload. How many queries do you want going parallel? 50 / 50?

Here is a good blog post that provides a query that gives you some stats on your query costs. Mean, median and mode. (this query takes several minutes to run)

http://sqlknowitall.com/determining-a-setting-for-cost-threshold-for-parallelism/