Breaking: SQL Server 2016 SP1 CU2 Supports ENABLE_PARALLEL_PLAN_PREFERENCE Hint

Last Updated 7 years ago

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.

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)

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

Wes Crockett
May 12, 2017 4:29 pm

Welcome back to the best country 😉

Reply
- Wes Crockett
  May 12, 2017 4:29 pm
  
  Wait… you probably wrote this a while ago.
  
  Reply
  - Erik Darling
    May 12, 2017 5:18 pm
    
    No, sadly, I wrote this today.
    
    I am back in the country where I can’t deadlift every car.
    
    Reply
    - Wes Crockett
      May 13, 2017 2:35 am
      
      Are you back stateside then?
      
      Reply
Beet
May 12, 2017 6:23 pm

SP2?

Reply
- Erik Darling
  May 12, 2017 6:27 pm
  
  I didn’t pay attention to my own screenshot. Fixed — thanks!
  
  Reply
Page Free Space : Forcing a Parallel Query Execution Plan
May 13, 2017 2:08 am

[…] this post by Erik Darling and this one by Dmitry […]

Reply