September 15, 2016 at 12:05 pm
Hi,
I have set my SQL Server instance's Cost Threshold for Parallelism to 50 and I happen to have a query whose subtree cost is 46.6243. However, when I look at the query plan, I see a parallelism iterator and four degrees of parallelism (which is what our MAXDOP is). I've attached a couple of screenshots. It doesn't make sense to me, but then again, it's very possible there's a good reason. Any ideas?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 15, 2016 at 12:38 pm
Yes, it's the cost of the serial plan that's subject to that limit. If, as the optimiser was generating the plan, the serial plan it considered had a cost of > 50, then it would have tried a parallel plan. The cost of the resulting parallel plan isn't compared to the cost threshold, just to the serial plan, to see if it's better.
Run the query with the MAXDOP 1 hint, then look at the cost of that (the serial) plan for the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2016 at 1:18 pm
Ah, that makes perfect sense. 72 as a serial plan.....
As always, thank you. Also, I just watched your SQL Bits presentation on indexes. Very, very helpful!
Mike Scalise, PMP
https://www.michaelscalise.com
September 15, 2016 at 1:26 pm
You're welcome.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply