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.
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'))
GORight-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.
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 :
- SQL SERVER – New Parallel Operation Cannot be Started Due to Too Many Parallel Operations Executing at this Time
- SQL SERVER – FIX: 3637 – A Parallel Operation Cannot be Started From a DAC Connection
- SQL SERVER – Number of Rows Read Per Threads in Parallel Operations
- Does Parallel Threads Process Equal Rows? – Interview Question of the Week #211
- SQL SERVER – Update Table Statistics in Parallel with FULLSCAN
Reference: Pinal Dave (https://blog.sqlauthority.com)
