4

Recently I've started to analyze execution plans. Today I stumbled on an execution plan that has no ParameterCompiledValue (and no ParameterList tag).

It is a plan of a select with one inner join and seven left outer joins.

Is there a reason why some execution plans have a ParameterCompiledValue and others don't?

Edit:

Is it correct to stay the ParameterList is empty due to the fact that sql server could not sniff the parameters? It is not an ad-hoc query and is exectued by a third party c# application. The query and queryplan can be found on Google drive

2

The XML plan uploaded includes:

<ParameterList>
    <ColumnReference Column="@today" ParameterCompiledValue="'2016-03-04 00:00:00.000'" />
</ParameterList>

There are multiple statements in the batch, so the XML contains multiple <StmtSimple> elements under <Statements>. Aside from the final select, the other statements are all assignments, which are not queries, so there is no parameter list at that level.

As Kin mentioned, you may find it useful to look at the plans in SQL Sentry Plan Explorer:

Plan Explorer capture

Related question:

Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN

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.