Breaking: SQL Server 2016 SP1 CU2 Supports ENABLE_PARALLEL_PLAN_PREFERENCE Hint

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.

And here are the boring results!

Like a version

And here are the query plans I get.

Of trace flags and men

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!

, , ,
Previous Post
SQL Server 2017: Statistics Information Comes To Query Plans
Next Post
[Video] Office Hours 2017/05/10 (With Transcriptions)

7 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}