How to tell from ShowPlan output if (optimize for (unknown)) was used?

  • Article

This one question someone asked me recently.

In SQL Server 2005, 2008, and 2008 R2 there’s no difference between the ParameterList node generated for a parameterized query optimized for the parameter’s runtime value and the ParameterList node generated for the same query parameterized for UNKNOWN.

For the query engine, a parameter for which the UNKNOWN hint has been set is considered not to have an initial value set. And for a parameter with no initial value, it doesn’t generate the string version of the initial value which is eventually used by showplan to produce the contents of the ParameterRuntimeValue & ParameterCompiledValue attributes for each ColumnReference node.

The only place where we can search for evidence of that special UNKNOWN value being used is the StatementText attribute of the StmtSimple node. But there’s a problem with that approach: the statement text is truncated after 4000 characters. It is not very typical to see a single statement larger than that size, but if it is you won’t notice that the (OPTIMIZE FOR (UNKNOWN)) was ever used in the query.

Having said this, I filed a DCR so that PG considers extending the showplan schema so that the ColumnReferenceType type includes a new attribute through which they can indicate whether ParameterCompiledValue used UNKNOWN or not.

Thanks,

Nacho