SQL SERVER – Limitation of ENABLE_PARALLEL_PLAN_PREFERENCE Hint

Earlier this week, I had written a blog post about SQL SERVER – Using Query Hint ENABLE_PARALLEL_PLAN_PREFERENCE and I received quite a few interesting emails. One of the emails was about the Limitation of ENABLE_PARALLEL_PLAN_PREFERENCE Hint. Let us learn about this preference.

Aaron asked in email –

“I saw the query hint to enable parallelism. If I use the hint in my query, will my query always go on parallel and my workload will be distributed on multiple CPU?”

Such an interesting question – the answer is NO.

SQL SERVER - Limitation of ENABLE_PARALLEL_PLAN_PREFERENCE Hint queryhint-800x119

The query hint ENABLE_PARALLEL_PLAN_PREFERENCE will force your query to go parallel but it will not force them to use all the CPUs. Let us see the query example which we had used earlier here.

First, make sure that you enable the actual execution plan for the query. Here is the blog post about:.

Next, run the following query:

SELECT i.AccountsPersonID, i.BillToCustomerID
FROM Sales.Invoices i
WHERE i.BillToCustomerID = 803
ORDER BY i.ConfirmedDeliveryTime
OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))
GO

Right-click on the index seek operator and go to properties. Over here, see the right side the properties and you will notice that SQL Server uses a total of 8 threads but except one thread all the rest of the threads are processing zero rows. This clearly indicates even though, we are forcing the parallelism only one CPU/thread is processing all the rows and rest are doing nothing.

SQL SERVER - Limitation of ENABLE_PARALLEL_PLAN_PREFERENCE Hint cputhreads

This query is a great example of the query to run on a single thread. If we do not force parallelism, it usually runs on a single thread.

Here are relevant blog posts which you may find interesting about Limitation :

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

, , , , ,
Previous Post
SQL SERVER – Security Risk of Public Role – Very Little
Next Post
SQL SERVER – Is Stream Aggregate is Same as Gather Streams of Parallelism?

Related Posts

Leave a Reply

Menu