How to Force a Parallel Execution Plan for a Query? – Interview Question of the Week #170

Question: How to Force a Parallel Execution Plan for a Query?

Answer: If you are SQL Server Performance Tuning Expert, you get all sort of questions. Recently during Comprehensive Database Performance Health Check, one of the customers asked a very interesting question related to the parallel execution plan.

Well, before we go into the technicality of a question, first let us see the answer.

Here is a query which runs on the single processor.

SELECT *
FROM [Sales].[Orders] t
WHERE t.CustomerID >100
ORDER BY OrderID
GO

Now there are two different methods to run the same query in multiple processors.

Method 1: QUERYTRACEON 8649

SELECT *
FROM [Sales].[Orders] t
WHERE t.CustomerID >100
ORDER BY OrderID
OPTION(QUERYTRACEON 8649)
GO

Method 2: HINT ENABLE_PARALLEL_PLAN_PREFERENCE

Please note that both the methods are not officially supported by Microsoft and it is quite possible they may create trouble (well, never created one for me so far).

SELECT *
FROM [Sales].[Orders] t
WHERE t.CustomerID >100
ORDER BY OrderID
OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))
GO

Once again, I am not way recommending you to use this link in the production environment. There is a myth that if a query uses multiple processors it will perform better. It is actually not true. If you run above query with the following settings and check the CPU and IO (SET STATISTICS IO, TIME ON), you will notice that the query demonstrated in this blog posts does a better job when it has run on a single machine.

Reference: Pinal Dave (https://blog.sqlauthority.com)