Serial Plan in plan cache

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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.