question

borispinsky avatar image
borispinsky asked

Does cost threshold for parallelism parameter could influence on serial plans without changing it to Parallel?

Today I had something strange: "cost threshold for parallelism: option to specify the threshold at which Microsoft SQL Server creates and runs parallel plans for queries" https://technet.microsoft.com/en-us/library/ms188603(v=sql.105).aspx I had some "heavy" query that running without Parallelism with duration of 18 seconds. After I changed "Cost Threshold for Parallelism" from 5 to 50 the query duration reduced from 18 to 1 second with different plan but it still not using Parallelism! When I changed it back to 5 the duration back to 18 sec!
performanceperformance-tuningparallelismoptimizer
4 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.

How much of that time was query execution vs how much was query compilation? See, for examples of how to identify - https://www.sqlskills.com/blogs/jonathan/identifying-high-compile-time-statements-from-the-plan-cache/ Were the query plans identical?
0 Likes 0 ·
Whats the cost of the serial plan? If the cost is within the threshold then the optimizer will 'consider' parallel operations - even though it might decide not to use one. As @ThomasRushton mentions - this could be obvious in the compilation times
0 Likes 0 ·
Also as a side note - changing this setting will flush the plan cache, so you will be getting a new plan after each change.
0 Likes 0 ·
Thanks guys! The plans were different...
0 Likes 0 ·

0 Answers

· Write an Answer

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.