Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to Force a Parallel Execution Plan in SQL Server 2016


By:   |   Read Comments   |   Related Tips: More > Performance Tuning

Quickly Resolve Performance Problems for IIS, .NET and SQL Server       >>>   Get Started


Problem

Query performance tuning is a major part of the SQL Server Database Administrator's job. During this journey, you may face cases in which the SQL Server Query Optimizer chooses a serial plan to execute a query, although it would execute much faster using a parallel plan. The choice made by the Query Optimize could be due to the fact that the cost of the parallel plan is a higher than the serial plan cost. Is there a way to force the Query Optimizer to use a parallel plan rather than a serial plan?

Solution

Parallelism is the process of splitting a big task into multiple smaller tasks that run simultaneously, where each task will accomplish part of the overall job. Finally, the partial results of each small task will be combined into one final result. You can imagine a parallel plan as multiple serial plans that run at the same time on a separate processing unit. As a result of using a parallel plan, the query will run much faster than the serial plan.

Before choosing to execute the query using serial or a parallel plan, the SQL Server Database Engine will check if the SQL Server instance is running on a server that has more than one processor.  The Maximum Degree of Parallelism (MAXDOP) is configured to allow parallel plans where a MAXDOP with a value equal to 1 means that a serial plan will always be used to execute the query. If the MAXDOP setting is greater than 1 or 0 (if 0 all processors will be used) and the cost of the query exceeds the Cost Threshold of Parallelism value and the cost of the parallel plan is less than the cost of a serial plan, then a parallel plan will be created and used. If the decision is taken to use a parallel plan, the SQL Server Engine detects the number of CPUs required to execute the query, called the Degree Of Parallelism (DOP), and distributes the tasks among these threads in order to execute the query.

In some circumstances, the SQL Server Query Optimizer chooses to execute the query using a serial plan, rather than using a parallel plan, due to the expensive cost of the parallel plan versus the serial plan, or because the query contains scalar or relational operators that cannot be run in parallel mode. However, not all the query optimizer decisions are the best option and you may find that the query will run faster when using a parallel plan, although the Query Optimizer chooses to use a serial plan, due to the slight difference in the cost between the serial and parallel plans. Wrong decisions may also occur due to optimizer model limitations or inaccurate information about the cardinality and distribution of output values provided to the Query Optimizer.

To overcome this performance issue, you should first fix the main cause of that wrong decision, such as making sure that the information provided to the optimizer is accurate (such as updated statistics or a bettr written query). If you did your job and the reason why a serial plan is used versus a paralleled plan is still not clear, then you need to force the use of a parallel plan within the query. In this tip, we will provide two methods to achieve that.

Example to Show Query Execution and Plan

Assume that we need to run the below SELECT query then check the time statistics and the actual execution plan.

USE MSSQLTipsDemo 
GO
SELECT PP.[ProductID]
      ,[Name]
      ,[ProductNumber]
      ,PTH.ActualCost 
      ,PTH.TransactionType      
  FROM [MSSQLTipsDemo].[Production].[Product] PP
  JOIN [MSSQLTipsDemo].[Production].TransactionHistory PTH
  ON PP.ProductID =PTH.ProductID 
  WHERE PP.SellEndDate <GETDATE()-2 AND MakeFlag =1 and Weight >148
   

The Query Optimizer chooses to execute the query using a serial plan as follows. We are going to ignore the Missing Index message, so we can illustrate how the plan can differ with this query.

Plan1

In addition, the query is executed within 71ms as shown in the time statistics below.

Time1

Run Query in Parallel Using Trace Flag 8649

What about running it using a parallel plan? The first method of forcing the previous query to use a parallel plan is enabling Trace Flag 8649, using the QUERYTRACEON query level option.

USE MSSQLTipsDemo 
GO
SELECT PP.[ProductID]
      ,[Name]
      ,[ProductNumber]
      ,PTH.ActualCost 
      ,PTH.TransactionType      
  FROM [MSSQLTipsDemo].[Production].[Product] PP
  JOIN [MSSQLTipsDemo].[Production].TransactionHistory PTH
  ON PP.ProductID =PTH.ProductID 
  WHERE PP.SellEndDate <GETDATE()-2 AND MakeFlag =1 and Weight >148
  OPTION(QUERYTRACEON 8649)
   

It is clear from the below execution plan that the query will run using a parallel plan, but is it faster than the serial plan?

Plan2

Checking the time statistics of the previous query, you will clearly see that the query is executed within 43ms using the parallel plan, which is much faster than the serial plan that took 71ms. Nothing comes for free and we can see the parallel plan consumes more CPU time than the serial plan:

Time2

Run Query in Parallel Using Hint Enable Parallel Plan Preference

Starting with SQL Server 2016 SP1, the OPTION(USE HINT (’ ’)) query hint is introduced as a replacement to the OPTION(QUERYTRACEON) query hint statement without the need to have sysadmin permissions to execute and you provide the hint name without the need to remember the trace flag number. Cumulative Update 2 for SQL Server 2016 SP1 comes with a new hint that is used to force the parallel plan execution for a specific query.

The previous query can be rewritten to use the new ENABLE_PARALLEL_PLAN_PREFERENCE query level hint as follows.

USE MSSQLTipsDemo 
GO
SELECT PP.[ProductID]
      ,[Name]
      ,[ProductNumber]
      ,PTH.ActualCost 
      ,PTH.TransactionType      
  FROM [MSSQLTipsDemo].[Production].[Product] PP
  JOIN [MSSQLTipsDemo].[Production].TransactionHistory PTH
  ON PP.ProductID =PTH.ProductID 
  WHERE PP.SellEndDate <GETDATE()-2 AND MakeFlag =1 and Weight >148
  OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))
   

Trying to run the previous query, an error will be raised showing that this hint is not valid in the current SQL Server version.

Error

Checking the current SQL Server version, you will see that we are using SQL Server 2016 SP1, which does not support the ENABLE_PARALLEL_PLAN_PREFERENCE hint that was introduced in CU2 for SP1:

Version1

Patching the current SQL Server instance with the latest Cumulative Update, which is CU3 at the date of writing this tip and the checking the SQL Server version again.

Version2

Now we can easily use the ENABLE_PARALLEL_PLAN_PREFERENCE query hint as shown below.

USE MSSQLTipsDemo 
GO
SELECT PP.[ProductID]
      ,[Name]
      ,[ProductNumber]
      ,PTH.ActualCost 
      ,PTH.TransactionType      
  FROM [MSSQLTipsDemo].[Production].[Product] PP
  JOIN [MSSQLTipsDemo].[Production].TransactionHistory PTH
  ON PP.ProductID =PTH.ProductID 
  WHERE PP.SellEndDate <GETDATE()-2 AND MakeFlag =1 and Weight >148
  OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))
   

The query will run successfully now forcing the parallel plan execution for the query as shown below.

Plan3

Again, the time statistics shows that the parallel plan is faster than the serial plan for this query although it is more expensive due to the extra CPU time consumed for the parallelism.

Time3
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools