Dmitry Piliugin

SQL Server 2017: How to Get a Parallel Plan

April 28, 2018 by

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.

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:

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

SQL Server 2017: Columnstore Indexes and Trivial Plan
SQL Server 2017: Columnstore in-place updates
SQL Server 2017: Scalar Subquery Simplification
SQL Server 2017: Interleaved Execution for mTVF
SQL Server 2017: Sort, Spill, Memory and Adaptive Memory Grant Feedback
SQL Server 2017: Statistics to Compile a Query Plan
SQL Server 2017: How to Get a Parallel Plan
SQL Server 2017: Adaptive Join Internals

References


See more

To view and analyze SQL Server query execution plans for free, check out ApexSQL Plan


Dmitry Piliugin

Dmitry Piliugin

Dmitry is a SQL Server enthusiast from Russia, Moscow. He started his journey to the world of SQL Server more than ten years ago. Most of the time he was involved as a developer of corporate information systems based on the SQL Server data platform.

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
Dmitry Piliugin
Query analysis, SQL Server 2017

About Dmitry Piliugin

Dmitry is a SQL Server enthusiast from Russia, Moscow. He started his journey to the world of SQL Server more than ten years ago. Most of the time he was involved as a developer of corporate information systems based on the SQL Server data platform. 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

244 Views