patternsqlMinor
Can't find compiled parameters values in the plan cache
Viewed 0 times
canthecompiledplancachefindvaluesparameters
Problem
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
Full XML plan
Query store:
Plan cache DMV's:
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:
Plan cache DMV's:
Solution
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.
Context
StackExchange Database Administrators Q#209382, answer score: 5
Revisions (0)
No revisions yet.