Can You Force A Parallel Plan in Query Store?

I love the questions I get when presenting: Can You Force a Parallel Plan in Query Store. I haven’t a clue. The trick I think is going to be in setting up the test. Let’s try it out.

Reliably Getting a Parallel Plan

Because this is for testing, rather than try to build some crazy query that may or may not go parallel, I’ve decided to cheat. I’ll take advantage of a little functionality that ensures I see a parallel plan when I want to. Here’s my code:

DBCC TRACEON(8649);
GO
SELECT soh.OrderDate,
       soh.ShipDate,
       sod.OrderQty
FROM Sales.SalesOrderHeader AS soh
    JOIN Sales.SalesOrderDetail AS sod
        ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.SalesOrderID = 43705;
GO
DBCC TRACEOFF(8649);

Traceflag 8649 will force all plans to go parallel by effectively making the Cost Threshold for Parallelism zero, but I can do this without changing the cost threshold for parallelism. Running this and capturing the execution plan, the parallel plan looks like this:

If I run the query without using the Traceflag, the execution plan looks like this:

Force a Parallel Plan

First up, let’s query the query store to find our plans:

SELECT qsqt.query_sql_text,
       CAST(qsp.query_plan AS XML),
	   qsq.query_id,
	   qsp.plan_id
FROM sys.query_store_query AS qsq
    JOIN sys.query_store_plan AS qsp
        ON qsp.query_id = qsq.query_id
    JOIN sys.query_store_query_text AS qsqt
        ON qsq.query_text_id = qsqt.query_text_id
WHERE qsq.query_id = 251;

Checking the two plans that this returns, I can identify which of them is the parallel plan. With that information in hand, forcing the plan is very simple:

EXEC sys.sp_query_store_force_plan 251, 261;

Now we have to verify that this is correct. The easiest way to do this is to run the query and capture the plan (NOTE: We can’t just capture an estimated plan at this juncture. I explain why here).

Just so we’re clear, I typed all this up before I actually tested the final outcome. I got the parallel plan, even when running the query without the Traceflag.

Conclusion

The answer to the question of whether or not you can force a parallel plan is a resounding yes. My assumption based on how execution plans work and on how the Query Store works is that, of course, this was going to be the outcome. However, it pays to know, not guess. The good news is, you can also force it the other way, ensuring that plan never goes parallel.

---

Want a chance to learn a lot more about how the Query Store works? How about learning that along with a bunch of other SQL Server tools that can make it much easier to identify and tune your queries? I’ve got some upcoming, all-day, seminars on this topic:

SQL Day, May 13, 2019, Wroclaw, Poland
SQLSaturday Columbus Precon, June 7 2019, Columbus OH