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.

SQL SERVER - Using Query Hint ENABLE_PARALLEL_PLAN_PREFERENCE queryhint-800x119

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.

SQL SERVER - Using Query Hint ENABLE_PARALLEL_PLAN_PREFERENCE queryhint1

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.

SQL SERVER - Using Query Hint ENABLE_PARALLEL_PLAN_PREFERENCE queryhint2

Query Hint

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:

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

, , , ,
Previous Post
SQL SERVER – Stop Using DBCC DBREINDEX and Use ALTER INDEX
Next Post
SQL SERVER – T-SQL Script to Identify TOP 10 Parallel Running Queries

Related Posts

2 Comments. Leave new

Leave a Reply

Menu