0

The most frequent query typically uses a variable that has hundreds of rows, but also runs often with a variable that has only a few rows (it's always one or the other, nothing in between). SQL sporadically picks up the query plan for the query against the few rows, resulting in spiking CPU when that plan runs against hundreds of rows, and my guess is it's after statistics update (auto stats are ON, I'm turning them off and adding a job to have it done at low peak rather than automatically at random times). Aside from pinning the plan in query store for the higher number of rows, is there a way to have both queries run successfully? Azure SQL V12.

1
  • Your question doesn't make much sense - what do you mean that a "query typically uses a variable that has hundreds of rows, but also runs often with a variable that has only a few rows"? SQL doesn't have variables in the strict sense. Do you mean that queries and subqueries can return varying amounts of records?
    – Vérace
    Commented Mar 17, 2019 at 9:23

1 Answer 1

0

It's a Parameter Sniffing problem, so you have a few options:

You can consider using sp_create_plan_guide. Creates a plan guide for associating query hints or actual query plans with queries in a database.

Enables or disables parameter sniffing. The default is ON. Setting PARAMETER_SNIFFING to OFF is equivalent to enabling Trace Flag 4136.

OPTION RECOMPLE avoids plan caching and the risk of reusing an inappropriate plan at the cost of compilation upon each execution.That is not a concern for infrequently executed queries but can be very expensive for an OLTP workload where the query is executed many times per second.

OPTIMIZE FOR UNKNOWN This hint directs the query optimizer to use the standard algorithms it has always used if no parameters values had been passed to the query at all. In this case the optimizer will look at all available statistical data to reach a determination of what the values of the local variables used to generate the queryplan should be, instead of looking at the specific parameter values that were passed to the query by the application. Take a look at this post.

1
  • @nmargol, did you get any luck with this? Commented Mar 18, 2019 at 21:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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