2

I have a query which was executing slowly earlier. Later I found out that it was not running parallel and this is making the query execution slow.

The query involves a big view and then querying the view with lots of temp tables and sub query.

I removed one UDF from the view and used inline functions and also used a scalar TVF and then it started running fast in parallel execution.

It was going well for few days and one fine day I noticed that the query ran slowly. So I checked the execution plan and found out that the query is executing in serial mode. I checked the plan cache for the query and I saw lots of cached plans involving that view. I deleted plans which are not parallel and then the query runs quickly.

Now I do this every morning to force the query to run parallel.

Additional Details:

  1. SQL Server 2016 Standard
  2. Query gets generated via LINQ-SQL from the dot net application. So ad hoc query.

How can I force the query to run parallel forever?

9

At first glance, this sounds like a classic parameter sniffing problem.

SQL Server builds an execution plan for the first set of parameters that get called when the plan needs to be compiled, and then reuses that plan over and over through the day. You can see what parameters they are - when you're viewing the serial plan, right-click on the select statement, and go into properties. On the properties window, look for Parameters, and then Compiled Values. That'll show you which values produce the serial plan.

To force the plan to always run parallel, you have several different options (many of which Erland covers in his excellent post that I linked to above), including:

  • Tune the indexes so everything gets a better plan (to get advice on that, post the plan & server details as described in Getting Help with a Slow Query)
  • Temporarily, use a plan guide to pin the parallel plan in cache (but just know that if the query changes by a single letter, the plan guide will silently fail since it no longer matches the query)
  • Use an OPTIMIZE FOR hint in the plan to guide it towards values that produce a parallel plan
  • Use the new ENABLE_PARALLEL_PLAN_PREFERENCE hint in SQL Server 2016

That's just a quick answer - but for much, much more, read Erland's excellent post, Slow in the App, Fast in SSMS that explains how one query can get different plans, and how to fix it.

  • 2
    Since this is SQL 2016 Query Store can force the plan, which is easier than using a Plan Guide. – David Browne - Microsoft Jul 16 '18 at 21:49
  • Easier, yes - but has the same problem in that it doesn’t work “permanently” as he requested. The instant someone changes anything about that part of the app, QS will fail to recognize it as the same query. (I don’t think that’s a big or anything - just noting that most people are surprised by that.) – Brent Ozar Jul 17 '18 at 8:51
  • Hi..sorry for the delayed response.Since the query is generating from the application...i don't have control to add ENABLE_PARALLEL_PLAN_PREFERENCE.I wanted to add it a complex view i am using in the query,but i got error message a USE database statement is not allowed in a procedure, function or trigger.Now i am thinking of creating a plan guide. – user9516827 Jul 19 '18 at 15:05
3

It might be difficult to give you a precise answer to your problem, as we do not know what your queries look like, but you can force a particular plan using QueryStore feature which was introduced in SQL 2016. If you are using an earlier version of SQL Server or do not feel that query store is a solution for you - you might try things like:

  • forcing recompilation each time it is run(one of the methods is adding OPTION(RECOMPILE) to the statement that causes the issue)
  • review possible parallel plan inhibitors in other queries

You should also review previous posts on StackExchange on this topic, e.g. SQL not engaging parallelism for extremely large query

1

If the cost of the query exceeds the Cost Threshold of Parallelism value and the cost of the parallel plan is less than the cost of a serial plan, then a parallel plan will be created and used.

In some cases,QO chooses Serial plan than parallel plan becausue parallel plan is more expensive than serial plan in that case. For example if query contains scalar or relational operators that cannot be run in parallel mode.

In some cases QO make wrong decision due to inaccurate information, outdated statistics, poor cardinality estimate etc. Due to which cost of serial plan is slightly more than parallel plan.

so QO still choose serial plan.

To overcome this issue query may be optimize and statistics should also be updated.

This help QO in making correct decision.

I removed one UDF from the view and used inline functions and also used a scalar TVF

These are welcome steps, as soon as you improve your query some of the plan were parallel and query performance improved.

So there might be further scope of improving your query.

One should pay more attention in optimizing query and tuning index than straight away trying Hint

Rest of the things like using HINT,parameter sniffing,OPTION(RECOMPILE) are already discuss.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.