Forcing Parallelism: Trace Flag vs. Query Hint

Just because something works doesn’t mean it’s the right thing to do. I had that type of challenge to my database morals recently when facing a query that refused to go parallel.

Strength in Numbers

I was vaguely familiar with Trace Flag 8649 which can be used to force parallelism by ignoring the Cost Threshold for Parallelism value. I gave the trace flag a try and it successfully forced my query to go parallel.

That’s great, but I kind of felt like I was teasing myself. The query went parallel and completed faster, but I still wasn’t seriously considering using it in production. The trace flag is not officially supported, and I read about issues related to security privileges.

As fate would have it, I was watching one of Brent Ozar’s office hours videos where a user by the name of Slow Cheetah asked about Trace Flag 8649. Brent immediately buried the idea of using it but recommended checking out the ENABLE_PARALLEL_PLAN_PREFERENCE query hint as an alternative.

I logged back into my database, modified my query to add the query hint, and got an error:

I was confused. I thought maybe there was a compatibility issue. Considering ENABLE_PARALLEL_PLAN_PREFERENCE came along in SQL Server 2016, there would be other issues to deal with if compatibility was that far out of date. I verified compatibility and that checked out as being fine. I read back over my query and realized my mistake.

I had a typo.

I had typed “_REFERENCE” instead of “_PREFERENCE” in the query hint. That’s my bad. I suppose I should have copied and pasted instead of trying to type it in myself.

Once I fixed the typo, the query went parallel as hinted and completed in a fraction of the time.

Short Example

I can’t share the query I was optimizing but here’s a short example of how to use the ENABLE_PARALLEL_PLAN_PREFERENCE query hint. Let’s say this is our query that’s not going parallel:

SELECT DisplayName
FROM Users
WHERE Location = 'Ohio';
GO

To add our query hint, all we need to do is reference it at the end of the query:

SELECT DisplayName
FROM Users
WHERE Location = 'Ohio'
OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));
GO

Query Hint Wins

I feel better about using the ENABLE_PARALLEL_PLAN_PREFERENCE query hint, as it’s officially supported. It also does not require administrator privileges like Trace Flag 8649. Having said that, the ENABLE_PARALLEL_PLAN_PREFERENCE query hint is still not documented, which is unfortunate. I found some complaints about that fact here, but they’re falling on deaf ears.

Bottom line, ENABLE_PARALLEL_PLAN_PREFERENCE is preferable to Trace Flag 8649 any day of the week.

Thanks for reading!

UPDATE: See comments below. I was incorrect about ENABLE_PARALLEL_PLAN_PREFERENCE being officially supported. Thanks to Erik for pointing that out.