SQL SERVER – Using Query Hint ENABLE_PARALLEL_PLAN_PREFERENCE

I am not a big fan of using SQL Server query hints. The matter of facts, during the Comprehensive Database Performance Health Check, I always advise against using any query hints at all. Trust me there are so many different options you should experiment before you opt for query hint. Recently one of the DBA asked me how to use query hint ENABLE_PARALLEL_PLAN_PREFERENCE.

First of all, let me rephrase the original question. Instead of asking the question of how to use query hint ENABLE_PARALLEL_PLAN_PREFERENCE, I usually get a question about how to force SQL Server to go parallel with the queries and use parallelism.

Before I continue answering the question, let me say, one should not experiment with the query hint without a proper understanding of how the entire eco-system of your server works. I never force any of my queries to use ANY query hints.

Now let us see the following query and its actual execution plan.

SELECT i.AccountsPersonID, i.BillToCustomerID
FROM Sales.Invoices i
WHERE i.BillToCustomerID = 803
ORDER BY i.ConfirmedDeliveryTime
GO

The above query uses the single processor to run the query.

Here is the query with ENABLE_PARALLEL_PLAN_PREFERENCE.

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

If you use query hint ENABLE_PARALLEL_PLAN_PREFERENCE, the query will run in parallel on multiple CPU.

However, please note that parallel operation is not necessarily faster or better operation. There is a good chance that in the parallel plan is even more expensive than a single processor plan.

Here are relevant blog posts which you may find interesting:

• 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://darkslategrey-bat-805937.hostingersite.com)