Hot Diggety!
After reading an excellent tip by Dmitry Pilugin about the new hint for forcing a parallel plan in SQL Server 2017, I figured I’d test it out on SQL Server 2016’s latest CU to see if it had been back ported yet.
Good news! It has. Obviously. That’s the title of the blog post.
Puddin’
Here are the test queries I ran.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
USE StackOverflow SELECT @@VERSION AS [Version, yo] SELECT COUNT(*) FROM dbo.Users AS u JOIN dbo.Posts AS p ON p.OwnerUserId = u.Id WHERE u.Reputation >= 100000 SELECT COUNT(*) FROM dbo.Users AS u JOIN dbo.Posts AS p ON p.OwnerUserId = u.Id WHERE u.Reputation >= 100000 OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')) SELECT COUNT(*) FROM dbo.Users AS u JOIN dbo.Posts AS p ON p.OwnerUserId = u.Id WHERE u.Reputation >= 100000 OPTION(QUERYTRACEON 8649) |
And here are the boring results!
And here are the query plans I get.
News
Good news: You don’t have to wait until SQL Server 2017 to use this.
More good news: It doesn’t require ColumnStore to work!
Bad news: You have to be on 2016 SP1 CU2 (at minimum, I’m guessing — if anyone wants to test 2016 SP1 with CU1, or 2014, feel free to see if the hint passes or errors).
More bad news: It’s still not documented. And, along with Trace Flag 8649, it seems like neither one ‘forces’ a parallel plan anymore.
Going a ways back, Trace Flag 8649 could generally get a parallel plan under, well… I’d never seen it NOT make a plan parallel (unless it couldn’t go parallel because of scalar UDFs or other ickypoo yuckies).
Testing some simpler queries with both options (the TF and the hint), some wouldn’t go parallel with either. Or both. I had to actually make SQL do some work.
Thanks for reading!
7 Comments. Leave new
Welcome back to the best country 😉
Wait… you probably wrote this a while ago.
No, sadly, I wrote this today.
I am back in the country where I can’t deadlift every car.
Are you back stateside then?
SP2?
I didn’t pay attention to my own screenshot. Fixed — thanks!
[…] this post by Erik Darling and this one by Dmitry […]