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.

How to Force a Parallel Execution Plan for a Query? - Interview Question of the Week #170 parallelexec-800x280

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

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

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

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

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

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

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)

Execution Plan, SQL Performance, SQL Scripts, SQL Server
Previous Post
What are Ports Needed to Configure Log Shipping? – Interview Question of the Week #169
Next Post
How to List All the SQL Server Jobs When Agent is Disabled? – Interview Question of the Week #171

Related Posts

Leave a Reply