Hi Everyone,
Why and when should be set 'cost threshold for parallelism'? We are on SQL 2017 Enterprise Edition.
What should be the value set for cost threshold for parallelism instead of default value 5 and why ? what benefit do we get if we set a high value and what should be the starting value?
-Bob
September 26, 2023 at 6:29 pm
Have a look at Grants page on the topic: Why You Should Change the Cost Threshold for Parallelism
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
The default value was set in 1998 on SQL Server 7. It's utterly and completely out of date. It bears no relation to the modern world. It should be a higher default, but Microsoft, being extremely conservative when it comes to their standards, won't change it. So, you should.
The core issue is simple. A query that crosses the threshold in terms of estimated cost can be run parallel. Not that it will be run parallel, just that it can. The problem is, simple queries without much complexity at all can easily pass an estimated cost of 5. What happens then is that you see queries that are only moving small amounts of data go parallel, taking up CPU, memory and I/O, but especially CPU, in order to split the data into multiple streams, process multiple streams, and then bring the streams together. This negatively impacts performance of those queries. Further, it starves resources of other queries, again, especially CPU, causing them to run slow, causing blocking, more resource consumption, more waits, etc.. You get the picture.
Follow the link that Johan gave you. It goes into more detail on this and also links to places that can help you suggest a better value.
Personally, I change this on every single server I've ever managed since about 2003. I even use this in demos of Redgate SQL Monitor as a thing to use the tool to help you solve. It's a silly default and should be changed everywhere.
"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
September 27, 2023 at 12:33 pm
The default value was set in 1998 on SQL Server 7. It's utterly and completely out of date. It bears no relation to the modern world. It should be a higher default, but Microsoft, being extremely conservative when it comes to their standards, won't change it. So, you should.
The core issue is simple. A query that crosses the threshold in terms of estimated cost can be run parallel. Not that it will be run parallel, just that it can. The problem is, simple queries without much complexity at all can easily pass an estimated cost of 5. What happens then is that you see queries that are only moving small amounts of data go parallel, taking up CPU, memory and I/O, but especially CPU, in order to split the data into multiple streams, process multiple streams, and then bring the streams together. This negatively impacts performance of those queries. Further, it starves resources of other queries, again, especially CPU, causing them to run slow, causing blocking, more resource consumption, more waits, etc.. You get the picture.
Follow the link that Johan gave you. It goes into more detail on this and also links to places that can help you suggest a better value.
Personally, I change this on every single server I've ever managed since about 2003. I even use this in demos of Redgate SQL Monitor as a thing to use the tool to help you solve. It's a silly default and should be changed everywhere.
"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
September 28, 2023 at 11:37 am
Thank you Grant & Johan.
October 3, 2023 at 5:41 am
The best value for CTFP is not a matter of how modern/capable your server might be. It's about your workload.
//www.linkedin.com/pulse/ctfp-myth-versus-method-brian-walker/
Creator of SQLFacts, a free suite of tools for SQL Server database professionals.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply