question

Tim avatar image
Tim asked

cost threshold for parallelism

The default value for cost threshold for parallelism is 5. I have read numerous blog post and attended different SQL Server related training sessions where really smart people have all said this value is 'way to low' on modern hardware. That the default value was carried over from the SQL Server 7 days. It is recommended by many that this value be increased. Asking around some have said start with 20, answers by others have ranged from 20 - 250. All seem to be in agreement that 5 is to low. I am curious if others have found a sweet spot as the standard new default for their organization and how you came to chose that number. Was it just a swag, based on a blog you read or a number based on analysis you conducted.
tuningparallelismcost threshold
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
The sweetspot I found was at 35. It seemed to be big enough without being too big for most of the OLTP systems I've had under management. Sometimes, depending on the system, I might go up from there, but so far, I've never gone down.
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Grant. Great to know that you have never had a negative impact to have to revert back.
0 Likes 0 ·
Never. Once or twice I've had to bump it up even higher, but I've never gone down yet.
0 Likes 0 ·
Estimate of how many instances you have done this on.
0 Likes 0 ·
+1 35 is what I've done on most systems where I've changed from default.
0 Likes 0 ·
400-500 instances between dev, test and production.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.