Optimize query plan when your results are either a few rows or hundreds of rows?

Question

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.

Answer 1

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.