New default value for Cost Threshold for Parallelism
The default value for Cost Threshold for Parallelism has remained the same since the release of SQL Server 7 back in the late 90s. Apparently this is based on the estimated query run times on one of the SQL Server devs team's desktop PC. Anyway hardware has moved on since the late 90s but the default setting hasn't - this leads to too many queries being considered for parallel plans with associated problems - higher compile costs, high CPU usage. Lots of people in the community writing about it - but no connect item yet - so here it is :-)
Upvotes: 38<=-=Mar 16 2017 11:48AM=-=>
Or better yet, include an option to automatically adjust it based on wait stats.<=-=Mar 17 2017 12:34AM=-=>
Or implement adaptive query plans that will adjust the parallelism dynamically based �n query execution stats<=-=Mar 17 2017 10:56AM=-=>
Thanks for the feedback. We�ll be keeping this item open for future consideration.
@Uwe Ricken, or session scoped better
SET COST_THRESHOLD_FOR_PARALLELISM 50.0;
because of different workloads can be caused by different queries over same DB.
And please query scoped too
OPTION (COST_THRESHOLD_FOR_PARALLELISM 50.0)
With precedence logic (session-level setting overrides instance-level setting, and query-level setting overrides both instance- and session-level settings).
And make it setting of the Resource Governor pool too. RG pool-level setting should have highest precedence probably.
Uwe Ricken commented
I would Love to see CTFP as a db scoped config.
Different workloads require different Settings :)