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 (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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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!
Table of contents
Currently he works as a database developer lead, responsible for the development of production databases in a media research company. He is also an occasional speaker at various community events and tech conferences. His favorite topic to present is about the Query Processor and anything related to it. Dmitry is a Microsoft MVP for Data Platform since 2014.
View all posts by Dmitry Piliugin
- SQL Server 2017: Adaptive Join Internals - April 30, 2018
- SQL Server 2017: How to Get a Parallel Plan - April 28, 2018
- SQL Server 2017: Statistics to Compile a Query Plan - April 28, 2018