SQL Server 2017: How to Get a Parallel Plan

SQL Server chooses parallel plans based on the costing (there are also some other factors that should be met for the plan that it can go parallel). Sometimes serial plan is slightly cheaper than a parallel, so it is assumed to be faster and picked by the optimizer, however, because the costing model is just a model it is not always true (for a number of reasons, enlisted in Paul’s article below) and parallel plan runs much faster.

There was one way to force parallel plan prior to SQL Server 2017, described by Paul White (b|t) in his post Forcing a Parallel Query Execution Plan. This method uses the undocumented trace flag 8649.

Let’s look at the example. I use sample MS database AdventureworksDW2016CTP3.

use [AdventureworksDW2016CTP3];
go
set showplan_xml on;
go
select
	fis.SalesAmount,
	dd.CalendarYear
from 
	dbo.FactInternetSales fis
	join dbo.DimDate dd on dd.DateKey = fis.OrderDateKey;

select
	fis.SalesAmount,
	dd.CalendarYear
from 
	dbo.FactInternetSales fis
	join dbo.DimDate dd on dd.DateKey = fis.OrderDateKey
option(querytraceon 8649);
go
set showplan_xml off;
go

The plans are:

You see that by default the plan is seral (first plan), if we apply the trace flag 8649 the plan goes parallel (second plan).

SQL Server 2017

In the new version of SQL Server which is now branded as SQL Server 2017 there is one more way to force parallel plan, that involves using the undocumented hint ENABLE_PARALLEL_PLAN_PREFERENCE.

Unfortunately, I have no information will it be documented some time or not, but I hope it will be.

Let’s look at the example:

set showplan_xml on;
go
select
	fis.SalesAmount,
	dd.CalendarYear
from 
	dbo.FactInternetSales fis
	join dbo.DimDate dd on dd.DateKey = fis.OrderDateKey
option(use hint('ENABLE_PARALLEL_PLAN_PREFERENCE'));
go
set showplan_xml off;
go

You may see that a query plan with this hint also goes parallel.

That’s all for that post. Thank you for reading!

PDF