3

Query store shows that a completely parameterized query has multiple query plans:

screenshot of the query store UI showing multiple plans for the same query

How can I find the input that is causing such long response times?

Why would the same parameterized query end up with different query plans? Where can I learn more about that?

  • 2
    You should be able to get at parameter values this way. And plans can change for a variety of reasons, such as a significant data change that invalidates statistics. Some other relevant info here and here. – Aaron Bertrand Feb 12 at 13:43
  • It does not work from QueryStore. When I click on the plan or in the operators, the "Properties" slider does not change. When I run the query with test values, and see the query plan, it then shows the parameters. – Vlad Feb 12 at 15:09
4

If you want to do this within the Query Store user interface, you can look at the compiled value for each of the plans to see which parameter values led to different plans:

  • click on one of the colored circles in the plan summary from your screenshot
  • right-click the graphical execution plan that appears at the bottom and choose "Properties"
  • expand the "Parameter List" node in the properties pane

screenshot of the execution plan properties parameter list

Here I'm looking at the compiled values that led to the "green" plan. Clicking on the "orange" plan will show the compiled values for that one.

If you prefer not to use the user interface, you can get the plans directly from the Query Store tables using the method described by Max Vernon here: Does SQL Server Query Store capture parameter values?

As Aaron Bertrand mentioned in the comments:

And plans can change for a variety of reasons, such as a significant data change that invalidates statistics. Some other relevant info here and here.

A new plan can get compiled for a number of different reasons (including statistics updates, manual plan clearing, adding up or rebuilding indexes, etc).

In the case in my screenshot, I can see that the new plan for this query showed up at 12:18 pm on 2/9. Looking at the OptimizerStatsUsage node of the execution plan properties, I can see that several of the statistics used by this query were updated just before that, so this is why I got the new plan:

screenshot of optimizer stats usage from execution plan properties

which is the process for which SQL Server chooses different plans depending on the parameters values? I would like to read more about that.

The process that SQL Server uses to generate a specific plan based on different input parameters is called "parameter sniffing." It's a really complex topic that you can read a lot more about it in Erik Darling's answer here:

Why is my query suddenly slower than it was yesterday?

There are some very comprehensive discussions about the topic of parameter sniffing in the "learn more" links at the bottom of that answer.

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.