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.
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: