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'))
GOIf 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://blog.sqlauthority.com)
4 Comments. Leave new
What versions does this work for Pinal?
I think for SQL Server 2016 SP2 onwards.
Well, it is quite simple. If a query seems to run slow and I try with this hint and it returns the results in milliseconds, then it is a good hint.
I run a set of simple SQL statements on a fairly large table to check that all data is consistent, example “select * from some_table where name is null” and it was slow so I killed it after 10 seconds. Then I reran the same SQL with this hint ENABLE_PARALLEL_PLAN_PREFERENCE and it returned result in milliseconds. I don’t need to run any elaborate analysis or some thing like that to understand that it works well in this scenario.
The downside with this approach is query execution time is how you are measuring success. What happens when another query comes along that performs slow? Add the hint? What about next year after you’ve deployed dozens of queries with the hint and your server is suffering massive resource contention (CPU, Memory, IO bottlenecks), deadlocks and timeouts? When that dreadful day comes, there is no easy button but rewriting expensive queries. Start when they crop up instead of pressing the “go fast” button. Try small changes, such as instead of SELECT * from SomeTable WHERE name IS NULL, try SELECT COUNT(1) FROM SomeTable WHERE name IS NULL. If the query is slow, look into your indexing and statistic refresh frequency. Measuring performance by how fast something runs must not be the only measurement in play.