Sql-server – Can’t find compiled parameters values in the plan cache

execution-planparameterplan-cachesql serversql-server-2016

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

Best Answer

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.

Related Question