3

I have found, through the query store, a query that's performing on average 297582 logical reads.

I wanted to see if I was able to tune that query a bit, and after that, try to execute the query again to see if there was any improvement.

The problem is that I couldn't find compiled parameters value in the cached plan.

Am I missing something? Maybe some reason/setting that prevents the caching of parameters values?

I can't find the parameters even if I open the execution plan as XML.

Additional informations: the query is executed by a third party application that prepares the statements and then executes them with sp_prepare and sp_execute.

Full XML plan

Query store:Can't find any parameter in the properties tab

Plan cache DMV's:Plan cache dmv

5

sp_prepare doesn't include the compiled values for parameters, because it doesn't 'sniff' them. When a query is issued with it, cardinality estimates are made using the density vector rather than the statistics histogram.

I blogged sort of recently about that here:

Why sp_prepare Isn’t as “Good” as sp_executesql for Performance

If you read the blog post, some of the terms I've used in this answer are explained a bit more.

  • Thank you so much. I've already read the article before (really helpful) but I didn't know that the parameters don't get saved somewhere in the plan cache (but yeah it makes sense). So, as a side question, how would you try to execute the query again for optimizing purposes without knowing any "real production" value? My guess is that you have to look into the various table and use some random ones, or talk to somebody that use the application to catch the query with extended events. Do you think that's a good approach? – Mattia Nocerino Jun 13 '18 at 7:06

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.