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!
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?
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
Also as a side note - changing this setting will flush the plan cache, so you will be getting a new plan after each change.
