By: Ahmad Yaseen | Updated: 2017-07-12 | Comments (2) | Related: > Performance Tuning
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.
In addition, the query is executed within 71ms as shown in the time statistics below.
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?
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:
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.
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:
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.
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.
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.
Next Steps
- Download the Microsoft® SQL Server® 2016 SP1 Latest Cumulative Update.
- Check Specifying Max Degree of Parallelism in SQL Server for a Query.
- Check also SQL Server 2016 DBCC CHECKDB with MAXDOP.
- Read more about What MAXDOP setting should be used for SQL Server.
About the author
Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-07-12
